--- Oksana Yasynska <oksana@athabascau.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