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