path: root/doc/src/sgml/ref/fetch.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.27 2003/03/11 19:40:22 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-FETCH">
 <refmeta>
  <refentrytitle id="SQL-FETCH-TITLE">FETCH</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   FETCH
  </refname>
  <refpurpose>
   retrieve rows from a query using a cursor
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>2003-03-11</date>
  </refsynopsisdivinfo>
  <synopsis>
FETCH [ <replaceable class="PARAMETER">direction</replaceable> { FROM | IN } ] <replaceable class="PARAMETER">cursor</replaceable>

where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:

    NEXT
    PRIOR
    FIRST
    LAST
    ABSOLUTE <replaceable class="PARAMETER">count</replaceable>
    RELATIVE <replaceable class="PARAMETER">count</replaceable>
    <replaceable class="PARAMETER">count</replaceable>
    ALL
    FORWARD
    FORWARD <replaceable class="PARAMETER">count</replaceable>
    FORWARD ALL
    BACKWARD
    BACKWARD <replaceable class="PARAMETER">count</replaceable>
    BACKWARD ALL
  </synopsis>

  <refsect2 id="R2-SQL-FETCH-1">
   <refsect2info>
    <date>2003-03-11</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">direction</replaceable></term>
      <listitem>
       <para>
	<replaceable class="PARAMETER">direction</replaceable>
	defines the fetch direction and number of rows to fetch.
	It can be one of the following:

	<variablelist>
	 <varlistentry>
	  <term>NEXT</term>
	  <listitem>
	   <para>
	    fetch next row. This is the default
	    if <replaceable class="PARAMETER">direction</replaceable> is omitted.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>PRIOR</term>
	  <listitem>
	   <para>
	    fetch prior row.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>FIRST</term>
	  <listitem>
	   <para>
	    fetch first row of query (same as ABSOLUTE 1).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>LAST</term>
	  <listitem>
	   <para>
	    fetch last row of query (same as ABSOLUTE -1).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></term>
	  <listitem>
	   <para>
	    fetch the <replaceable class="PARAMETER">count</replaceable>'th
	    row of query, or the
	    abs(<replaceable class="PARAMETER">count</replaceable>)'th row
	    from the end if
	    <replaceable class="PARAMETER">count</replaceable> &lt; 0.
	    Position before first row or after last row
	    if <replaceable class="PARAMETER">count</replaceable> is out of
	    range; in particular, ABSOLUTE 0 positions before first row.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>RELATIVE <replaceable class="PARAMETER">count</replaceable></term>
	  <listitem>
	   <para>
	    fetch the <replaceable class="PARAMETER">count</replaceable>'th
	    succeeding row, or the
	    abs(<replaceable class="PARAMETER">count</replaceable>)'th prior
	    row if <replaceable class="PARAMETER">count</replaceable> &lt; 0.
	    RELATIVE 0 re-fetches current row, if any.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term><replaceable class="PARAMETER">count</replaceable></term>
	  <listitem>
	   <para>
	    fetch the next <replaceable class="PARAMETER">count</replaceable>
	    rows (same as FORWARD <replaceable class="PARAMETER">count</replaceable>).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>ALL</term>
	  <listitem>
	   <para>
	    fetch all remaining rows (same as FORWARD ALL).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>FORWARD</term>
	  <listitem>
	   <para>
	    fetch next row (same as NEXT).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>FORWARD <replaceable class="PARAMETER">count</replaceable></term>
	  <listitem>
	   <para>
	    fetch next <replaceable class="PARAMETER">count</replaceable>
	    rows.  FORWARD 0 re-fetches current row.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>FORWARD ALL</term>
	  <listitem>
	   <para>
	    fetch all remaining rows.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>BACKWARD</term>
	  <listitem>
	   <para>
	    fetch prior row (same as PRIOR).
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>BACKWARD <replaceable class="PARAMETER">count</replaceable></term>
	  <listitem>
	   <para>
	    fetch prior <replaceable class="PARAMETER">count</replaceable>
	    rows (scanning backwards).  BACKWARD 0 re-fetches current row.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>BACKWARD ALL</term>
	  <listitem>
	   <para>
	    fetch all prior rows (scanning backwards).
	   </para>
	  </listitem>
	 </varlistentry>

	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">count</replaceable></term>
      <listitem>
       <para>
	<replaceable class="PARAMETER">count</replaceable>
	is a possibly-signed integer constant, determining the location
	or number of rows to fetch.  For FORWARD and BACKWARD cases,
	specifying a negative <replaceable
	class="PARAMETER">count</replaceable>
	is equivalent to changing the sense of FORWARD and BACKWARD.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">cursor</replaceable></term>
      <listitem>
       <para>
	An open cursor's name.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-FETCH-2">
   <refsect2info>
    <date>2003-03-11</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>
    <command>FETCH</command> returns rows from the result of the query defined
    by the specified cursor.
    The following messages will be returned if the query fails:

    <variablelist>
     <varlistentry>
      <term><computeroutput>
