Thread: Return rows from function with expressions

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

I've spent a good deal of time reading here and in the pg docs about
functions that use SETOF. I can't find a plain and simple way to do
something like the above. Is there? I simply need to pass in some params,
and do some evals inside the function and return a set that includes some
cols that are not part of the base table. I've seen solutions that involve
OUT params; a method which uses RECORD that requires the columns to be
defined in the call; refcursors (which I don't really understand); are those
the only options? A simple example like the one I've used for sql server
above would be ideal.
--
View this message in context: http://www.nabble.com/Return-rows-from-function-with-expressions-tf3812759.html#a10792602
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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