Re: Question about functions that return a set of records

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