/usr/man2/cat.l/declare.l.Z

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

       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 for-
       mat  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 col-
       umn, 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 rep-
       resentation 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 for-
              mat.   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 unaf-
              fected 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  compatibil-
              ity 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(l)] 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(l)], COMMIT  [commit(l)]  and  ROLLBACK  [rollback(l)]  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  subse-
       quent  transactions  in the same session. (But if the creating transac-
       tion 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. How-
       ever, 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,  appli-
       cation  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 state-
       ments.

EXAMPLES

       To declare a cursor:

       DECLARE liahona CURSOR FOR SELECT * FROM films;

       See FETCH [fetch(l)] 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(l)], FETCH [fetch(l)], MOVE [move(l)]

SQL - Language Statements         2005-11-05                         DECLARE()