SETOF and language 'plpgsql'
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
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
Import Notes
Resolved by subject fallback
"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
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
Import Notes
Resolved by subject fallback