DECLARE()			 SQL Commands			     DECLARE()



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



SQL - Language Statements	  2010-12-14			     DECLARE()