DECLARE STATEMENT SQL - Language Statements DECLARE STATEMENT Declares a cursor 1998-04-15 DECLARE cursor [ BINARY ] FOR SELECT query 1998-04-15 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. 1998-04-15 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. 1998-04-15 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. 1998-04-15 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. To declare a cursor: DECLARE liahona CURSOR FOR SELECT * FROM films; 1998-04-15 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.