On Saturday, March 03, 2012 9:13:52 am John Fabiani wrote:
> The following example shows one way to return multiple cursors from a
> single function:
>
> CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
> BEGIN
> OPEN $1 FOR SELECT * FROM table_1;
> RETURN NEXT $1;
> OPEN $2 FOR SELECT * FROM table_2;
> RETURN NEXT $2;
> END;
> $$ LANGUAGE plpgsql;
>
> -- need to be in a transaction to use cursors.
> BEGIN;
>
> SELECT * FROM myfunc('a', 'b');
>
> FETCH ALL FROM a;
> FETCH ALL FROM b;
> COMMIT;
>
> I really enjoy programming - everyday I learn something!
>
> This is of course is different than creating a temp table that lives as
> long as the session lives.
If you want a server side cursor in Postgres that outlives a transaction you
can
use SQL to DECLARE a cursor WITH HOLD. The rules for cursors inside plpgsql are
different so the previous does not apply there.
http://www.postgresql.org/docs/9.0/interactive/sql-declare.html
Psycopg2 can handle server side cursors through the cursor() function using
named cursors. See:
http://initd.org/psycopg/docs/usage.html#server-side-cursors
FYI the link above shows you a way to get around the plpgsql restriction on
WITH
HOLD using a named cursor.
>
> Johnf
--
Adrian Klaver
[email protected]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
Searchable Archives: http://leafe.com/archives/search/dabo-users
This message:
http://leafe.com/archives/byMID/[email protected]
