doc/src/sgml/ref/declare.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-DECLARE">
   7  <refmeta>
   8   <refentrytitle id="SQL-DECLARE-TITLE">DECLARE</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>DECLARE</refname>
  14   <refpurpose>define a cursor</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-declare">
  18   <primary>DECLARE</primary>
  19  </indexterm>
  20
  21  <indexterm zone="sql-declare">
  22   <primary>cursor</primary>
  23   <secondary>DECLARE</secondary>
  24  </indexterm>
  25
  26  <refsynopsisdiv>
  27 <synopsis>
  28 DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
  29     CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
  30 </synopsis>
  31  </refsynopsisdiv>
  32
  33  <refsect1>
  34   <title>Description</title>
  35
  36   <para>
  37    <command>DECLARE</command> allows a user to create cursors, which
  38    can be used to retrieve
  39    a small number of rows at a time out of a larger query.
  40    After the cursor is created, rows are fetched from it using
  41    <xref linkend="sql-fetch" endterm="sql-fetch-title">.
  42   </para>
  43  </refsect1>
  44
  45  <refsect1>
  46   <title>Parameters</title>
  47
  48   <variablelist>
  49    <varlistentry>
  50     <term><replaceable class="parameter">name</replaceable></term>
  51     <listitem>
  52      <para>
  53       The name of the cursor to be created.
  54      </para>
  55     </listitem>
  56    </varlistentry>
  57
  58    <varlistentry>
  59     <term><literal>BINARY</literal></term>
  60     <listitem>
  61      <para>
  62       Causes the cursor to return data in binary rather than in text format.
  63      </para>
  64     </listitem>
  65    </varlistentry>
  66
  67    <varlistentry>
  68     <term><literal>INSENSITIVE</literal></term>
  69     <listitem>
  70      <para>
  71       Indicates that data retrieved from the cursor should be
  72       unaffected by updates to the table(s) underlying the cursor that occur
  73       after the cursor is created.  In <productname>PostgreSQL</productname>,
  74       this is the default behavior; so this key word has no
  75       effect and is only accepted for compatibility with the SQL standard.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79
  80    <varlistentry>
  81     <term><literal>SCROLL</literal></term>
  82     <term><literal>NO SCROLL</literal></term>
  83     <listitem>
  84      <para>
  85       <literal>SCROLL</literal> specifies that the cursor can be used
  86       to retrieve rows in a nonsequential fashion (e.g.,
  87       backward). Depending upon the complexity of the query's
  88       execution plan, specifying <literal>SCROLL</literal> might impose
  89       a performance penalty on the query's execution time.
  90       <literal>NO SCROLL</literal> specifies that the cursor cannot be
  91       used to retrieve rows in a nonsequential fashion.  The default is to
  92       allow scrolling in some cases; this is not the same as specifying
  93       <literal>SCROLL</literal>. See <xref linkend="sql-declare-notes"
  94       endterm="sql-declare-notes-title"> for details.
  95      </para>
  96     </listitem>
  97    </varlistentry>
  98
  99    <varlistentry>
 100     <term><literal>WITH HOLD</literal></term>
 101     <term><literal>WITHOUT HOLD</literal></term>
 102     <listitem>
 103      <para>
 104       <literal>WITH HOLD</literal> specifies that the cursor can
 105       continue to be used after the transaction that created it
 106       successfully commits.  <literal>WITHOUT HOLD</literal> specifies
 107       that the cursor cannot be used outside of the transaction that
 108       created it. If neither <literal>WITHOUT HOLD</literal> nor
 109       <literal>WITH HOLD</literal> is specified, <literal>WITHOUT
 110       HOLD</literal> is the default.
 111      </para>
 112     </listitem>
 113    </varlistentry>
 114
 115    <varlistentry>
 116     <term><replaceable class="parameter">query</replaceable></term>
 117     <listitem>
 118      <para>
 119       A <xref linkend="sql-select" endterm="sql-select-title"> or
 120       <xref linkend="sql-values" endterm="sql-values-title"> command
 121       which will provide the rows to be returned by the cursor.
 122      </para>
 123     </listitem>
 124    </varlistentry>
 125   </variablelist>
 126
 127   <para>
 128    The key words <literal>BINARY</literal>,
 129    <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
 130    appear in any order.
 131   </para>
 132  </refsect1>
 133
 134  <refsect1 id="sql-declare-notes">
 135   <title id="sql-declare-notes-title">Notes</title>
 136
 137   <para>
 138    Normal cursors return data in text format, the same as a
 139    <command>SELECT</> would produce.  The <literal>BINARY</> option
 140    specifies that the cursor should return data in binary format.
 141    This reduces conversion effort for both the server and client,
 142    at the cost of more programmer effort to deal with platform-dependent
 143    binary data formats.
 144    As an example, if a query returns a value of one from an integer column,
 145    you would get a string of <literal>1</> with a default cursor,
 146    whereas with a binary cursor you would get
 147    a 4-byte field containing the internal representation of the value
 148    (in big-endian byte order).
 149   </para>
 150
 151   <para>
 152    Binary cursors should be used carefully.  Many applications,
 153    including <application>psql</application>, are not prepared to
 154    handle binary cursors and expect data to come back in the text
 155    format.
 156   </para>
 157
 158   <note>
 159    <para>
 160     When the client application uses the <quote>extended query</> protocol
 161     to issue a <command>FETCH</> command, the Bind protocol message
 162     specifies whether data is to be retrieved in text or binary format.
 163     This choice overrides the way that the cursor is defined.  The concept
 164     of a binary cursor as such is thus obsolete when using extended query
 165     protocol &mdash; any cursor can be treated as either text or binary.
 166    </para>
 167   </note>
 168
 169    <para>
 170     Unless <literal>WITH HOLD</literal> is specified, the cursor
 171     created by this command can only be used within the current
 172     transaction.  Thus, <command>DECLARE</> without <literal>WITH
 173     HOLD</literal> is useless outside a transaction block: the cursor would
 174     survive only to the completion of the statement.  Therefore
 175     <productname>PostgreSQL</productname> reports an error if such a
 176     command is used outside a transaction block.
 177     Use
 178     <xref linkend="sql-begin" endterm="sql-begin-title">,
 179     <xref linkend="sql-commit" endterm="sql-commit-title">
 180     and
 181     <xref linkend="sql-rollback" endterm="sql-rollback-title">
 182     to define a transaction block.
 183    </para>
 184
 185    <para>
 186     If <literal>WITH HOLD</literal> is specified and the transaction
 187     that created the cursor successfully commits, the cursor can
 188     continue to be accessed by subsequent transactions in the same
 189     session.  (But if the creating transaction is aborted, the cursor
 190     is removed.)  A cursor created with <literal>WITH HOLD</literal>
 191     is closed when an explicit <command>CLOSE</command> command is
 192     issued on it, or the session ends.  In the current implementation,
 193     the rows represented by a held cursor are copied into a temporary
 194     file or memory area so that they remain available for subsequent
 195     transactions.
 196    </para>
 197
 198    <para>
 199     <literal>WITH HOLD</literal> may not be specified when the query
 200     includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
 201    </para>
 202
 203    <para>
 204     The <literal>SCROLL</> option should be specified when defining a
 205     cursor that will be used to fetch backwards.  This is required by
 206     the SQL standard.  However, for compatibility with earlier
 207     versions, <productname>PostgreSQL</productname> will allow
 208     backward fetches without <literal>SCROLL</>, if the cursor's query
 209     plan is simple enough that no extra overhead is needed to support
 210     it. However, application developers are advised not to rely on
 211     using backward fetches from a cursor that has not been created
 212     with <literal>SCROLL</literal>.  If <literal>NO SCROLL</> is
 213     specified, then backward fetches are disallowed in any case.
 214    </para>
 215
 216    <para>
 217     If the cursor's query includes <literal>FOR UPDATE</> or <literal>FOR
 218     SHARE</>, then returned rows are locked at the time they are first
 219     fetched, in the same way as for a regular
 220     <xref linkend="sql-select" endterm="sql-select-title"> command with
 221     these options.
 222     In addition, the returned rows will be the most up-to-date versions;
 223     therefore these options provide the equivalent of what the SQL standard
 224     calls a <quote>sensitive cursor</>.  It is often wise to use <literal>FOR
 225     UPDATE</> if the cursor is intended to be used with <command>UPDATE
 226     ... WHERE CURRENT OF</> or <command>DELETE ... WHERE CURRENT OF</>,
 227     since this will prevent other sessions from changing the rows between
 228     the time they are fetched and the time they are updated.  Without
 229     <literal>FOR UPDATE</>, a subsequent <literal>WHERE CURRENT OF</> command
 230     will have no effect if the row was changed meanwhile.
 231    </para>
 232
 233    <para>
 234     <literal>SCROLL</literal> may not be specified when the query
 235     includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
 236    </para>
 237
 238    <para>
 239     The SQL standard only makes provisions for cursors in embedded
 240     <acronym>SQL</acronym>.  The <productname>PostgreSQL</productname>
 241     server does not implement an <command>OPEN</command> statement for
 242     cursors; a cursor is considered to be open when it is declared.
 243     However, <application>ECPG</application>, the embedded SQL
 244     preprocessor for <productname>PostgreSQL</productname>, supports
 245     the standard SQL cursor conventions, including those involving
 246     <command>DECLARE</command> and <command>OPEN</command> statements.
 247    </para>
 248
 249    <para>
 250     You can see all available cursors by querying the <link
 251     linkend="view-pg-cursors"><structname>pg_cursors</structname></link>
 252     system view.
 253    </para>
 254  </refsect1>
 255
 256  <refsect1>
 257   <title>Examples</title>
 258
 259   <para>
 260    To declare a cursor:
 261 <programlisting>
 262 DECLARE liahona CURSOR FOR SELECT * FROM films;
 263 </programlisting>
 264    See <xref linkend="sql-fetch" endterm="sql-fetch-title"> for more
 265    examples of cursor usage.
 266   </para>
 267  </refsect1>
 268
 269  <refsect1>
 270   <title>Compatibility</title>
 271
 272   <para>
 273    The SQL standard says that it is implementation-dependent whether cursors
 274    are sensitive to concurrent updates of the underlying data by default.  In
 275    <productname>PostgreSQL</productname>, cursors are insensitive by default,
 276    and can be made sensitive by specifying <literal>FOR UPDATE</>.  Other
 277    products may work differently.
 278   </para>
 279
 280   <para>
 281    The SQL standard allows cursors only in embedded
 282    <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</>
 283    permits cursors to be used interactively.
 284   </para>
 285
 286   <para>
 287    Binary cursors are a <productname>PostgreSQL</productname>
 288    extension.
 289   </para>
 290  </refsect1>
 291
 292  <refsect1>
 293   <title>See Also</title>
 294
 295   <simplelist type="inline">
 296    <member><xref linkend="sql-close" endterm="sql-close-title"></member>
 297    <member><xref linkend="sql-fetch" endterm="sql-fetch-title"></member>
 298    <member><xref linkend="sql-move" endterm="sql-move-title"></member>
 299   </simplelist>
 300  </refsect1>
 301 </refentry>