SETOF and language 'plpgsql'

From: "Eric Ridge" <ebr(at)tcdi(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: SETOF and language 'plpgsql'
Date: 2001-11-03 07:47:24
Message-ID: D3ADE25911614840BC69C72E3171E4ED0FBDDA@tcdiexch.tcdi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2001-11-03 08:32:09 Re: how do the pro's do this? (still a newbie)
Previous Message Gunnar Lindholm 2001-11-03 07:34:37 Re: how do the pro's do this? (still a newbie)