Seth Woolley's Man Viewer

declare(7) - DECLARE - define a cursor - man 7 declare

([section] manual, -k keyword, -K [section] search, -f whatis)
man plain no title

DECLARE(7)                       SQL Commands                       DECLARE(7)

       DECLARE - define a cursor

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

       DECLARE  allows a user to create cursors, which can be used to retrieve
       a small number of rows at a time(1,2,n) out of a  larger  query.  Cursors  can
       return  data either in(1,8) text or in(1,8) binary format using FETCH [fetch(7)].

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

       As  an  example, if(3,n) a query returns a value of one from an integer col-
       umn, you would get a string(3,n) 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(1,8) 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(1,8) 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(1,8) 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.

       name   The name of the cursor to be created.

       BINARY Causes the cursor to return data in(1,8) binary rather than  in(1,8)  text

              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.


       NO SCROLL
              SCROLL specifies that the cursor may be used to retrieve rows in(1,8)
              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(1,8) a nonsequential fashion.

       WITH 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(2,7,2 select_tut)(7)] for further information
              about valid queries.


              FOR READ ONLY indicates that the cursor will be used in(1,8) a  read-
              only  mode. FOR UPDATE indicates that the cursor will be used to
              update(7,n) tables. Since cursor updates are not currently  supported
              in(1,8) PostgreSQL, specifying FOR UPDATE will cause an error(8,n) 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(1,8)  PostgreSQL, the FOR UPDATE clause
              provokes an error(8,n) message.

       The key words BINARY, INSENSITIVE, and SCROLL may appear in(1,8) any  order.

       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(8,n)  if(3,n)  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  subse-
       quent  transactions  in(1,8) the same session. (But if(3,n) 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(1,n) 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(3,n) 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(1,8) any case.

       The SQL standard only makes provisions for cursors in(1,8) embedded SQL. The
       PostgreSQL server does not implement an OPEN statement for  cursors;  a
       cursor  is  considered  to be open(2,3,n) 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-

       To declare a cursor:

       DECLARE liahona CURSOR FOR SELECT * FROM films;

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

       The SQL standard allows cursors only in(1,8) embedded SQL  and  in(1,8)  modules.
       PostgreSQL permits cursors to be used interactively.

       The  SQL  standard  allows cursors to update(7,n) table data. All PostgreSQL
       cursors are read(2,n,1 builtins) only.

       Binary cursors are a PostgreSQL extension.

SQL - Language Statements         2003-11-02                        DECLARE(7)

References for this manual (incoming links)