DECLARE STATEMENT

Name

DECLARE — Declares a cursor.
   DECLARE cursor [ BINARY ] 
       FOR SELECT query
  

Inputs

BINARY

The BINARY keyword causes the cursor to fetch data in binary rather than in ASCII format.

cursor

The cursor's name.

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.

Outputs

SELECT

The message returned if the SELECT is run successfully.

NOTICE BlankPortalAssignName: portal "cursor" already exists

This error occurs if cursor "cursor" is already declared.

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 ASCII or in binary foramt.

Normal cursors return data in ASCII format. Since data is stored natively in binary format, the system must do a conversion to produce the ASCII format. In addition, ASCII formats are often larger in size than binary format. Once the information comes back in ASCII, the client application often has to convert it to a binary format to manipulate it anyway.

BINARY cursors give you back the data in the native binary representation. So binary cursors will tend to be a little faster since they suffer less conversion overhead. For example, for an integer column, you get a C integer number like ^A using a binary cursor, while you get a string value like '1' using the non binary cursor.

However, ASCII is architecture-neutral whereas binary representation can differ between different machine architectures. Therefore, if your client machine and server machine use different representations, you will probably not want your data returned in binary format. Again, 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.

PostgreSQL does not have an explicit OPEN cursor statement; a cursor is considered to be open when it is DECLAREd.

Usage

To declare a cursor:

   DECLARE liahona CURSOR
      FOR SELECT * FROM films;
  

Compatibility

SQL92

SQL92 specifies some additional capabilities for the DECLARE statement:

   DECLARE cursor [ INSENSITIVE ] [ SCROLL ] CURSOR 
       FOR SELECT expression
       [ ORDER BY column [, ... ] [ ASC | DESC ]
       [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
   

INSENSITIVE

UPDATE and DELETE CURRENT operations are not allowed if the cursor is declared to be INSENSITIVE.

SCROLL

If SCROLL is not specified, only FETCH NEXT will be allowed.

FOR READ ONLY/UPDATE

If READ ONLY is specified, UPDATE/DELETE CURRENT operations will not be allowed.