doc/src/sgml/ref/fetch.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-FETCH">
   7  <refmeta>
   8   <refentrytitle id="SQL-FETCH-TITLE">FETCH</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>FETCH</refname>
  15   <refpurpose>retrieve rows from a query using a cursor</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-fetch">
  19   <primary>FETCH</primary>
  20  </indexterm>
  21
  22  <indexterm zone="sql-fetch">
  23   <primary>cursor</primary>
  24   <secondary>FETCH</secondary>
  25  </indexterm>
  26
  27  <refsynopsisdiv>
  28 <synopsis>
  29 FETCH [ <replaceable class="PARAMETER">direction</replaceable> { FROM | IN } ] <replaceable class="PARAMETER">cursorname</replaceable>
  30
  31 where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:
  32
  33     NEXT
  34     PRIOR
  35     FIRST
  36     LAST
  37     ABSOLUTE <replaceable class="PARAMETER">count</replaceable>
  38     RELATIVE <replaceable class="PARAMETER">count</replaceable>
  39     <replaceable class="PARAMETER">count</replaceable>
  40     ALL
  41     FORWARD
  42     FORWARD <replaceable class="PARAMETER">count</replaceable>
  43     FORWARD ALL
  44     BACKWARD
  45     BACKWARD <replaceable class="PARAMETER">count</replaceable>
  46     BACKWARD ALL
  47 </synopsis>
  48  </refsynopsisdiv>
  49
  50  <refsect1>
  51   <title>Description</title>
  52
  53   <para>
  54    <command>FETCH</command> retrieves rows using a previously-created cursor.
  55   </para>
  56
  57   <para>
  58    A cursor has an associated position, which is used by
  59    <command>FETCH</>.  The cursor position can be before the first row of the
  60    query result, on any particular row of the result, or after the last row
  61    of the result.  When created, a cursor is positioned before the first row.
  62    After fetching some rows, the cursor is positioned on the row most recently
  63    retrieved.  If <command>FETCH</> runs off the end of the available rows
  64    then the cursor is left positioned after the last row, or before the first
  65    row if fetching backward.  <command>FETCH ALL</> or <command>FETCH BACKWARD
  66    ALL</> will always leave the cursor positioned after the last row or before
  67    the first row.
  68   </para>
  69
  70   <para>
  71    The forms <literal>NEXT</>, <literal>PRIOR</>, <literal>FIRST</>,
  72    <literal>LAST</>, <literal>ABSOLUTE</>, <literal>RELATIVE</> fetch
  73    a single row after moving the cursor appropriately.  If there is no
  74    such row, an empty result is returned, and the cursor is left
  75    positioned before the first row or after the last row as
  76    appropriate.
  77   </para>
  78
  79   <para>
  80    The forms using <literal>FORWARD</> and <literal>BACKWARD</>
  81    retrieve the indicated number of rows moving in the forward or
  82    backward direction, leaving the cursor positioned on the
  83    last-returned row (or after/before all rows, if the <replaceable
  84    class="PARAMETER">count</replaceable> exceeds the number of rows
  85    available).
  86   </para>
  87
  88   <para>
  89    <literal>RELATIVE 0</>, <literal>FORWARD 0</>, and
  90    <literal>BACKWARD 0</> all request fetching the current row without
  91    moving the cursor, that is, re-fetching the most recently fetched
  92    row.  This will succeed unless the cursor is positioned before the
  93    first row or after the last row; in which case, no row is returned.
  94   </para>
  95
  96   <note>
  97    <para>
  98     This page describes usage of cursors at the SQL command level.
  99     If you are trying to use cursors inside a <application>PL/pgSQL</>
 100     function, the rules are different &mdash;
 101     see <xref linkend="plpgsql-cursors">.
 102    </para>
 103   </note>
 104  </refsect1>
 105
 106  <refsect1>
 107   <title>Parameters</title>
 108
 109   <variablelist>
 110    <varlistentry>
 111     <term><replaceable class="PARAMETER">direction</replaceable></term>
 112     <listitem>
 113      <para>
 114       <replaceable class="PARAMETER">direction</replaceable> defines
 115       the fetch direction and number of rows to fetch.  It can be one
 116       of the following:
 117
 118       <variablelist>
 119        <varlistentry>
 120         <term><literal>NEXT</literal></term>
 121         <listitem>
 122          <para>
 123           Fetch the next row. This is the default if <replaceable
 124           class="PARAMETER">direction</replaceable> is omitted.
 125          </para>
 126         </listitem>
 127        </varlistentry>
 128
 129        <varlistentry>
 130         <term><literal>PRIOR</literal></term>
 131         <listitem>
 132          <para>
 133           Fetch the prior row.
 134          </para>
 135         </listitem>
 136        </varlistentry>
 137
 138        <varlistentry>
 139         <term><literal>FIRST</literal></term>
 140         <listitem>
 141          <para>
 142           Fetch the first row of the query (same as <literal>ABSOLUTE 1</literal>).
 143          </para>
 144         </listitem>
 145        </varlistentry>
 146
 147        <varlistentry>
 148         <term><literal>LAST</literal></term>
 149         <listitem>
 150          <para>
 151           Fetch the last row of the query (same as <literal>ABSOLUTE -1</literal>).
 152          </para>
 153         </listitem>
 154        </varlistentry>
 155
 156        <varlistentry>
 157         <term><literal>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></literal></term>
 158         <listitem>
 159          <para>
 160           Fetch the <replaceable
 161           class="PARAMETER">count</replaceable>'th row of the query,
 162           or the <literal>abs(<replaceable
 163           class="PARAMETER">count</replaceable>)</literal>'th row from
 164           the end if <replaceable
 165           class="PARAMETER">count</replaceable> is negative.  Position
 166           before first row or after last row if <replaceable
 167           class="PARAMETER">count</replaceable> is out of range; in
 168           particular, <literal>ABSOLUTE 0</literal> positions before
 169           the first row.
 170          </para>
 171         </listitem>
 172        </varlistentry>
 173
 174        <varlistentry>
 175         <term><literal>RELATIVE <replaceable class="PARAMETER">count</replaceable></literal></term>
 176         <listitem>
 177          <para>
 178           Fetch the <replaceable
 179           class="PARAMETER">count</replaceable>'th succeeding row, or
 180           the <literal>abs(<replaceable
 181           class="PARAMETER">count</replaceable>)</literal>'th prior
 182           row if <replaceable class="PARAMETER">count</replaceable> is
 183           negative.  <literal>RELATIVE 0</literal> re-fetches the
 184           current row, if any.
 185          </para>
 186         </listitem>
 187        </varlistentry>
 188
 189        <varlistentry>
 190         <term><replaceable class="PARAMETER">count</replaceable></term>
 191         <listitem>
 192          <para>
 193           Fetch the next <replaceable
 194           class="PARAMETER">count</replaceable> rows (same as
 195           <literal>FORWARD <replaceable
 196           class="PARAMETER">count</replaceable></literal>).
 197          </para>
 198         </listitem>
 199        </varlistentry>
 200
 201        <varlistentry>
 202         <term><literal>ALL</literal></term>
 203         <listitem>
 204          <para>
 205           Fetch all remaining rows (same as <literal>FORWARD ALL</literal>).
 206          </para>
 207         </listitem>
 208        </varlistentry>
 209
 210        <varlistentry>
 211         <term><literal>FORWARD</literal></term>
 212         <listitem>
 213          <para>
 214           Fetch the next row (same as <literal>NEXT</literal>).
 215          </para>
 216         </listitem>
 217        </varlistentry>
 218
 219        <varlistentry>
 220         <term><literal>FORWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
 221         <listitem>
 222          <para>
 223           Fetch the next <replaceable
 224           class="PARAMETER">count</replaceable> rows.
 225           <literal>FORWARD 0</literal> re-fetches the current row.
 226          </para>
 227         </listitem>
 228        </varlistentry>
 229
 230        <varlistentry>
 231         <term><literal>FORWARD ALL</literal></term>
 232         <listitem>
 233          <para>
 234           Fetch all remaining rows.
 235          </para>
 236         </listitem>
 237        </varlistentry>
 238
 239        <varlistentry>
 240         <term><literal>BACKWARD</literal></term>
 241         <listitem>
 242          <para>
 243           Fetch the prior row (same as <literal>PRIOR</literal>).
 244          </para>
 245         </listitem>
 246        </varlistentry>
 247
 248        <varlistentry>
 249         <term><literal>BACKWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
 250         <listitem>
 251          <para>
 252           Fetch the prior <replaceable
 253           class="PARAMETER">count</replaceable> rows (scanning
 254           backwards).  <literal>BACKWARD 0</literal> re-fetches the
 255           current row.
 256          </para>
 257         </listitem>
 258        </varlistentry>
 259
 260        <varlistentry>
 261         <term><literal>BACKWARD ALL</literal></term>
 262         <listitem>
 263          <para>
 264           Fetch all prior rows (scanning backwards).
 265          </para>
 266         </listitem>
 267        </varlistentry>
 268       </variablelist>
 269      </para>
 270     </listitem>
 271    </varlistentry>
 272
 273    <varlistentry>
 274     <term><replaceable class="PARAMETER">count</replaceable></term>
 275     <listitem>
 276      <para>
 277       <replaceable class="PARAMETER">count</replaceable> is a
 278       possibly-signed integer constant, determining the location or
 279       number of rows to fetch.  For <literal>FORWARD</> and
 280       <literal>BACKWARD</> cases, specifying a negative <replaceable
 281       class="PARAMETER">count</replaceable> is equivalent to changing
 282       the sense of <literal>FORWARD</> and <literal>BACKWARD</>.
 283      </para>
 284     </listitem>
 285    </varlistentry>
 286
 287    <varlistentry>
 288     <term><replaceable class="PARAMETER">cursorname</replaceable></term>
 289     <listitem>
 290      <para>
 291       An open cursor's name.
 292      </para>
 293     </listitem>
 294    </varlistentry>
 295   </variablelist>
 296  </refsect1>
 297
 298  <refsect1>
 299   <title>Outputs</title>
 300
 301   <para>
 302    On successful completion, a <command>FETCH</> command returns a command
 303    tag of the form
 304 <screen>
 305 FETCH <replaceable class="parameter">count</replaceable>
 306 </screen>
 307    The <replaceable class="parameter">count</replaceable> is the number
 308    of rows fetched (possibly zero).  Note that in
 309    <application>psql</application>, the command tag will not actually be
 310    displayed, since <application>psql</application> displays the fetched
 311    rows instead.
 312   </para>
 313  </refsect1>
 314
 315  <refsect1>
 316   <title>Notes</title>
 317
 318   <para>
 319    The cursor should be declared with the <literal>SCROLL</literal>
 320    option if one intends to use any variants of <command>FETCH</>
 321    other than <command>FETCH NEXT</> or <command>FETCH FORWARD</> with
 322    a positive count.  For simple queries
 323    <productname>PostgreSQL</productname> will allow backwards fetch
 324    from cursors not declared with <literal>SCROLL</literal>, but this
 325    behavior is best not relied on. If the cursor is declared with
 326    <literal>NO SCROLL</literal>, no backward fetches are allowed.
 327   </para>
 328
 329   <para>
 330    <literal>ABSOLUTE</literal> fetches are not any faster than
 331    navigating to the desired row with a relative move: the underlying
 332    implementation must traverse all the intermediate rows anyway.
 333    Negative absolute fetches are even worse: the query must be read to
 334    the end to find the last row, and then traversed backward from
 335    there.  However, rewinding to the start of the query (as with
 336    <literal>FETCH ABSOLUTE 0</literal>) is fast.
 337   </para>
 338
 339   <para>
 340    <xref linkend="sql-declare" endterm="sql-declare-title">
 341    is used to define a cursor.  Use
 342    <xref linkend="sql-move" endterm="sql-move-title">
 343    to change cursor position without retrieving data.
 344   </para>
 345  </refsect1>
 346
 347  <refsect1>
 348   <title>Examples</title>
 349
 350   <para>
 351    The following example traverses a table using a cursor:
 352
 353 <programlisting>
 354 BEGIN WORK;
 355
 356 -- Set up a cursor:
 357 DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
 358
 359 -- Fetch the first 5 rows in the cursor liahona:
 360 FETCH FORWARD 5 FROM liahona;
 361
 362  code  |          title          | did | date_prod  |   kind   |  len
 363 -------+-------------------------+-----+------------+----------+-------
 364  BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44
 365  BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43
 366  JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
 367  P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08
 368  P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28
 369
 370 -- Fetch the previous row:
 371 FETCH PRIOR FROM liahona;
 372
 373  code  |  title  | did | date_prod  |  kind  |  len
 374 -------+---------+-----+------------+--------+-------
 375  P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
 376
 377 -- Close the cursor and end the transaction:
 378 CLOSE liahona;
 379 COMMIT WORK;
 380 </programlisting>
 381   </para>
 382  </refsect1>
 383
 384  <refsect1>
 385   <title>Compatibility</title>
 386
 387   <para>
 388    The SQL standard defines <command>FETCH</command> for use in
 389    embedded SQL only.  The variant of <command>FETCH</command>
 390    described here returns the data as if it were a
 391    <command>SELECT</command> result rather than placing it in host
 392    variables.  Other than this point, <command>FETCH</command> is
 393    fully upward-compatible with the SQL standard.
 394   </para>
 395
 396   <para>
 397    The <command>FETCH</command> forms involving
 398    <literal>FORWARD</literal> and <literal>BACKWARD</literal>, as well
 399    as the forms <literal>FETCH <replaceable
 400    class="PARAMETER">count</replaceable></literal> and <literal>FETCH
 401    ALL</literal>, in which <literal>FORWARD</literal> is implicit, are
 402    <productname>PostgreSQL</productname> extensions.
 403   </para>
 404
 405   <para>
 406    The SQL standard allows only <literal>FROM</> preceding the cursor
 407    name; the option to use <literal>IN</> is an extension.
 408   </para>
 409  </refsect1>
 410
 411  <refsect1>
 412   <title>See Also</title>
 413
 414   <simplelist type="inline">
 415    <member><xref linkend="sql-close" endterm="sql-close-title"></member>
 416    <member><xref linkend="sql-declare" endterm="sql-declare-title"></member>
 417    <member><xref linkend="sql-move" endterm="sql-move-title"></member>
 418   </simplelist>
 419  </refsect1>
 420 </refentry>