From 6261c75014c9948837d9d025493ef18b8f833f70 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 11 Mar 2003 19:40:24 +0000 Subject: Implement SQL92-compatible FIRST, LAST, ABSOLUTE n, RELATIVE n options for FETCH and MOVE. --- doc/src/sgml/ref/fetch.sgml | 330 ++++++++++++++++++++++++++------------------ doc/src/sgml/ref/move.sgml | 16 +-- 2 files changed, 204 insertions(+), 142 deletions(-) (limited to 'doc/src') 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 @@ @@ -18,17 +18,32 @@ PostgreSQL documentation - 1999-07-20 + 2003-03-11 -FETCH [ direction ] [ count ] { IN | FROM } cursor -FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ] - { IN | FROM } cursor +FETCH [ direction { FROM | IN } ] cursor + +where direction can be empty or one of: + + NEXT + PRIOR + FIRST + LAST + ABSOLUTE count + RELATIVE count + count + ALL + FORWARD + FORWARD count + FORWARD ALL + BACKWARD + BACKWARD count + BACKWARD ALL - 1998-09-01 + 2003-03-11 Inputs @@ -41,96 +56,170 @@ 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> @@ -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 - FETCH allows a user to retrieve rows using a cursor. - The number of rows retrieved is specified by - #. - If the number of rows remaining in the cursor is less - than #, - 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. + FETCH retrieves rows using a cursor. - 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 - FETCH always steps one row in the specified direction - (if possible) before beginning to return rows. If the - FETCH 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 - FETCH ALL. + A cursor has an associated position that is used by + 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 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. FETCH ALL or FETCH BACKWARD + ALL will always leave the cursor positioned after the last row or before + the first row. + + + + 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. + + + + The forms using FORWARD and BACKWARD are not in the SQL standard, but + are PostgreSQL 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 count exceeds the number of rows + available). - 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. - - - 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, - FORWARD -1 is the same as BACKWARD 1. - - - - 1998-04-15 + 2003-03-11 Notes - A cursor to be used in backwards fetching should be declared with the - SCROLL option. In simple cases, PostgreSQL - 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 FETCH other than FETCH NEXT + or FETCH FORWARD with a positive count. For simple queries + PostgreSQL will allow backwards fetch from + cursors not declared with SCROLL, but this behavior is best not relied on. - The FORWARD, BACKWARD, and ALL keywords are - PostgreSQL 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. @@ -316,7 +395,7 @@ FETCH FORWARD 5 IN liahona; -- Fetch previous row: -FETCH BACKWARD 1 IN liahona; +FETCH PRIOR FROM liahona; code | title | did | date_prod | kind | len @@ -339,52 +418,39 @@ COMMIT WORK; - 1998-09-01 + 2003-03-11 SQL92 - - - The non-embedded use of cursors is a PostgreSQL - extension. The syntax and usage of cursors is being compared - against the embedded form of cursors defined in SQL92. - - - - - - SQL92 allows absolute positioning of the cursor for - FETCH, and allows placing the results into explicit variables: + SQL92 defines FETCH for use in embedded contexts only. + Therefore, it describes placing the results into explicit variables using + an INTO clause, for example: -FETCH ABSOLUTE # +FETCH ABSOLUTE n FROM cursor INTO :variable [, ...] - - - ABSOLUTE - - - The cursor should be positioned to the specified absolute - row number. All row numbers in PostgreSQL - are relative numbers so this capability is not supported. - - - - - :variable - - - Target host variable(s). - - - - + PostgreSQL'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 SQL92. + + + + The FETCH forms involving FORWARD and BACKWARD (including the forms + FETCH count and FETCH ALL, + in which FORWARD is implicit) are PostgreSQL + extensions. + + + + SQL92 allows only FROM preceding the + cursor name; the option to use IN is an extension. diff --git a/doc/src/sgml/ref/move.sgml b/doc/src/sgml/ref/move.sgml index 928faabc818..f01ee9d8a58 100644 --- a/doc/src/sgml/ref/move.sgml +++ b/doc/src/sgml/ref/move.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -MOVE [ direction ] [ count ] { IN | FROM } cursor +MOVE [ direction { FROM | IN } ] cursor @@ -33,9 +33,7 @@ MOVE [ direction ] [ - MOVE allows the user to move the cursor position a - specified number of rows, or to the beginning or end of the cursor. - MOVE ALL moves to the end of the cursor. + MOVE repositions a cursor without retrieving any data. MOVE works exactly like the FETCH command, except it only repositions the cursor and does not return rows. @@ -54,8 +52,9 @@ MOVE [ direction ] [ - MOVE is a PostgreSQL - language extension. + The count returned in MOVE's status string is the + count of the number of rows that would have been returned by the + equivalent FETCH command. @@ -119,9 +118,6 @@ COMMIT WORK; There is no SQL92 MOVE statement. - Instead, SQL92 allows - one to FETCH rows from an absolute cursor position, - implicitly moving the cursor to the correct position. -- cgit v1.2.3