All access to cursors in PL/pgSQL goes through
cursor variables, which are always of the special data type
refcursor. One way to create a cursor variable
is just to declare it as a variable of type refcursor.
Another way is to use the cursor declaration syntax,
which in general is:
name CURSOR [ ( arguments ) ] FOR select_query ;
(FOR may be replaced by IS for Oracle
compatibility.) arguments, if any,
are a comma-separated list of name
datatype pairs that define names to
be replaced by parameter values in the given query. The actual
values to substitute for these names will be specified later,
when the cursor is opened.
Some examples:
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * from tenk1;
curs3 CURSOR (key int) IS SELECT * from tenk1 where unique1 = key;
All three of these variables have the data type refcursor,
but the first may be used with any query, while the second has
a fully specified query already bound to it, and the last
has a parameterized query bound to it. (key will be
replaced by an integer parameter value when the cursor is opened.)
The variable curs1
is said to be unbound since it is not bound to
any particular query.
Before a cursor can be used to retrieve rows, it must be
opened. (This is the equivalent action to the SQL
command DECLARE CURSOR.) PL/pgSQL has
four forms of the OPEN statement, two of which use unbound cursor
variables and the other two use bound cursor variables.
OPEN unbound-cursor FOR SELECT ...;
The cursor variable is opened and given the specified query
to execute. The cursor cannot be open already, and it must
have been declared as an unbound cursor (that is, as a simple
refcursor variable). The SELECT query is treated
in the same way as other SELECT statements in PL/pgSQL:
PL/pgSQL variable names are substituted,
and the query plan is cached for possible re-use.
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN unbound-cursor FOR EXECUTE query-string;
The cursor variable is opened and given the specified query
to execute. The cursor cannot be open already, and it must
have been declared as an unbound cursor (that is, as a simple
refcursor variable). The query is specified as a
string expression in the same way as in the EXECUTE command.
As usual, this gives flexibility so the query can vary
from one run to the next.
OPEN curs1 FOR EXECUTE ''SELECT * FROM '' || quote_ident($1);
OPEN bound-cursor [ ( argument_values ) ];
This form of OPEN is used to open a cursor variable whose query
was bound to it when it was declared.
The cursor cannot be open already. 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.
The query plan for a bound cursor is always considered
cacheable --- there is no equivalent of EXECUTE in this case.
OPEN curs2;
OPEN curs3(42);
Once a cursor has been opened, it can be manipulated with the
statements described here.
These manipulations need not occur in the same function that
opened the cursor to begin with. You can return a refcursor
value out of a function and let the caller operate on the cursor.
(Internally, a refcursor value is simply the string name
of a Portal containing the active query for the cursor. This name
can be passed around, assigned to other refcursor variables,
and so on, without disturbing the Portal.)
All Portals are implicitly closed at transaction end. Therefore
a refcursor value is useful to reference an open cursor
only until the end of the transaction.
FETCH cursor INTO target;
FETCH retrieves the next row from the cursor into a target,
which may be a row variable, a record variable, or a comma-separated
list of simple variables, just like SELECT INTO. As with
SELECT INTO, the special variable FOUND may be
checked to see whether a row was obtained or not.
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo,bar,baz;
CLOSE cursor;
CLOSE closes the Portal underlying an open cursor.
This can be used to release resources earlier than end of
transaction, or to free up the cursor variable to be opened again.
CLOSE curs1;
PL/pgSQL functions can return cursors to the
caller. This is used to return multiple rows or columns from the
function. The function opens the cursor and returns the cursor
name to the caller. The caller can then FETCH rows from the
cursor. The cursor can be closed by the caller, or it will be
closed automatically when the transaction closes.
The cursor name returned by the function can be specified by the
caller or automatically generated. The following example shows
how a cursor name can be supplied by the caller:
CREATE TABLE test (col text);
INSERT INTO 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;
The following example uses automatic cursor name generation:
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE 'plpgsql';
BEGIN;
SELECT reffunc2();
reffunc2
--------------------
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
