11<!--
2- $Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.54 2002/03/22 19:20:18 petere Exp $
2+ $Header: /cvsroot/pgsql/doc/src/sgml/Attic/plsql.sgml,v 2.55 2002/04/09 02:31:58 momjian Exp $
33-->
44
55<chapter id="plpgsql">
@@ -762,7 +762,7 @@ CREATE FUNCTION logfunc2 (TEXT) RETURNS TIMESTAMP AS '
762762 <para>
763763 If the expression's result data type doesn't match the variable's
764764 data type, or the variable has a specific size/precision
765- (as for <type>char(20)</type>), the result value will be implicitly
765+ (like <type>char(20)</type>), the result value will be implicitly
766766 converted by the <application>PL/pgSQL</application> interpreter using
767767 the result type's output-function and
768768 the variable type's input-function. Note that this could potentially
@@ -880,7 +880,7 @@ PERFORM <replaceable>query</replaceable>;
880880 This executes a <literal>SELECT</literal>
881881 <replaceable>query</replaceable> and discards the
882882 result. <application>PL/pgSQL</application> variables are substituted
883- into the query as usual.
883+ in the query as usual.
884884 </para>
885885
886886 <note>
@@ -927,7 +927,7 @@ EXECUTE <replaceable class="command">query-string</replaceable>;
927927 <para>
928928 Note in particular that no substitution of <application>PL/pgSQL</>
929929 variables is done on the query string. The values of variables must
930- be insertedinto the query string as it is constructed.
930+ be insertedin the query string as it is constructed.
931931 </para>
932932
933933 <para>
@@ -1441,16 +1441,16 @@ END LOOP;
14411441 <title>Cursors</title>
14421442
14431443 <para>
1444- Rather than executing a whole query at once, it is possible to
1445- set up a <firstterm>cursor</> that encapsulates the query, and
1446- then read the query result a few rows at a time.
1447- for doing this is to avoid memory overrun when the result contains
1448- a large number of rows.
(However, <application>PL/pgSQL</> users
1449- don't normally need to worry about that, since FOR loops automatically
1450- use a cursor internally to avoid memory problems.)
1451- possibility is that a function can return a reference to a cursor
1452- that it has set up, allowing the caller to read the rows.
1453- provides one way of returning a row set from a function.
1444+ Rather than executing a whole query at once, it is possible to set
1445+ up a <firstterm>cursor</> that encapsulates the query, and then read
1446+ the query result a few rows at a time. One reason for doing this is
1447+ to avoid memory overrun when the result contains a large number of
1448+ rows. (However, <application>PL/pgSQL</> users don't normally need
1449+ to worry about that, since FOR loops automatically use a cursor
1450+ internally to avoid memory problems.) A more interesting usage is to
1451+ return a reference to a cursor that it has created, allowing the
1452+ caller to read the rows. This provides one way of returning multiple
1453+ rows and columns from a function.
14541454 </para>
14551455
14561456 <sect2 id="plpgsql-cursor-declarations">
@@ -1498,11 +1498,10 @@ DECLARE
14981498
14991499 <para>
15001500 Before a cursor can be used to retrieve rows, it must be
1501- <firstterm>opened</>. (This is the equivalent action to
1502- the SQL command <command>DECLARE CURSOR</>.)
1503- <application>PL/pgSQL</> has four forms of the OPEN statement,
1504- two of which are for use with unbound cursor variables
1505- and the other two for use with bound cursor variables.
1501+ <firstterm>opened</>. (This is the equivalent action to the SQL
1502+ command <command>DECLARE CURSOR</>.) <application>PL/pgSQL</> has
1503+ four forms of the OPEN statement, two of which use unbound cursor
1504+ variables and the other two use bound cursor variables.
15061505 </para>
15071506
15081507 <sect3>
@@ -1518,7 +1517,7 @@ OPEN <replaceable>unbound-cursor</replaceable> FOR SELECT ...;
15181517have been declared as an unbound cursor (that is, as a simple
15191518<type>refcursor</> variable). The SELECT query is treated
15201519in the same way as other SELECTs in <application>PL/pgSQL</>:
1521- <application>PL/pgSQL</> variable names are substituted for ,
1520+ <application>PL/pgSQL</> variable names are substituted,
15221521and the query plan is cached for possible re-use.
15231522
15241523<programlisting>
@@ -1539,8 +1538,8 @@ OPEN <replaceable>unbound-cursor</replaceable> FOR EXECUTE <replaceable class="c
15391538to execute. The cursor cannot be open already, and it must
15401539have been declared as an unbound cursor (that is, as a simple
15411540<type>refcursor</> variable). The query is specified as a
1542- string expression in the same way asfor the EXECUTE command.
1543- As usual, this gives flexibilityfor the queryto vary
1541+ string expression in the same way asin the EXECUTE command.
1542+ As usual, this gives flexibilityso the querycan vary
15441543from one run to the next.
15451544
15461545<programlisting>
@@ -1562,7 +1561,7 @@ OPEN <replaceable>bound-cursor</replaceable> <optional> ( <replaceable>argument_
15621561The cursor cannot be open already. A list of actual argument
15631562value expressions must appear if and only if the cursor was
15641563declared to take arguments. These values will be substituted
1565- into the query.
1564+ in the query.
15661565The query plan for a bound cursor is always considered
15671566cacheable --- there is no equivalent of EXECUTE in this case.
15681567
@@ -1593,7 +1592,7 @@ OPEN curs3(42);
15931592 </para>
15941593
15951594 <para>
1596- All Portals are implicitly closed atend of transaction. Therefore
1595+ All Portals are implicitly closed at transaction end . Therefore
15971596 a <type>refcursor</> value is useful to reference an open cursor
15981597 only until the end of the transaction.
15991598 </para>
@@ -1608,7 +1607,7 @@ FETCH <replaceable>cursor</replaceable> INTO <replaceable>target</replaceable>;
16081607
16091608 FETCH retrieves the next row from the cursor into a target,
16101609which may be a row variable, a record variable, or a comma-separated
1611- list of simple variables, justas for SELECT INTO. As with
1610+ list of simple variables, justlike SELECT INTO. As with
16121611SELECT INTO, the special variable FOUND may be checked to see
16131612whether a row was obtained or not.
16141613
@@ -1633,6 +1632,70 @@ CLOSE <replaceable>cursor</replaceable>;
16331632
16341633<programlisting>
16351634CLOSE curs1;
1635+ </programlisting>
1636+ </para>
1637+ </sect3>
1638+
1639+ <sect3>
1640+ <title>Returning Cursors</title>
1641+
1642+ <para>
1643+
1644+ <application>PL/pgSQL</> functions can return cursors to the
1645+ caller. This is used to return multiple rows or columns from the
1646+ function. The function opens the cursor and returns the cursor
1647+ name to the caller. The caller can then FETCH rows from the
1648+ cursor. The cursor can be CLOSEd by the caller, or it will be
1649+ closed automatically when the transaction closes.
1650+
1651+ </para>
1652+
1653+ <para>
1654+ The cursor name returned by the function can be specified by the
1655+ caller or automatically generated. The following example shows
1656+ how a cursor name can be supplied by the caller:
1657+
1658+ <programlisting>
1659+ CREATE TABLE test (col text);
1660+ INSERT INTO test VALUES ('123');
1661+
1662+ CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
1663+ BEGIN
1664+ OPEN $1 FOR SELECT col FROM test;
1665+ RETURN $1;
1666+ END;
1667+ ' LANGUAGE 'plpgsql';
1668+
1669+ BEGIN;
1670+ SELECT reffunc('funccursor');
1671+ FETCH ALL IN funccursor;
1672+ COMMIT;
1673+ </programlisting>
1674+ </para>
1675+
1676+ <para>
1677+ The following example uses automatic cursor name generation:
1678+
1679+ <programlisting>
1680+ CREATE FUNCTION reffunc2() RETURNS refcursor AS '
1681+ DECLARE
1682+ ref refcursor;
1683+ BEGIN
1684+ OPEN ref FOR SELECT col FROM test;
1685+ RETURN ref;
1686+ END;
1687+ ' LANGUAGE 'plpgsql';
1688+
1689+ BEGIN;
1690+ SELECT reffunc2();
1691+
1692+ reffunc2
1693+ --------------------
1694+ <unnamed cursor 1>
1695+ (1 row)
1696+
1697+ FETCH ALL IN "<unnamed cursor 1>";
1698+ COMMIT;
16361699</programlisting>
16371700 </para>
16381701 </sect3>