Rather than executing a whole query at once, it is possible to set
up a cursor that encapsulates the query, and then read
the query result a few rows at a time. One reason for doing this is
to avoid memory overrun when the result contains a large number of
rows. (However, PL/pgSQL users do not normally need
to worry about that, since FOR
loops automatically use a cursor
internally to avoid memory problems.) A more interesting usage is to
return a reference to a cursor that a function has created, allowing the
caller to read the rows. This provides an efficient way to return
large row sets from functions.
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
) ] FORquery
;
(FOR
may be replaced by IS
for
Oracle compatibility.)
arguments
, if specified, is a
comma-separated list of 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.
name
datatype
Some examples:
DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) 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
three forms of the OPEN
statement, two of which use unbound
cursor variables while the third uses a bound cursor variable.
OPENunbound_cursor
FORquery
;
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 must be a
SELECT
, or something else that returns rows
(such as EXPLAIN
). The query
is treated in the same way as other SQL commands in
PL/pgSQL: PL/pgSQL
variable names are substituted, and the query plan is cached for
possible reuse.
An example:
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPENunbound_cursor
FOR EXECUTEquery_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.
An example:
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
OPENbound_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.
Examples:
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 so-called 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 usable to reference an open cursor
only until the end of the transaction.
FETCHcursor
INTOtarget
;
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.
An example:
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.
An example:
CLOSE curs1;
PL/pgSQL functions can return cursors to the caller. This is useful to return multiple rows or columns, especially with very large result sets. To do this, the function opens the cursor and returns the cursor name to the caller (or simply opens the cursor using a portal name specified by or otherwise known 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 portal name used for a cursor can be specified by the
programmer or automatically generated. To specify a portal name,
simply assign a string to the refcursor
variable before
opening it. The string value of the refcursor
variable
will be used by OPEN
as the name of the underlying portal.
However, if the refcursor
variable is null,
OPEN
automatically generates a name that does not
conflict with any existing portal, and assigns it to the
refcursor
variable.
A bound cursor variable is initialized to the string value representing its name, so that the portal name is the same as the cursor variable name, unless the programmer overrides it by assignment before opening the cursor. But an unbound cursor variable defaults to the null value initially, so it will receive an automatically-generated unique name, unless overridden.
The following example shows one way 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;
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;