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.
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.
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,
COMMIT
and
ROLLBACK
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.
