Thread: Question about functions that return a set of records

Question about functions that return a set of records

From

Mike Christensen

Date:

20 February 2009, 05:51:00

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

Re: Question about functions that return a set of records

From

Asko Oja

Date:

20 February 2009, 06:00:27

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

Re: Question about functions that return a set of records

From

Mike Christensen

Date:

20 February 2009, 06:11:25

Won't I still need a column definition list when I call that since it returns SETOF record?

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

Re: Question about functions that return a set of records

From

"A. Kretschmer"

Date:

20 February 2009, 06:30:19

In response to Mike Christensen :
> Won't I still need a column definition list when I call that since it returns
> SETOF record?

You have to provide a column definition, either as OUT-parameter
definition or as column definition list in the select.

Please, don't top-posting.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: Question about functions that return a set of records

From

Mike Christensen

Date:

20 February 2009, 06:31:14

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

Re: Question about functions that return a set of records

From

"Albe Laurenz"

Date:

20 February 2009, 07:19:41

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

Re: Question about functions that return a set of records

From

Mike Christensen

Date:

20 February 2009, 07:34:31

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

Re: Question about functions that return a set of records

From

"Francisco Figueiredo Jr."

Date:

20 February 2009, 13:15:33

On Fri, Feb 20, 2009 at 3:50 AM, Mike Christensen <imaudi@comcast.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.
>

Hmm, Npgsql supports this syntax when your function returns a record.
But I think it is easy to add support for a setof record.
I'll check it out.

To use the support of record in Npgsql, you just need to specify your
parameters which will receive the returned values ("the output list")
as out parameters. Npgsql will take care of them and build the output
list for you when calling your function.

commandtext = "function_name";
command.parameters.add("first parameter"));
command.parameters[0].Direction = InDirection;

command.parameters.add("second parameter"));
command.parameters[1].Direction = OutDirection;

command.parameters.add("Third parameter"));
command.parameters[2].Direction = OutDirection;

And when you call your function, Npgsql will pass your first parameter
and build the output list with the second and third parameters.

For while, if possible, you could use Npgsql support for returning
setof refcursor. You can check examples about how to do that
in our user manual: http://manual.npgsql.org

I hope it helps.

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://fxjr.blogspot.com
http://www.npgsql.org