Provided by: postgresql-client-8.0_8.0.7-2build1_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.

       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 command that will provide the rows to  be  returned  by
              the  cursor. Refer to SELECT [select(7)] for further information
              about valid queries.

       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.

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