Re: options for no multiple rows?

On Sat, 26 Jan 2002, pgsql wrote:

> Greets!
>
> Ok, from what I've read (good, bad or indifferent) you can't create a
> stored procedure/function and return multiple rows to use in say a
> resultset with the following:

Depending on what you're doing (and if you're willing to work with the
7.2rcs or wait for it), 7.2 allows you to define functions that return
cursors that you can then fetch from within the transaction you called the
function in, so you should be able to do a sequence like:

begin;
select * from func(param);
-- get back name of cursor, say "<unnamed cursor 1>" --
fetch 10 from "<unnamed cursor 1>";
fetch 10 from "<unnamed cursor 1>";
close "<unnamed cursor 1>";
commit;

I don't think this is quite a complete replacement. AFAIK, you can't use
the cursor like a table (ie in later joins and such), but that may not be
necessary for what you're doing.

(In case you're wondering, my test function looked like:
create function ct(int) returns refcursor as 'declare curs1 refcursor;
begin open curs1 for select * from cttable where key= $1; return curs1;
end;' language 'plpgsql';
)