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