WARNING:  PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</replaceable>" not found
       </computeroutput></term>
      <listitem>
       <para>
	If <replaceable class="PARAMETER">cursor</replaceable> is not known.
	The cursor must have been declared within the current transaction block.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-FETCH-1">
  <refsect1info>
   <date>2003-03-11</date>
  </refsect1info>
  <title>
   Description
  </title>

  <para>
   <command>FETCH</command> retrieves rows using a cursor.
  </para>

  <para>
   A cursor has an associated <firstterm>position</> that is used by
   <command>FETCH</>.  The cursor position can be before the first row of the
   query result, or on any particular row of the result, or after the last row
   of the result.  When created, a cursor is positioned before the first row.
   After fetching some rows, the cursor is positioned on the row most recently
   retrieved.  If <command>FETCH</> runs off the end of the available rows
   then the cursor is left positioned after the last row, or before the first
   row if fetching backward.  <command>FETCH ALL</> or <command>FETCH BACKWARD
   ALL</> will always leave the cursor positioned after the last row or before
   the first row.
  </para>

  <para>
   The SQL-compatible forms (NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE)
   fetch a single row after moving the cursor appropriately.  If there is
   no such row, an empty result is returned, and the cursor is left positioned
   before the first row or after the last row as appropriate.
  </para>

  <para>
   The forms using FORWARD and BACKWARD are not in the SQL standard, but
   are <productname>PostgreSQL</productname> extensions.  These forms
   retrieve the indicated number of rows moving in the forward or backward
   direction, leaving the cursor positioned on the last-returned row
   (or after/before all rows, if the <replaceable
   class="PARAMETER">count</replaceable> exceeds the number of rows
   available).
  </para>

   <tip>
    <para>
     RELATIVE 0, FORWARD 0, and BACKWARD 0 all request
     fetching the current row without moving the
     cursor --- that is, re-fetching the most recently fetched row.
     This will succeed unless the cursor is positioned before the
     first row or after the last row; in which case, no row is returned.
    </para>
   </tip>

  <refsect2 id="R2-SQL-FETCH-3">
   <refsect2info>
    <date>2003-03-11</date>
   </refsect2info>
   <title>
    Notes
   </title>

   <para>
    The cursor should be declared with the SCROLL option if one intends to
    use any variants of <command>FETCH</> other than <command>FETCH NEXT</>
    or <command>FETCH FORWARD</> with a positive count.  For simple queries
    <productname>PostgreSQL</productname> will allow backwards fetch from
    cursors not declared with SCROLL, but this behavior is best not relied on.
   </para>

   <para>
    ABSOLUTE 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 FETCH ABSOLUTE 0) is fast.
   </para>

   <para>
    Updating data via a cursor is not supported by 
    <productname>PostgreSQL</productname>,
    because mapping cursor updates back to base tables is
    not generally possible, as is also the case with VIEW updates.
    Consequently,
    users must issue explicit UPDATE commands to replace data.
   </para>

   <para>
    Cursors may only be used inside transaction blocks.
   </para>

   <para>
    <xref linkend="sql-declare" endterm="sql-declare-title">
    is used to define a cursor.
    Use
    <xref linkend="sql-move" endterm="sql-move-title">
    to change cursor position without retrieving data.
    Refer to
    <xref linkend="sql-begin" endterm="sql-begin-title">,
    <xref linkend="sql-commit" endterm="sql-commit-title">,
    and
    <xref linkend="sql-rollback" endterm="sql-rollback-title">
    for further information about transactions.
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-FETCH-2">
  <title>
   Usage
  </title>

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

<programlisting>
-- Set up and use a cursor:

BEGIN WORK;
DECLARE liahona CURSOR FOR SELECT * FROM films;

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

<computeroutput>
 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
</computeroutput>

-- Fetch previous row:
FETCH PRIOR FROM liahona;

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

-- close the cursor and commit work:

CLOSE liahona;
COMMIT WORK;
</programlisting>
  </para>        
 </refsect1>

 <refsect1 id="R1-SQL-FETCH-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-FETCH-4">
   <refsect2info>
    <date>2003-03-11</date>
   </refsect2info>
   <title>
    SQL92
   </title>

   <para>
    <acronym>SQL92</acronym> defines FETCH for use in embedded contexts only.
    Therefore, it describes placing the results into explicit variables using
    an <literal>INTO</> clause, for example:

    <synopsis>
FETCH ABSOLUTE <replaceable class="PARAMETER">n</replaceable>
    FROM <replaceable class="PARAMETER">cursor</replaceable>
    INTO :<replaceable class="PARAMETER">variable</replaceable> [, ...]
    </synopsis>

    <productname>PostgreSQL</productname>'s use of non-embedded cursors
    is non-standard, and so is its practice of returning the result data
    as if it were a SELECT result.  Other than this point, FETCH is fully
    upward-compatible with <acronym>SQL92</acronym>.
   </para>

   <para>
    The FETCH forms involving FORWARD and BACKWARD (including the forms
    FETCH <replaceable class="PARAMETER">count</replaceable> and FETCH ALL,
    in which FORWARD is implicit) are <productname>PostgreSQL</productname>
    extensions.
   </para>

   <para>
    <acronym>SQL92</acronym> allows only <literal>FROM</> preceding the
    cursor name; the option to use <literal>IN</> is an extension.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->