DECLARE
DECLARE — Define a cursor
Synopsis
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
Description
DECLARE allows a user to create a cursor, which can be used to retrieve a small number of rows at a time out of a larger query. After a cursor is created, rows can be fetched from it using FETCH.
Parameters
name
The name of the cursor to be created.
BINARY
Causes the cursor to return data in binary rather than in text format.
INSENSITIVE
Indicates that data retrieved from the cursor should be unaffected by updates to the underlying tables that occurred after the cursor was created. This is the default behavior.
SCROLL
NO SCROLL
SCROLL specifies that the cursor can be used to retrieve rows in a non-sequential manner (e.g., backwards). Depending on the complexity of the query's execution plan, specifying SCROLL may incur a performance penalty in query execution time. NO SCROLL specifies that the cursor cannot be used to retrieve rows in a non-sequential manner. The default is to allow scrolling in some cases, but this is not the same as specifying SCROLL. See Notes for details.
WITH HOLD
WITHOUT HOLD
WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits. WITHOUT HOLD specifies that the cursor cannot be used outside of the transaction that created it. If neither is specified, WITHOUT HOLD is the default.
query
A SELECT or VALUES command that provides the rows to be returned by the cursor.
The keywords BINARY, INSENSITIVE, and SCROLL can appear in any order.
Notes
Normal cursors return data in text format, the same as produced by SELECT. The BINARY option specifies that the cursor should return data in binary format. This reduces the conversion effort between server and client, but places more work on the programmer to handle platform-dependent binary data formats. For example, if a query returns a value of one from an integer column, a default cursor will get the string 1, whereas a binary cursor will get a 4-byte internal representation of the value (in big-endian byte order).
Binary cursors should be used with caution. Many applications, including psql, are not prepared to handle binary cursors and still expect data to arrive in text format.
Unless WITH HOLD is specified, the cursor created by this command can only be used within the current transaction. Thus, DECLARE without WITH HOLD is useless outside a transaction block: the cursor would only survive until the statement completes. Therefore the system will report an error if such a command is used outside a transaction block. Use BEGIN and COMMIT (or ROLLBACK) to define a transaction block.
If WITH HOLD is specified and the transaction that created the cursor successfully commits, the cursor can continue to be accessed by subsequent transactions in the same session (but the cursor will be removed if the creating transaction is aborted). A cursor created with WITH HOLD can be closed with an explicit CLOSE command, or it will be closed automatically when the session ends. In the current implementation, rows represented by a held cursor are copied to a temporary file or memory area so that they remain available for subsequent transactions.
WITH HOLD cannot be specified when the query includes FOR UPDATE or FOR SHARE.
The SCROLL option should be specified when defining a cursor that will be used to fetch rows backwards. This is required by the SQL standard.
However, for backward compatibility with earlier versions, the system will allow backwards fetching without SCROLL if the cursor's query plan is simple enough to support it without additional overhead. However, application developers are advised not to rely on fetching rows backwards from a cursor created without SCROLL. If NO SCROLL is specified, backwards fetching is not allowed under any circumstances.
Backwards fetching is also not allowed when the query includes FOR UPDATE or FOR SHARE. Therefore SCROLL cannot be specified in this case.
If the cursor's query includes FOR UPDATE or FOR SHARE, the returned rows are locked when they are first fetched, just as with a regular SELECT with these options. In addition, the returned rows will be the latest version, so these options provide the equivalent of what the SQL standard calls a "sensitive cursor" (specifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an error).
The SQL standard only specifies cursors in embedded SQL. A cursor is considered to be open when it is declared.
Examples
# Declare a cursor:
DECLARE liahona CURSOR FOR SELECT * FROM films;
# For more cursor examples, see FETCH.