Postgres Pro
Downloads
Home   >   mailing lists

Thread: SETOF and language 'plpgsql'

SETOF and language 'plpgsql'

From
"Eric Ridge"
Date:
How do you return a "setof <datatype>" from a plpgsql function?  I
thought the below would do the trick, but it does not.

   create table tbl_foo (id bigint);
   insert into tbl_foo (id) values (1);
   insert into tbl_foo (id) values (2);

   create function func_foo () returns setof bigint as
   'BEGIN
    return (select id from tbl_foo);
    END;
   ' language 'plpgsql';

   select func_foo();
   ERROR:  More than one tuple returned by a subselect used as an
expression.

but this works as expected with "language 'sql'":

   create function func_foo () returns setof bigint as
   'select id from tbl_foo'
   language 'sql';

   select func_foo();
    ?column?
   ----------
           1
           2
   (2 rows)


Either I'm missing something really silly, or plpgsql just can't do it.
Any insight will be greatly appreciated!

eric

Re: SETOF and language 'plpgsql'

From
"Eric Ridge"
Date:
> How do you return a "setof <datatype>" from a plpgsql function?

I'll respond to my own post...

After searching the archives I found a post by Jan Wieck that basically
says, "You can't....not sure if we will be able to  do  it  for  7.2".
Then Tom Lane replied, "...If you want to fix plpgsql so that it retains
state and can produce multiple elements of a set over repeated calls,
the same way that SQL functions do, then it could be done today."

http://archives2.us.postgresql.org/pgsql-sql/2000-08/msg00244.php
   and
http://archives2.us.postgresql.org/pgsql-sql/2000-08/msg00252.php

They are dated August 2000.

It's over a year later.  What's the status today?  Is this planned for
7.2?  The TODO list doesn't mention it.

eric

Re: SETOF and language 'plpgsql'

From
Tom Lane
Date:
"Eric Ridge" <ebr@tcdi.com> writes:
>> How do you return a "setof <datatype>" from a plpgsql function?

> After searching the archives I found a post by Jan Wieck that basically
> says, "You can't....not sure if we will be able to  do  it  for  7.2".
> Then Tom Lane replied, "...If you want to fix plpgsql so that it retains
> state and can produce multiple elements of a set over repeated calls,
> the same way that SQL functions do, then it could be done today."

I think you misread that ;-).  What I was saying was that there wasn't
(any longer) anything outside plpgsql that would prevent it from
returning sets.  There would be a nontrivial amount of work to do inside
plpgsql to make it happen; see Jan's followup
http://archives2.us.postgresql.org/pgsql-sql/2000-08/msg00258.php

> It's over a year later.  What's the status today?

About the same.

There is support in 7.2 for plpgsql functions to return references to
cursors. This is not by any means the same thing as a SETOF result,
but it can serve some of the same purposes.

            regards, tom lane

Re: SETOF and language 'plpgsql'

From
"Eric Ridge"
Date:
> I think you misread that ;-).  What I was saying was that there wasn't
> (any longer) anything outside plpgsql that would prevent it from
> returning sets.  There would be a nontrivial amount of work to do
inside
> plpgsql to make it happen; see Jan's followup

Thanks for the clarification.  :)

> > It's over a year later.  What's the status today?
>
> About the same.
>
> There is support in 7.2 for plpgsql functions to return references to
> cursors. This is not by any means the same thing as a SETOF result,
> but it can serve some of the same purposes.

I'll look into this to see if it can do what I want.  Or if I can do
what it wants!

eric