Provided by: postgresql-client-8.2_8.2.7-1_i386 bug

NAME

       DECLARE - define a cursor

SYNOPSIS

       DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
           CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
           [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

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(7)].

       Normal cursors return data in text format, the same as a  SELECT  would
       produce.  Since  data  is  stored natively in binary format, the system
       must do a conversion to produce the text format. Once  the  information
       comes  back in text form, the client application may need to convert it
       to a binary format to manipulate it. In  addition,  data  in  the  text
       format  is  often  larger  in  size  than  in the binary format. Binary
       cursors return the data in a binary representation  that  may  be  more
       easily manipulated.  Nevertheless, if you intend to display the data as
       text anyway, retrieving it in text form will save you  some  effort  on
       the client side.

       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 field containing the internal
       representation of the value (in big-endian byte order).

       Binary cursors should be used carefully. Many  applications,  including
       psql, are not prepared to handle binary cursors and expect data to come
       back in the text format.

              Note: When the client application uses  the  ‘‘extended  query’’
              protocol  to  issue  a  FETCH command, the Bind protocol message
              specifies whether data is to be  retrieved  in  text  or  binary
              format.   This  choice  overrides  the  way  that  the cursor is
              defined. The concept of a binary cursor as such is thus obsolete
              when  using  extended query protocol — any cursor can be treated
              as either text or binary.

PARAMETERS

       name   The name of the cursor to be created.

       BINARY Causes the cursor to return data in binary rather than  in  text
              format.

       INSENSITIVE
              Indicates   that  data  retrieved  from  the  cursor  should  be
              unaffected by updates to the tables underlying the cursor  while
              the  cursor  exists. In PostgreSQL, all cursors are insensitive;
              this key word  currently  has  no  effect  and  is  present  for
              compatibility with the SQL standard.

       SCROLL

       NO SCROLL
              SCROLL specifies that the cursor may be used to retrieve rows in
              a nonsequential fashion (e.g.,  backward).  Depending  upon  the
              complexity  of the query’s execution plan, specifying SCROLL may
              impose a performance penalty on the query’s execution time.   NO
              SCROLL specifies that the cursor cannot be used to retrieve rows
              in a nonsequential fashion. The default is to allow scrolling in
              some cases; this is not the same as specifying SCROLL. See Notes
              [declare(7)] for details.

       WITH HOLD

       WITHOUT HOLD
              WITH HOLD specifies that the cursor  may  continue  to  be  used
              after  the  transaction  that  created  it successfully commits.
              WITHOUT HOLD specifies that the cursor cannot be used outside of
              the  transaction  that  created  it. If neither WITHOUT HOLD nor
              WITH HOLD is specified, WITHOUT HOLD is the default.

       query  A SELECT [select(7)] or VALUES [values(7)]  command  which  will
              provide the rows to be returned by the cursor.

       FOR READ ONLY

       FOR UPDATE
              FOR  READ ONLY indicates that the cursor will be used in a read-
              only mode. FOR UPDATE indicates that the cursor will be used  to
              update  tables. Since cursor updates are not currently supported
              in PostgreSQL, specifying FOR UPDATE will cause an error message
              and specifying FOR READ ONLY has no effect.

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

       The  key words BINARY, INSENSITIVE, and SCROLL may appear in any order.

NOTES

       Unless WITH HOLD is specified, the cursor created by this  command  can
       only be used within the current transaction. Thus, DECLARE without WITH
       HOLD is useless outside a transaction block: the cursor  would  survive
       only  to  the completion of the statement. Therefore PostgreSQL reports
       an error if this command is used  outside  a  transaction  block.   Use
       BEGIN  [begin(7)],  COMMIT  [commit(7)]  and  ROLLBACK [rollback(7)] to
       define a transaction block.

       If WITH HOLD is specified and the transaction that created  the  cursor
       successfully  commits,  the  cursor  can  continue  to  be  accessed by
       subsequent transactions in the  same  session.  (But  if  the  creating
       transaction  is  aborted, the cursor is removed.) A cursor created with
       WITH HOLD is closed when an explicit CLOSE command is issued on it,  or
       the  session  ends. In the current implementation, the rows represented
       by a held cursor are copied into a temporary file  or  memory  area  so
       that they remain available for subsequent transactions.

       The  SCROLL option should be specified when defining a cursor that will
       be used to fetch backwards. This  is  required  by  the  SQL  standard.
       However, for compatibility with earlier versions, PostgreSQL will allow
       backward fetches without SCROLL, if the cursor’s query plan  is  simple
       enough  that  no  extra  overhead  is  needed  to  support it. However,
       application developers are  advised  not  to  rely  on  using  backward
       fetches  from  a  cursor  that  has not been created with SCROLL. If NO
       SCROLL is specified, then backward fetches are disallowed in any  case.

       The SQL standard only makes provisions for cursors in embedded SQL. The
       PostgreSQL server does not implement an OPEN statement for  cursors;  a
       cursor  is  considered  to be open when it is declared.  However, ECPG,
       the embedded SQL preprocessor for PostgreSQL, supports the standard SQL
       cursor   conventions,   including  those  involving  DECLARE  and  OPEN
       statements.

       You can see all available cursors by  querying  the  pg_cursors  system
       view.

EXAMPLES

       To declare a cursor:

       DECLARE liahona CURSOR FOR SELECT * FROM films;

       See FETCH [fetch(7)] for more examples of cursor usage.

COMPATIBILITY

       The  SQL  standard  allows cursors only in embedded SQL and in modules.
       PostgreSQL permits cursors to be used interactively.

       The SQL standard allows cursors to update table  data.  All  PostgreSQL
       cursors are read only.

       Binary cursors are a PostgreSQL extension.

SEE ALSO

       CLOSE [close(7)], FETCH [fetch(l)], MOVE [move(l)]