SQL:2008 alternative syntax for LIMIT/OFFSET:

OFFSET num {ROW|ROWS} FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY

3 files changed, 55 insertions, 14 deletions

diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index f73ca6ed64a..91a756b3ae2 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.106 2008/10/07 19:27:04 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.107 2008/10/22 11:00:33 petere Exp $
 PostgreSQL documentation
 -->
 
@@ -30,7 +30,8 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
-    [ OFFSET <replaceable class="parameter">start</replaceable> ]
+    [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
+    [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
     [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
 
 where <replaceable class="parameter">from_item</replaceable> can be one of:
@@ -150,7 +151,7 @@ and <replaceable class="parameter">with_query</replaceable> is:
 
     <listitem>
      <para>
-      If the <literal>LIMIT</literal> or <literal>OFFSET</literal>
+      If the <literal>LIMIT</literal> (or <literal>FETCH FIRST</literal>) or <literal>OFFSET</literal>
       clause is specified, the <command>SELECT</command> statement
       only returns a subset of the result rows. (See <xref
       linkend="sql-limit" endterm="sql-limit-title"> below.)
@@ -892,6 +893,24 @@ OFFSET <replaceable class="parameter">start</replaceable>
    </para>
 
    <para>
+    SQL:2008 introduced a different syntax to achieve the same thing,
+    which PostgreSQL also supports.  It is:
+<synopsis>
+OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
+FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
+</synopsis>
+    Both clauses are optional, but if present
+    the <literal>OFFSET</literal> clause must come before
+    the <literal>FETCH</literal> clause.  <literal>ROW</literal>
+    and <literal>ROWS</literal> as well as <literal>FIRST</literal>
+    and <literal>NEXT</literal> are noise words that don't influence
+    the effects of these clauses.  When using expressions other than
+    constants for the offset or fetch count, parentheses will be
+    necessary in most cases.  If the fetch count is omitted, it
+    defaults to 1.
+   </para>    
+
+   <para>
     When using <literal>LIMIT</>, it is a good idea to use an
     <literal>ORDER BY</> clause that constrains the result rows into a
     unique order.  Otherwise you will get an unpredictable subset of
@@ -1338,12 +1357,29 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
   </refsect2>
 
   <refsect2>
+   <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
+
+   <para>
+    The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
+    are <productname>PostgreSQL</productname>-specific syntax, also
+    used by <productname>MySQL</productname>.  The SQL:2008 standard
+    has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
+    ...</literal> for the same functionality, as shown above
+    in <xref linkend="sql-limit" endterm="sql-limit-title">, and this
+    syntax is also used by <productname>IBM DB2</productname>.
+    (Applications written for <productname>Oracle</productname>
+    frequently use a workaround involving the automatically
+    generated <literal>rownum</literal> column, not available in
+    PostgreSQL, to implement the effects of these clauses.)
+   </para>
+  </refsect2>
+
+  <refsect2>
    <title>Nonstandard Clauses</title>
 
    <para>
-    The clauses <literal>DISTINCT ON</literal>,
-    <literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
-    defined in the SQL standard.
+    The clause <literal>DISTINCT ON</literal> is not defined in the
+    SQL standard.
    </para>
   </refsect2>
  </refsect1>
diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml
index de9a86a878c..cbe46cf763e 100644
--- a/doc/src/sgml/ref/select_into.sgml
+++ b/doc/src/sgml/ref/select_into.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.41 2008/10/04 21:56:52 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.42 2008/10/22 11:00:34 petere Exp $
 PostgreSQL documentation
 -->
 
@@ -31,7 +31,8 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
     [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
-    [ OFFSET <replaceable class="parameter">start</replaceable> ]
+    [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
+    [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
     [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
 </synopsis>
  </refsynopsisdiv>
diff --git a/doc/src/sgml/ref/values.sgml b/doc/src/sgml/ref/values.sgml
index 23b84d78203..ef605f818f0 100644
--- a/doc/src/sgml/ref/values.sgml
+++ b/doc/src/sgml/ref/values.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/values.sgml,v 1.4 2007/02/01 00:28:19 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/values.sgml,v 1.5 2008/10/22 11:00:34 petere Exp $
 PostgreSQL documentation
 -->
 
@@ -23,7 +23,8 @@ PostgreSQL documentation
 VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ...]
     [ ORDER BY <replaceable class="parameter">sort_expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
-    [ OFFSET <replaceable class="parameter">start</replaceable> ]
+    [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
+    [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -48,8 +49,10 @@ VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ..
   <para>
    Within larger commands, <command>VALUES</> is syntactically allowed
    anywhere that <command>SELECT</> is.  Because it is treated like a
-   <command>SELECT</> by the grammar, it is possible to use the <literal>ORDER
-   BY</>, <literal>LIMIT</>, and <literal>OFFSET</> clauses with a
+   <command>SELECT</> by the grammar, it is possible to use
+   the <literal>ORDER BY</>, <literal>LIMIT</> (or
+   equivalently <literal>FETCH FIRST</literal>),
+   and <literal>OFFSET</> clauses with a
    <command>VALUES</> command.
   </para>
  </refsect1>
@@ -227,9 +230,10 @@ WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.
   <title>Compatibility</title>
 
   <para>
-   <command>VALUES</command> conforms to the SQL standard, except that
+   <command>VALUES</command> conforms to the SQL standard.
    <literal>LIMIT</literal> and <literal>OFFSET</literal> are
-   <productname>PostgreSQL</productname> extensions.
+   <productname>PostgreSQL</productname> extensions; see also
+   under <xref linkend="sql-select" endterm="sql-select-title">.
   </para>
  </refsect1>