On Thu, Jan 13, 2005 at 01:44:58PM -0500, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> >> Please find attached an example that allows people to return multiple
> >> named refcursors from a function. Big kudos to Kris Jurka for
> >> figuring this out and pasting to rafb :)
>
> > Oops. Joe Conway pointed out that this patch could be more succinct
> > and not create an essentially useless new type.
>
> I think the example is pretty confusing, or at least not compelling,
> since it's not clear to the reader why you'd go to all that trouble
> to return two scalars. The cursors ought to return rowsets.
> Maybe
>
> + OPEN $1 FOR SELECT * FROM table1;
> + RETURN NEXT $1;
> + OPEN $2 FOR SELECT * FROM table2;
> + RETURN NEXT $2;
>
> regards, tom lane
Good point. Next patch attached :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.55
diff -c -r1.55 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 8 Jan 2005 22:13:34 -0000 1.55
--- doc/src/sgml/plpgsql.sgml 13 Jan 2005 17:21:19 -0000
***************
*** 2306,2311 ****
--- 2306,2339 ----
COMMIT;
</programlisting>
</para>
+
+ <para>
+ The following example lets you return multiple cursors from a
+ single function.
+
+ <programlisting>
+ CREATE OR REPLACE FUNCTION myfunc(refcursor, refcursor)
+ RETURNS SETOF refcursor
+ LANGUAGE plpgsql
+ AS $$
+ BEGIN
+ OPEN $1 FOR SELECT * FROM table_1;
+ RETURN NEXT $1;
+ OPEN $2 FOR SELECT * FROM table_2;
+ RETURN NEXT $2;
+ RETURN;
+ END;
+ $$;
+
+ -- need to be in a transaction to use cursors.
+ BEGIN;
+
+ SELECT * FROM myfunc('a', 'b');
+
+ FETCH ALL FROM a;
+ FETCH ALL FROM b;
+ </programlisting>
+ </para>
</sect3>
</sect2>
</sect1>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
