diff options
Diffstat (limited to 'doc/src/sgml/ref/fetch.sgml')
| -rw-r--r-- | doc/src/sgml/ref/fetch.sgml | 330 |
1 files changed, 198 insertions, 132 deletions
diff --git a/doc/src/sgml/ref/fetch.sgml b/doc/src/sgml/ref/fetch.sgml index 0452cf0144f..8f3244eb39f 100644 --- a/doc/src/sgml/ref/fetch.sgml +++ b/doc/src/sgml/ref/fetch.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.26 2003/03/10 03:53:49 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.27 2003/03/11 19:40:22 tgl Exp $ PostgreSQL documentation --> @@ -18,17 +18,32 @@ PostgreSQL documentation </refnamediv> <refsynopsisdiv> <refsynopsisdivinfo> - <date>1999-07-20</date> + <date>2003-03-11</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> +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>1998-09-01</date> + <date>2003-03-11</date> </refsect2info> <title> Inputs @@ -41,97 +56,171 @@ FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</repl <listitem> <para> <replaceable class="PARAMETER">direction</replaceable> - defines the fetch direction. It can be one of - the following: + defines the fetch direction and number of rows to fetch. + It can be one of the following: <variablelist> <varlistentry> - <term>FORWARD</term> + <term>NEXT</term> <listitem> <para> - fetch next row(s). This is the default + fetch next row. This is the default if <replaceable class="PARAMETER">direction</replaceable> is omitted. </para> </listitem> </varlistentry> + <varlistentry> - <term>BACKWARD</term> + <term>PRIOR</term> <listitem> <para> - fetch previous row(s). + fetch prior row. </para> </listitem> </varlistentry> + <varlistentry> - <term>RELATIVE</term> + <term>FIRST</term> <listitem> <para> - Same as FORWARD; provided for SQL92 compatibility. + fetch first row of query (same as ABSOLUTE 1). </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: + <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> < 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> < 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> - <variablelist> <varlistentry> - <term><replaceable class="PARAMETER">#</replaceable></term> + <term>FORWARD ALL</term> <listitem> <para> - A signed integer constant that specifies how many rows to fetch. - Note that a negative integer is equivalent to changing the sense of - FORWARD and BACKWARD. Zero re-fetches the current row, if any. + fetch all remaining rows. </para> </listitem> </varlistentry> <varlistentry> - <term> - ALL - </term> + <term>BACKWARD</term> <listitem> <para> - Retrieve all remaining rows. + fetch prior row (same as PRIOR). </para> </listitem> </varlistentry> <varlistentry> - <term> - NEXT - </term> + <term>BACKWARD <replaceable class="PARAMETER">count</replaceable></term> <listitem> <para> - Equivalent to specifying a count of <command>1</command>. + fetch prior <replaceable class="PARAMETER">count</replaceable> + rows (scanning backwards). BACKWARD 0 re-fetches current row. </para> </listitem> </varlistentry> <varlistentry> - <term> - PRIOR - </term> + <term>BACKWARD ALL</term> <listitem> <para> - Equivalent to specifying a count of <command>-1</command>. + 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> @@ -145,7 +234,7 @@ FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</repl <refsect2 id="R2-SQL-FETCH-2"> <refsect2info> - <date>1998-04-15</date> + <date>2003-03-11</date> </refsect2info> <title> Outputs @@ -162,25 +251,11 @@ WARNING: PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</rep </computeroutput></term> <listitem> <para> - If <replaceable class="PARAMETER">cursor</replaceable> - is not previously declared. - The cursor must be declared within a transaction block. + If <replaceable class="PARAMETER">cursor</replaceable> is not known. + The cursor must have been declared within the current 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> - </variablelist> </para> </refsect2> @@ -188,75 +263,79 @@ WARNING: FETCH/ABSOLUTE not supported, using RELATIVE <refsect1 id="R1-SQL-FETCH-1"> <refsect1info> - <date>1998-04-15</date> + <date>2003-03-11</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. - Rows may be fetched in both FORWARD and BACKWARD - directions. The default direction is FORWARD. + <command>FETCH</command> retrieves rows using a cursor. </para> <para> - 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 last row retrieved. A new - <command>FETCH</command> always steps one row in the specified direction - (if possible) before beginning to return rows. If the - <command>FETCH</command> requests more rows than available, the cursor is - left positioned after the last row of the query result (or before the first - row, in the case of a backward fetch). This will always be the case after - <command>FETCH ALL</>. + 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> - A zero row count requests fetching the current row without moving the + 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> - <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> - <refsect2 id="R2-SQL-FETCH-3"> <refsect2info> - <date>1998-04-15</date> + <date>2003-03-11</date> </refsect2info> <title> Notes </title> <para> - A cursor to be used in backwards fetching should be declared with the - SCROLL option. In simple cases, <productname>PostgreSQL</productname> - will allow backwards fetch from cursors not declared with SCROLL, but - this behavior is best not relied on. + 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> - The FORWARD, BACKWARD, and ALL keywords are - <productname>PostgreSQL</productname> extensions. - See below for details on compatibility issues. + 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> @@ -316,7 +395,7 @@ FETCH FORWARD 5 IN liahona; </computeroutput> -- Fetch previous row: -FETCH BACKWARD 1 IN liahona; +FETCH PRIOR FROM liahona; <computeroutput> code | title | did | date_prod | kind | len @@ -339,52 +418,39 @@ COMMIT WORK; <refsect2 id="R2-SQL-FETCH-4"> <refsect2info> - <date>1998-09-01</date> + <date>2003-03-11</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: + <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">#</replaceable> +FETCH ABSOLUTE <replaceable class="PARAMETER">n</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> + <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> |