A SQL 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 having to re-execute the query with
different LIMIT and OFFSET clauses.
Used within a programming Application Programming Interface (API), cursors are often used to
allow multiple queries to be executed to a single database backend, which are then tracked and managed separately by the
application through references to the cursor. This prevents having to store all of the results in memory within the
application.
Cursors are often abstracted within a programming API (such as
libpq++
's PgCursor
class), though they can also be directly created and manipulated through standard SQL commands. For the sake of generality,
this section uses
psql
to demonstrate the fundamental concepts of cursors with SQL. 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 populating the cursor with information about the result set returned from the executed query. The
FETCH command lets you pull rows from an open cursor. 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.
Note: If you are interested in learning how to use cursors within a particular API, consult that API's
documentation.
A cursor is both created and executed with the DECLARE SQL command. This process is also
referred to as "opening" a cursor. 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
-
cursorname
is the name of the cursor to create.
-
[ BINARY ]
-
The optional BINARY keyword causes 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.
-
[ INSENSITIVE ] [ SCROLL ]
-
The INSENSITIVE and SCROLL keywords exist for
compliance 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. Since PostgreSQL requires that cursors be defined
within transaction blocks, this behavior is already implied. The SCROLL SQL
keyword exists to specify that multiple rows can be selected at a time from the cursor. This is the default in
PostgreSQL, even if unspecified.
-
CURSOR FOR
query
-
query
is the complete query whose result set will be accessible by the cursor, when executed.
-
[ FOR { READ ONLY | UPDATE [ OF
column
[, ...] ] } ]
-
As of PostgreSQL 7.1.x, cursors may only be defined as READ ONLY, and the
FOR clause is therefore superfluous.
Example 7-42 begins a transaction block with the BEGIN
keyword, and opens a cursor named all_books with
SELECT * FROM books as its executed SQL statement.
Example 7-42. Declaring a cursor
booktown=#
BEGIN;
BEGIN
booktown=#
DECLARE all_books CURSOR
booktown-#
FOR SELECT * FROM books;
SELECT
The SELECT message returned from Example 7-42 indicates that
the statement was executed successfully. This means that the rows retrieved by the query are now accessible from the
all_books cursor.
You may retrieve rows from a cursor with the FETCH SQL command.
Here is the syntax for the FETCH SQL command:
FETCH [ FORWARD | BACKWARD | RELATIVE ]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM }
cursor
In this syntax diagram,
cursor
is the name of the cursor from which to 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 or
behind of the current location. The FORWARD and
BACKWARD keywords may be used to specify the direction, though the default is forward.
The RELATIVE keyword is a noise term made available for SQL92 compliance.
|
Warning
|
|
The ABSOLUTE keyword can be used, but absolute cursor positioning and fetching are not supported as of PostgreSQL 7.1.x; the cursor will still use relative positioning and provide a notice
regarding the state of absolute positioning being unsupported.
|
Following the direction you may optionally specify a quantity. This quantity may either be a literal number of
rows to be returned (in the form of an integer constant) or one of several keywords. The
ALL keyword causes returns all rows from the current cursor position. 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.
There is no functional difference between the IN and
FROM keywords, but one of these must be specified.
Example 7-43 fetches the first four rows stored in the result set pointed to by the
all_books 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.
Example 7-43. Fetching rows from a cursor
booktown=#
FETCH 4 FROM all_books;
id | title | author_id | subject_id
------+-----------------------+-----------+------------
7808 | The Shining | 4156 | 9
4513 | Dune | 1866 | 15
4267 | 2001: A Space Odyssey | 2001 | 15
1608 | The Cat in the Hat | 1809 | 2
(4 rows)
booktown=#
FETCH NEXT FROM all_books;
id | title | author_id | subject_id
------+-----------------------------+-----------+------------
1590 | Bartholomew and the Oobleck | 1809 | 2
(1 row)
booktown=#
FETCH PRIOR FROM all_books;
id | title | author_id | subject_id
------+--------------------+-----------+------------
1608 | The Cat in the Hat | 1809 | 2
(1 row)
A cursor maintains a position in the result set of its referenced SELECT statement. You can use the
MOVE command to move the cursor to a specified row position in that result
set. Here is the syntax for the MOVE command:
MOVE [ FORWARD | BACKWARD | RELATIVE ]
[ # | ALL | NEXT | PRIOR ]
{ IN | FROM }
cursor
As you can see, the syntax is very similar to FETCH. However, the
MOVE command does not retrieve any rows and only moves the current position of the specified
cursor
. The amount is specified by either an integer constant, the ALL keyword (to move
as far as can be moved in the specified direction), NEXT, or
PRIOR. Example 7-44 moves the cursor forward 10 rows from its current
position in the result set.
Example 7-44. Moving a cursor
booktown=#
MOVE FORWARD 10
booktown-#
IN all_books;
MOVE
Use the CLOSE command to explicitly close an open cursor. A cursor is also
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 CLOSE, where
cursorname
is the name of
the cursor intended to be closed:
CLOSE
cursorname
Example 7-45 closes the all_books cursor, freeing the
associated memory, and rendering the cursor's results inaccessible.
Example 7-45. Closing a cursor
booktown=#
CLOSE all_books;
CLOSE
booktown=#
COMMIT;
COMMIT
