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;