fetch.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.20 2002/03/06 06:48:05 momjian 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 table using a cursor
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1999-07-20</date>
  </refsynopsisdivinfo>
  <synopsis>
FETCH [ <replaceable class="PARAMETER">direction</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable> ] { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</replaceable> | ALL | NEXT | PRIOR ]
    { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
  </synopsis>

  <refsect2 id="R2-SQL-FETCH-1">
   <refsect2info>
    <date>1998-09-01</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

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

	<variablelist>
	 <varlistentry>
	  <term>FORWARD</term>
	  <listitem>
	   <para>
	    fetch next row(s). This is the default
	    if <replaceable class="PARAMETER">selector</replaceable> is omitted.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>BACKWARD</term>
	  <listitem>
	   <para>
	    fetch previous row(s).
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>RELATIVE</term>
	  <listitem>
	   <para>
	    Noise word for SQL92 compatibility.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">count</replaceable></term>
      <listitem>
       <para>
	<replaceable class="PARAMETER">count</replaceable>
	determines how many rows to fetch. It can be one of the following:

	<variablelist>
	 <varlistentry>
	  <term><replaceable class="PARAMETER">#</replaceable></term>
	  <listitem>
	   <para>
	    A signed integer that specifies how many rows to fetch.
	    Note that a negative integer is equivalent to changing the sense of
	    FORWARD and BACKWARD.
	   </para>
	  </listitem>
	 </varlistentry>

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

	 <varlistentry>
	  <term>
	   NEXT
	  </term>
	  <listitem>
	   <para>
	    Equivalent to specifying a count of <command>1</command>.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>
	   PRIOR
	  </term>
	  <listitem>
	   <para>
	    Equivalent to specifying a count of <command>-1</command>.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </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>1998-04-15</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>
    <command>FETCH</command> returns the results 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 previously declared.
	The cursor must be declared within a transaction block.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
WARNING:  FETCH/ABSOLUTE not supported, using RELATIVE
       </computeroutput></term>
      <listitem>
       <para>
	<productname>PostgreSQL</productname> does not support absolute
	positioning of cursors.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
ERROR:  FETCH/RELATIVE at current position is not supported
       </computeroutput></term>
      <listitem>
       <para>
	<acronym>SQL92</acronym> allows one to repetitively retrieve the cursor
	at its <quote>current position</quote> using the syntax
	<synopsis>
FETCH RELATIVE 0 FROM <replaceable class="PARAMETER">cursor</replaceable>.
	</synopsis>
       </para>

       <para>
	<productname>PostgreSQL</productname> does not currently support
	this notion; in fact the value zero is reserved to indicate that
	all rows should be retrieved and is equivalent to specifying the ALL keyword.
	If the RELATIVE keyword has been used, <productname>PostgreSQL</productname> 
	assumes that the user intended <acronym>SQL92</acronym> behavior
	and returns this error message.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-FETCH-1">
  <refsect1info>
   <date>1998-04-15</date>
  </refsect1info>
  <title>
   Description
  </title>

  <para>
   <command>FETCH</command> allows a user to retrieve rows using a cursor.
   The number of rows retrieved is specified by
   <replaceable class="PARAMETER">#</replaceable>.
   If the number of rows remaining in the cursor is less
   than <replaceable class="PARAMETER">#</replaceable>,
   then only those available are fetched.
   Substituting the keyword ALL in place of a number will
   cause all remaining rows in the cursor to be retrieved.
   Instances may be fetched in both FORWARD and BACKWARD
   directions. The default direction is FORWARD.

   <tip>
    <para>
     Negative numbers are allowed to be specified for the
     row count. A negative number is equivalent to reversing
     the sense of the FORWARD and BACKWARD keywords. For example,
     <command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>.
    </para>
   </tip>
  </para>

  <refsect2 id="R2-SQL-FETCH-3">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Notes
   </title>

   <para>
    Note that the FORWARD and BACKWARD keywords are
    <productname>PostgreSQL</productname> extensions.
    The <acronym>SQL92</acronym> syntax is also supported, specified
    in the second form of the command. See below for details
    on compatibility issues.
   </para>

   <para>
    Updating data in 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 of transactions because
    the data that they store spans multiple user queries.
   </para>

   <para>
    Use
    <xref linkend="sql-move" endterm="sql-move-title">
    to change cursor position.
    <xref linkend="sql-declare" endterm="sql-declare-title">
    will define a cursor.
    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 examples 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 BACKWARD 1 IN 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>1998-09-01</date>
   </refsect2info>
   <title>
    SQL92
   </title>

   <para>
    <note>
     <para>
      The non-embedded use of cursors is a <productname>PostgreSQL</productname>
      extension. The syntax and usage of cursors is being compared
      against the embedded form of cursors defined in <acronym>SQL92</acronym>.
     </para>
    </note>
   </para>

   <para>
    <acronym>SQL92</acronym> allows absolute positioning of the cursor for
    FETCH, and allows placing the results into explicit variables:

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

    <variablelist>
     <varlistentry>
      <term>ABSOLUTE</term>
      <listitem>
       <para>
	The cursor should be positioned to the specified absolute
	row number. All row numbers in <productname>PostgreSQL</productname>
	are relative numbers so this capability is not supported.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>:<replaceable class="PARAMETER">variable</replaceable></term>
      <listitem>
       <para>
	Target host variable(s).
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </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:
-->