DECLARE(l) SQL - Language Statements (2002-11-22) DECLARE(l)
NAME
DECLARE - define a cursor
SYNOPSIS
DECLARE cursorname [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
CURSOR FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ]
INPUTS
cursorname
The name of the cursor to be used in subsequent FETCH
operations.
BINARY
Causes the cursor to fetch data in binary rather than
in text format.
INSENSITIVE
SQL92 keyword indicating that data retrieved from the
cursor should be unaffected by updates from other
processes or cursors. Since cursor operations occur
within transactions in PostgreSQL this is always the
case. This keyword has no effect.
SCROLL
SQL92 keyword indicating that data may be retrieved in
multiple rows per FETCH operation. Since this is
allowed at all times by PostgreSQL this keyword has no
effect.
query
An SQL query which will provide the rows to be governed
by the cursor. Refer to the SELECT statement for
further information about valid arguments.
READ ONLY
SQL92 keyword indicating that the cursor will be used
in a read only mode. Since this is the only cursor
access mode available in PostgreSQL this keyword has no
effect.
UPDATE
SQL92 keyword indicating that the cursor will be used
to update tables. Since cursor updates are not
currently supported in PostgreSQL this keyword provokes
an informational error message.
column
Column(s) to be updated. Since cursor updates are not
currently supported in PostgreSQL the UPDATE clause
Page 1 (printed 3/24/03)
DECLARE(l) SQL - Language Statements (2002-11-22) DECLARE(l)
provokes an informational error message.
OUTPUTS
DECLARE CURSOR
The message returned if the SELECT is run successfully.
WARNING: Closing pre-existing portal "cursorname"
This message is reported if the same cursor name was
already declared in the current transaction block. The
previous definition is discarded.
blocks
ERROR: DECLARE CURSOR may only be used in begin/end transaction
This error occurs if the cursor is not declared within
a transaction block.
DESCRIPTION
DECLARE allows a user to create cursors, which can be used
to retrieve a small number of rows at a time out of a larger
query. Cursors can return data either in text or in binary
format using FETCH [fetch(l)].
Normal cursors return data in text format, either ASCII or
another encoding scheme depending on how the PostgreSQL
backend was built. Since data is stored natively in binary
format, the system must do a conversion to produce the text
format. In addition, text formats are often larger in size
than the corresponding binary format. Once the information
comes back in text form, the client application may need to
convert it to a binary format to manipulate it. BINARY
cursors give you back the data in the native binary
representation.
As an example, if a query returns a value of one from an
integer column, you would get a string of 1 with a default
cursor whereas with a binary cursor you would get a 4-byte
value equal to control-A (^A).
BINARY cursors should be used carefully. User applications
such as psql are not aware of binary cursors and expect data
to come back in a text format.
String representation is architecture-neutral whereas binary
representation can differ between different machine
architectures. PostgreSQL does not resolve byte ordering or
representation issues for binary cursors. Therefore, if
your client machine and server machine use different
representations (e.g., ``big-endian'' versus ``little-
endian''), you will probably not want your data returned in
binary format. However, binary cursors may be a little more
efficient since there is less conversion overhead in the
server to client data transfer.
Page 2 (printed 3/24/03)
DECLARE(l) SQL - Language Statements (2002-11-22) DECLARE(l)
Tip: If you intend to display the data in ASCII,
getting it back in ASCII will save you some effort on
the client side.
NOTES
Cursors are only available in transactions. Use to BEGIN
[begin(l)], COMMIT [commit(l)] and ROLLBACK [rollback(l)] to
define a transaction block.
In SQL92 cursors are only available in embedded SQL (ESQL)
applications. The PostgreSQL backend does not implement an
explicit OPEN cursor statement; a cursor is considered to be
open when it is declared. However, ecpg, the embedded SQL
preprocessor for PostgreSQL, supports the SQL92 cursor
conventions, including those involving DECLARE and OPEN
statements.
USAGE
To declare a cursor:
DECLARE liahona CURSOR
FOR SELECT * FROM films;
COMPATIBILITY
SQL92
SQL92 allows cursors only in embedded SQL and in modules.
PostgreSQL permits cursors to be used interactively. SQL92
allows embedded or modular cursors to update database
information. All PostgreSQL cursors are read only. The
BINARY keyword is a PostgreSQL extension.
Page 3 (printed 3/24/03)