--- Oksana Yasynska <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
