Search Postgresql Archives

Re: Question about functions that return a set of records

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux