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 don't normally need to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.) A more interesting possibility is that a function can return a reference to a cursor that it has set up, allowing the caller to read the rows. This provides one way of returning a rowset from a function.
All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special datatype 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 datatype 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 are for use with unbound cursor variables and the other two for use with 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 SELECTs in PL/pgSQL: PL/pgSQL variable names are substituted for, 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 for the EXECUTE command. As usual, this gives flexibility for the query to 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 into 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 end of transaction. 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 as for 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;