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