Re: Question about functions that return a set of records

Hey thanks for your email, this was exactly the explanation I was looking for.  I figured out the CREATE TYPE technique but I'm gonna give the out parameters a try as well, it kinda looks cleaner especially if the only thing that uses the type is a single stored proc..

Albe Laurenz wrote:

Mike Christensen wrote: 

I have the following function:

CREATE FUNCTION foo(_userid uuid) RETURNS SETOF record AS
$BODY$
BEGIN RETURN QUERY   select n.UserId, u.Alias, n.Date, n.Data   --Bunch of joins, etc

If I understand correctly, I have to return "SETOF record" since my 
result set doesn't match a table and isn't a single value.  However, 
this means when I want to call it I have to provide a column definition 
list, such as:

select * from foo as (...);

Is there any way to specify this column list within the function 
itself?  The problem I'm running into is I want to call this function 
using Npgsql which doesn't appear to support passing in a column 
definition list.   

You can avoid that problem if you specify the return type in the function definition.

There are two possibilities:

The "classical" way is to define a TYPE similar to this:

CREATE TYPE foo_type AS (  _userid uuid,  _alias text,  _date date,  _data text
);

or similar, depending on your select list and data types.
Then you can define the function as:

CREATE FUNCTION foo(_userid uuid) RETURNS SETOF foo_type ...

The "new" way is to use output parameters. This is a little harder
to understand, but you need not define a foo_type:

CREATE FUNCTION foo(INOUT _userid uuid, OUT _alias text, OUT _date date, OUT _data text)  RETURNS SETOF RECORD ...

In both cases you can call the function like this:

SELECT * FROM foo('A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11');

The OUT parameters are just a different way of specifying the output type.

Yours,
Laurenz Albe