<!--
$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:
-->