Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/fetch.sgml`
cc6839cfa10137668675ccbf2519dd25764fb429e387868d0000000100000d89
 <command>FETCH FORWARD</command> with
   a positive count.  For simple queries
   <productname>PostgreSQL</productname> will allow backwards fetch
   from cursors not declared with <literal>SCROLL</literal>, but this
   behavior is best not relied on. If the cursor is declared with
   <literal>NO SCROLL</literal>, no backward fetches are allowed.
  </para>

  <para>
   <literal>ABSOLUTE</literal> fetches are not any faster than
   navigating to the desired row with a relative move: the underlying
   implementation must traverse all the intermediate rows anyway.
   Negative absolute fetches are even worse: the query must be read to
   the end to find the last row, and then traversed backward from
   there.  However, rewinding to the start of the query (as with
   <literal>FETCH ABSOLUTE 0</literal>) is fast.
  </para>

  <para>
   <link linkend="sql-declare"><command>DECLARE</command></link>
   is used to define a cursor.  Use
   <link linkend="sql-move"><command>MOVE</command></link>
   to change cursor position without retrieving data.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   The following example traverses a table using a cursor:

<programlisting>
BEGIN WORK;

-- Set up a cursor:
DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;

-- Fetch the first 5 rows in the cursor liahona:
FETCH FORWARD 5 FROM liahona;

 code  |          title          | did | date_prod  |   kind   |  len
-------+-------------------------+-----+------------+----------+-------
 BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44
 BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43
 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
 P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08
 P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28

-- Fetch the previous row:
FETCH PRIOR FROM liahona;

 code  |  title  | did | date_prod  |  kind  |  len
-------+---------+-----+------------+--------+-------
 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08

-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;
</programlisting></para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   The SQL standard defines <command>FETCH</command> for use in
   embedded SQL only.  The variant of <command>FETCH</command>
   described here returns the data as if it were a
   <command>SELECT</command> result rather than placing it in host
   variables.  Other than this point, <command>FETCH</command> is
   fully upward-compatible with the SQL standard.
  </para>

  <para>
   The <command>FETCH</command> forms involving
   <literal>FORWARD</literal> and <literal>BACKWARD</literal>, as well
   as the forms <literal>FETCH <replaceable
   class="parameter">count</replaceable></literal> and <literal>FETCH
   ALL</literal>, in which <literal>FORWARD</literal> is implicit, are
   <productname>PostgreSQL</productname> extensions.
  </para>

  <para>
   The SQL standard allows only <literal>FROM</literal> preceding the cursor
   name; the option to use <literal>IN</literal>, or to leave them out altogether, is
   an extension.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-close"/></member>
   <member><xref linkend="sql-declare"/></member>
   <member><xref linkend="sql-move"/></member>
  </simplelist>
 </refsect1>
</refentry>
Title: FETCH Command Examples, Compatibility with SQL Standard, and Related Commands
Summary
This section provides an example of using the FETCH command to traverse a table with a cursor, including setting up the cursor, fetching rows forward and backward, and closing the cursor. It then discusses the compatibility of the PostgreSQL FETCH command with the SQL standard, noting that it returns data like a SELECT result instead of placing it in host variables. It highlights PostgreSQL extensions like FORWARD and BACKWARD fetch forms and the optional use of FROM/IN or omitting them before the cursor name. Finally, it lists related commands like CLOSE, DECLARE, and MOVE.