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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
