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
>
>