Creating cursors
A cursor in PostgreSQL is a read-only pointer to a fully executed SELECT
statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL backend. By executing a cursor and maintaining a reference to its returned result set, an application can more efficiently manage which rows to retrieve from a result set at different times without re-executing the query with different LIMIT
and OFFSET
clauses.
The four SQL commands involved with PostgreSQL cursors are DECLARE
, FETCH
, MOVE
, and CLOSE
.
The DECLARE
command both defines and opens a cursor, in effect defining the cursor in memory, and then populates the cursor with information about the result set returned from the executed query. A cursor may be declared only within an existing transaction block, so you must execute a BEGIN
command prior to declaring a cursor.
Here is the syntax for DECLARE
:
DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ] CURSOR FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
DECLARE cursorname
is the name of the cursor to create. The optional BINARY
keyword causes the output to be retrieved in binary format instead of standard ASCII; this can be more efficient, though it is only relevant to custom applications as clients such as psql are not built to handle anything but text output. The INSENSITIVE
and SCROLL
keywords exist to comply with the SQL standard, though they each define PostgreSQL's default behavior and are never necessary. The INSENSITIVE
SQL keyword exists to ensure that all data retrieved from the cursor remains unchanged from other cursors or connections. As PostgreSQL requires the cursors to be defined within transaction blocks, this behavior is already implied. The SCROLL
SQL keyword exists to specify that multiple rows at a time can be selected from the cursor. This is the default in PostgreSQL, even if it is unspecified.
The CURSOR FOR
query is the complete query and its result set will be accessible by the cursor when executed.
The [FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
cursors may only be defined as READ ONLY
, and the FOR
clause is, therefore, superfluous.
Let's begin a transaction block with the BEGIN
keyword, and open a cursor named order_cur
with SELECT * FROM orders
as its executed select statement:
BEGIN; DECLARE order_cur CURSOR FOR SELECT * FROM orders;
Once the cursor is successfully declared, it means that the rows retrieved by the query are now accessible from the order_cur
cursor.
Using cursors
In order to retrieve rows from the open cursor, we need to use the FETCH
command. The MOVE
command moves the current location of the cursor within the result set and the CLOSE
command closes the cursor, freeing up any associated memory.
Here is the syntax for the FETCH
SQL command:
FETCH [ FORWARD | BACKWARD] [ # | ALL | NEXT | PRIOR ] { IN | FROM } cursor
cursor
is the name of the cursor from where we can retrieve row data. A cursor
always points to a current position in the executed statement's result set and rows can be retrieved either ahead of the current location or behind it. The FORWARD
and BACKWARD
keywords may be used to specify the direction, though the default is forward. The NEXT
keyword (the default) returns the next single row from the current cursor position. The PRIOR
keyword causes the single row preceding the current cursor position to be returned.
Let's consider an example that fetches the first four rows stored in the result set, pointed to by the order_cur
cursor. As a direction is not specified, FORWARD
is implied. It then uses a FETCH
statement with the NEXT
keyword to select the fifth row, and then another FETCH
statement with the PRIOR
keyword to again select the fourth retrieved row.
FETCH 4 FROM order_cur;
In this case, the first four rows will be fetched.
Closing a cursor
You can use the CLOSE
command to explicitly close an open cursor. A cursor can also be implicitly closed if the transaction block that it resides within is committed with the COMMIT
command, or rolled back with the ROLLBACK
command.
Here is the syntax for the CLOSE
command, where Cursorname
is the name of the cursor intended to be closed:
CLOSE Cursorname;