Re: Question about functions that return a set of records

Nevermind. I figured it out, you just do this:

CREATE TYPE notification AS (UserId uuid, Data text);
CREATE FUNCTION Foo(_userid uuid)
RETURNS SETOF notification AS
$BODY$
BEGIN
RETURN QUERY
select n.UserId as UserId, n.data as Data from subscriptions s
inner join notifications n on n.userid = s.userid
inner join users u on u.userid = s.userid
where s.subscriberid=_userid
order by n.date desc;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 10;

Works great when calling it in Npgsql as well, it just figures out the
composite type is a DataRow.

Sweet!
Mike

Asko Oja wrote:
> CREATE FUNCTION func(
> i_users text[],
> OUT username text,
> OUT update_time timestamp with time zone
> ) RETURNS SETOF record AS
> $_$
> select f.username , f.update_time
> from tbl f
> where f.username = ANY ($1);
> $_$
> LANGUAGE sql SECURITY DEFINER;
>
> On Fri, Feb 20, 2009 at 8:50 AM, Mike Christensen <imaudi(at)comcast(dot)net
> <mailto:imaudi(at)comcast(dot)net>> 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.
>
> One idea is to use a view and then have the function select * from
> the view and apply the where clause. However, I'm not sure if
> this would be as performant since views may not be indexed (dunno
> if this is true or not)..
>
> Any other ideas would be appreciated.. Thanks!
>
> Mike
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>