diff options
Diffstat (limited to 'doc/src/sgml/ref/fetch.sgml')
| -rw-r--r-- | doc/src/sgml/ref/fetch.sgml | 418 |
1 files changed, 418 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/fetch.sgml b/doc/src/sgml/ref/fetch.sgml new file mode 100644 index 0000000..f0f3ac2 --- /dev/null +++ b/doc/src/sgml/ref/fetch.sgml @@ -0,0 +1,418 @@ +<!-- +doc/src/sgml/ref/fetch.sgml +PostgreSQL documentation +--> + +<refentry id="sql-fetch"> + + <indexterm zone="sql-fetch"> + <primary>FETCH</primary> + </indexterm> + + <indexterm zone="sql-fetch"> + <primary>cursor</primary> + <secondary>FETCH</secondary> + </indexterm> + <refmeta> + <refentrytitle>FETCH</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>FETCH</refname> + <refpurpose>retrieve rows from a query using a cursor</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<!-- Note the "direction" bit is also in ref/move.sgml --> +<synopsis> +FETCH [ <replaceable class="parameter">direction</replaceable> ] [ FROM | IN ] <replaceable class="parameter">cursor_name</replaceable> + +<phrase>where <replaceable class="parameter">direction</replaceable> can be one of:</phrase> + + 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> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>FETCH</command> retrieves rows using a previously-created cursor. + </para> + + <para> + A cursor has an associated position, which is used by + <command>FETCH</command>. The cursor position can be before the first row of the + query result, 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</command> 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</command> or <command>FETCH BACKWARD + ALL</command> will always leave the cursor positioned after the last row or before + the first row. + </para> + + <para> + The forms <literal>NEXT</literal>, <literal>PRIOR</literal>, <literal>FIRST</literal>, + <literal>LAST</literal>, <literal>ABSOLUTE</literal>, <literal>RELATIVE</literal> 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 <literal>FORWARD</literal> and <literal>BACKWARD</literal> + 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> + + <para> + <literal>RELATIVE 0</literal>, <literal>FORWARD 0</literal>, and + <literal>BACKWARD 0</literal> 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> + + <note> + <para> + This page describes usage of cursors at the SQL command level. + If you are trying to use cursors inside a <application>PL/pgSQL</application> + function, the rules are different — + see <xref linkend="plpgsql-cursor-using"/>. + </para> + </note> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <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><literal>NEXT</literal></term> + <listitem> + <para> + Fetch the next row. This is the default if <replaceable + class="parameter">direction</replaceable> is omitted. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>PRIOR</literal></term> + <listitem> + <para> + Fetch the prior row. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FIRST</literal></term> + <listitem> + <para> + Fetch the first row of the query (same as <literal>ABSOLUTE 1</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>LAST</literal></term> + <listitem> + <para> + Fetch the last row of the query (same as <literal>ABSOLUTE -1</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ABSOLUTE <replaceable class="parameter">count</replaceable></literal></term> + <listitem> + <para> + Fetch the <replaceable + class="parameter">count</replaceable>'th row of the query, + or the <literal>abs(<replaceable + class="parameter">count</replaceable>)</literal>'th row from + the end if <replaceable + class="parameter">count</replaceable> is negative. Position + before first row or after last row if <replaceable + class="parameter">count</replaceable> is out of range; in + particular, <literal>ABSOLUTE 0</literal> positions before + the first row. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>RELATIVE <replaceable class="parameter">count</replaceable></literal></term> + <listitem> + <para> + Fetch the <replaceable + class="parameter">count</replaceable>'th succeeding row, or + the <literal>abs(<replaceable + class="parameter">count</replaceable>)</literal>'th prior + row if <replaceable class="parameter">count</replaceable> is + negative. <literal>RELATIVE 0</literal> re-fetches the + 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 + <literal>FORWARD <replaceable + class="parameter">count</replaceable></literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ALL</literal></term> + <listitem> + <para> + Fetch all remaining rows (same as <literal>FORWARD ALL</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FORWARD</literal></term> + <listitem> + <para> + Fetch the next row (same as <literal>NEXT</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FORWARD <replaceable class="parameter">count</replaceable></literal></term> + <listitem> + <para> + Fetch the next <replaceable + class="parameter">count</replaceable> rows. + <literal>FORWARD 0</literal> re-fetches the current row. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FORWARD ALL</literal></term> + <listitem> + <para> + Fetch all remaining rows. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>BACKWARD</literal></term> + <listitem> + <para> + Fetch the prior row (same as <literal>PRIOR</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>BACKWARD <replaceable class="parameter">count</replaceable></literal></term> + <listitem> + <para> + Fetch the prior <replaceable + class="parameter">count</replaceable> rows (scanning + backwards). <literal>BACKWARD 0</literal> re-fetches the + current row. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>BACKWARD ALL</literal></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 <literal>FORWARD</literal> and + <literal>BACKWARD</literal> cases, specifying a negative <replaceable + class="parameter">count</replaceable> is equivalent to changing + the sense of <literal>FORWARD</literal> and <literal>BACKWARD</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">cursor_name</replaceable></term> + <listitem> + <para> + An open cursor's name. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Outputs</title> + + <para> + On successful completion, a <command>FETCH</command> command returns a command + tag of the form +<screen> +FETCH <replaceable class="parameter">count</replaceable> +</screen> + The <replaceable class="parameter">count</replaceable> is the number + of rows fetched (possibly zero). Note that in + <application>psql</application>, the command tag will not actually be + displayed, since <application>psql</application> displays the fetched + rows instead. + </para> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + The cursor should be declared with the <literal>SCROLL</literal> + option if one intends to use any variants of <command>FETCH</command> + other than <command>FETCH NEXT</command> or <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> |