Re: plpgsql: return multiple result sets

--- Oksana Yasynska <oksana(at)athabascau(dot)ca> wrote:
> Hi all,
>
> I'm running Postgres 7.2.1 and I need to return
> multiple row sets from plpgsql
> function. I'm new in plpgsql but according
> documentation and everything I
> could find in the mailing list I need to switch to
> 7.3 to get at least SETOF
> rows as a result.
>
> I can't really upgrade Postgres now. Is there is any
> a workaround idea to
> retrieve multiple rowsets?
>
> I have up to 50 tables in database to join and pass
> this data to the another
> application
> I had an idea to build a view and retrieve cursor on
> this view (if I stay with
> 7.2) or generate custom type based on the columns of
> all 50 tables and
> retrieve a SETOF custom type (if I use 7.3)
>
> Can anybody give me any suggestion?

You can return a cursor from your function, which you
can then use in your application. Sort of like:

create function my_cursor_test(refcursor, integer)
returns refcursor as 'begin open $1 as cursor for
select * from mytable where id = $2; return $1; end;'
language 'plpgsql';

Then call it like:
begin;
select my_cursor_test(mycursor, 1);
select * from mycursor;
(output comes here)
end;

Note the need to wrap the statements in an explicit
transaction. With statements being autocommitted, the
cursor would be closed immediately following the
function call.

Better check the syntax too, I just dashed that off
(hey, it's Saturday). It's all there in the 7.2 docs
under "procedural languages".

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com