summaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/ref/select.sgml48
-rw-r--r--doc/src/sgml/ref/select_into.sgml5
-rw-r--r--doc/src/sgml/ref/values.sgml16
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>
generated by cgit v1.2.3 (git 2.46.0) at 2025-12-04 07:08:21 +0000