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
[ [ NO ] SCROLL ] CURSOR [ (arguments
) ] FORquery
;
(FOR
can be replaced by
IS
for Oracle compatibility.) If SCROLL
is specified, the cursor will be capable of
scrolling backward; if NO SCROLL
is
specified, backward fetches will be rejected; if neither
specification appears, it is query-dependent whether backward
fetches will be allowed. 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) FOR SELECT * FROM tenk1 WHERE unique1 = key;
All three of these variables have the data type refcursor
, but the first can 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.
Bound cursor variables can also be used without explicitly
opening the cursor, via the FOR
statement described in Section 42.7.4.
OPEN
FOR
query
OPENunbound_cursorvar
[ [ NO ] SCROLL ] 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 variable (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. When a PL/pgSQL variable is substituted into the
cursor query, the value that is substituted is the one it has at
the time of the OPEN
; subsequent
changes to the variable will not affect the cursor's behavior. The
SCROLL
and NO
SCROLL
options have the same meanings as for a bound
cursor.
An example:
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
OPEN FOR
EXECUTE
OPENunbound_cursorvar
[ [ NO ] SCROLL ] FOR EXECUTEquery_string
[ USINGexpression
[, ... ] ];
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 variable (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 plan can vary from one run to the next
(see Section 42.10.2), and it also
means that variable substitution is not done on the command string.
As with EXECUTE
, parameter values can
be inserted into the dynamic command via format()
and USING
.
The SCROLL
and NO SCROLL
options have the same meanings as for a
bound cursor.
An example:
OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
In this example, the table name is inserted into the query via
format()
. The comparison value for
col1
is inserted via a USING
parameter, so it needs no quoting.
OPENbound_cursorvar
[ ( [argument_name
:= ]argument_value
[, ...] ) ];
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. Notice that SCROLL
and NO SCROLL
cannot be specified in OPEN
, as the
cursor's scrolling behavior was already determined.
Argument values can be passed using either positional or named
notation. In positional notation, all arguments are specified in
order. In named notation, each argument's name is specified using
:=
to separate it from the argument
expression. Similar to calling functions, described in Section 4.3, it is also
allowed to mix positional and named notation.
Examples (these use the cursor declaration examples above):
OPEN curs2; OPEN curs3(42); OPEN curs3(key := 42);
Because variable substitution is done on a bound cursor's query,
there are really two ways to pass values into the cursor: either
with an explicit argument to OPEN
, or
implicitly by referencing a PL/pgSQL variable in the query. However, only
variables declared before the bound cursor was declared will be
substituted into it. In either case the value to be passed is
determined at the time of the OPEN
.
For example, another way to get the same effect as the curs3
example above is
DECLARE key integer; curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key; BEGIN key := 42; OPEN curs4;
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.
FETCH
FETCH [direction
{ FROM | IN } ]cursor
INTOtarget
;
FETCH
retrieves the next row from
the cursor into a target, which might be a row variable, a record
variable, or a comma-separated list of simple variables, just like
SELECT INTO
. If there is no next row,
the target is set to NULL(s). As with SELECT
INTO
, the special variable FOUND
can be checked to see whether a row was
obtained or not.
The direction
clause
can be any of the variants allowed in the SQL FETCH command except the ones that can
fetch more than one row; namely, it can be NEXT
, PRIOR
,
FIRST
, LAST
, ABSOLUTE
count
, RELATIVE
count
, FORWARD
, or BACKWARD
.
Omitting direction
is the
same as specifying NEXT
. direction
values that require
moving backward are likely to fail unless the cursor was declared
or opened with the SCROLL
option.
cursor
must be the
name of a refcursor
variable that
references an open cursor portal.
Examples:
FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x;
MOVE
MOVE [direction
{ FROM | IN } ]cursor
;
MOVE
repositions a cursor without
retrieving any data. MOVE
works
exactly like the FETCH
command, except
it only repositions the cursor and does not return the row moved
to. As with SELECT INTO
, the special
variable FOUND
can be checked to see
whether there was a next row to move to.
The direction
clause
can be any of the variants allowed in the SQL FETCH command, namely NEXT
, PRIOR
,
FIRST
, LAST
, ABSOLUTE
count
, RELATIVE
count
, ALL
, FORWARD
[
count
| ALL
], or BACKWARD
[ count
| ALL
]. Omitting direction
is the same as specifying
NEXT
. direction
values that require
moving backward are likely to fail unless the cursor was declared
or opened with the SCROLL
option.
Examples:
MOVE curs1; MOVE LAST FROM curs3; MOVE RELATIVE -2 FROM curs4; MOVE FORWARD 2 FROM curs4;
UPDATE/DELETE WHERE CURRENT OF
UPDATEtable
SET ... WHERE CURRENT OFcursor
; DELETE FROMtable
WHERE CURRENT OFcursor
;
When a cursor is positioned on a table row, that row can be
updated or deleted using the cursor to identify the row. There are
restrictions on what the cursor's query can be (in particular, no
grouping) and it's best to use FOR
UPDATE
in the cursor. For more information see the DECLARE reference page.
An example:
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
CLOSE
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; -- need to be in a transaction to use cursors. 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;
There is a variant of the FOR
statement that allows iterating through the rows returned by a
cursor. The syntax is:
[ <<label
>> ] FORrecordvar
INbound_cursorvar
[ ( [argument_name
:= ]argument_value
[, ...] ) ] LOOPstatements
END LOOP [label
];
The cursor variable must have been bound to some query when it
was declared, and it cannot
be open already. The FOR
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 OPEN
(see Section 42.7.2.3).
The variable recordvar
is automatically defined as type record
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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.