DECLARE(l)                                             DECLARE(l)

NAME
       DECLARE - Defines a cursor for table access

SYNOPSIS
       DECLARE cursor [ BINARY ] [ INSENSITIVE ] [ SCROLL ]
           CURSOR FOR query
           [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] ]

   INPUTS
       cursor 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 opera-
              tions occur within transactions in Postgres 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 Postgres this keyword has
              no effect.

       query  An SQL query which will provide the rows to be gov-
              erned 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  readonly  mode. Since this is the only
              cursor access mode available in Postgres this  key-
              word has no effect.

       UPDATE SQL92  keyword  indicating  that the cursor will be
              used to update tables. Since cursor updates are not
              currently  supported  in Postgres this keyword pro-
              vokes an informational error message.

       column Column(s) to be updated.  Since cursor updates  are
              not  currently  supported  in  Postgres  the UPDATE
              clause provokes an informational error message.

   OUTPUTS
       SELECT The message returned if the SELECT is run  success-
              fully.

       NOTICE This error occurs if cursor is already declared.

       ERROR:  Named  portals  may only be used in begin/end
              transaction blocks
              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(l).

       Normal cursors return data in text format, either ASCII or
       another encoding scheme  depending  on  how  the  Postgres
       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 applica-
       tion may need to convert it to a binary format to  manipu-
       late  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  and Postgres 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(l),  commit(l)  and rollback(l) to define a transac-
       tion block.

       In SQL92 cursors are only available in embedded SQL (ESQL)
       applications.   The Postgres 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 Postgres, 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.
       Postgres  permits cursors to be used interactively.  SQL92
       allows embedded or  modular  cursors  to  update  database
       information.   All  Postgres  cursors  are  readonly.  The
       BINARY keyword is a Postgres extension.

SQL - Language Statements 15 August 1999                        1