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.

FETCH [ FORWARD | BACKWARD]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM } 
cursor

FETCH 4 FROM order_cur;

CLOSE
Cursorname;