test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
</programlisting>
       </para>

       <para>
        The following example uses automatic cursor name generation:

<programlisting>
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 &lt;unnamed cursor 1&gt;
(1 row)

FETCH ALL IN "&lt;unnamed cursor 1&gt;";
COMMIT;
</programlisting>
       </para>

       <para>
        The following example shows one way to return multiple cursors
        from a single function:

<programlisting>
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;
</programlisting>
       </para>
     </sect3>
   </sect2>

   <sect2 id="plpgsql-cursor-for-loop">
    <title>Looping through a Cursor's Result</title>

    <para>
     There is a variant of the <command>FOR</command> statement that allows
     iterating through the rows returned by a cursor.  The syntax is:

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
FOR <replaceable>recordvar</replaceable> IN <replaceable>bound_cursorvar</replaceable> <optional> ( <optional> <replaceable>argument_name</replaceable> { := | =&gt; } </optional> <replaceable>argument_value</replaceable> <optional>, ...</optional> ) </optional> LOOP
    <replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>

     The cursor variable must have been bound to some query when it was
     declared, and it <emphasis>cannot</emphasis> be open already.  The
     <command>FOR</command> statement automatically opens the cursor, and it closes
     the cursor again when the loop exits.  A list of actual argument value
     expressions must appear if and only if the cursor was declared to take
     arguments.  These values will be substituted in the query, in just
     the same way as during an <command>OPEN</command> (see <xref
     linkend="plpgsql-open-bound-cursor"/>).
   </para>

   <para>
     The variable <replaceable>recordvar</replaceable> is automatically
     defined as type <type>record</type> and exists only inside the loop (any
     existing definition of the variable name is ignored within the loop).
     Each row returned by the cursor is successively assigned to this
     record variable and the loop body is executed.
    </para>
   </sect2>

  </sect1>

  <sect1 id="plpgsql-transactions">
   <title>Transaction Management</title>

   <para>
    In procedures invoked by the <command>CALL</command> command
    as well as in anonymous code blocks (<command>DO</command> command),
    it is possible to end transactions using the
    commands <command>COMMIT</command> and <command>ROLLBACK</command>.  A new
    transaction is started automatically after a transaction is ended using
    these commands, so there is no separate <command>START
    TRANSACTION</command> command.  (Note that <command>BEGIN</command> and
    <command>END</command> have different meanings in PL/pgSQL.)
   </para>

   <para>
    Here is a simple example:
<programlisting>
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();
</programlisting>