Re: Return rows from function with expressions
От | Ragnar |
---|---|
Тема | Re: Return rows from function with expressions |
Дата | |
Msg-id | 1180046117.1879.14.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Return rows from function with expressions (novnov <novnovice@gmail.com>) |
Список | pgsql-general |
On fim, 2007-05-24 at 13:59 -0700, novnov wrote: > I've written many stored procedures in ms sql and a good many functions in > postgres, but I'm rather unsure of how to get a list back from a postgres > function which is not based on a table. Example from sql server: > > set ANSI_NULLS ON > set QUOTED_IDENTIFIER ON > GO > ALTER PROCEDURE [dbo].[procPatient] > @PatStatusID int = 0 > AS > BEGIN > SELECT PatID, PatFName + ' ' + PatLName as pname FROM tblPatient WHERE > PatStatusID = @PatStatusID > END > > Output from the sproc above is like > PatID pname > 123 Merton Baffled > 129 Jim Puzzled test=# create table pats (patid int, patfname text, patlname text, patstatus int); CREATE TABLE test=# insert into pats values (123,'Merton','Baffled',2); INSERT 0 1 test=# insert into pats values (129,'Jim','Puzzled',2); INSERT 0 1 test=# insert into pats values (132,'Joe','Confused',1); INSERT 0 1 test=# create type patrec as (patid int, patname text); CREATE TYPE test=# create or replace function getpats(int) returns setof patrec as $$ select patid,patfname|| ' ' || patlname from pats where patstatus=$1 $$ language SQL; CREATE FUNCTION test=# select * from getpats(2); patid | patname -------+---------------- 123 | Merton Baffled 129 | Jim Puzzled (2 rows) hope this helps gnari
В списке pgsql-general по дате отправления: