Re: Question about functions that return a set of records

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>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)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>