Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit361bfc3

Browse files
committed
SQL:2008 alternative syntax for LIMIT/OFFSET:
OFFSET num {ROW|ROWS} FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY
1 parente6ae3b5 commit361bfc3

File tree

6 files changed

+108
-27
lines changed

6 files changed

+108
-27
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 42 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.106 2008/10/07 19:27:04 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select.sgml,v 1.107 2008/10/22 11:00:33 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -30,7 +30,8 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
3030
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
3131
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
3232
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
33-
[ OFFSET <replaceable class="parameter">start</replaceable> ]
33+
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
34+
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
3435
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
3536

3637
where <replaceable class="parameter">from_item</replaceable> can be one of:
@@ -150,7 +151,7 @@ and <replaceable class="parameter">with_query</replaceable> is:
150151

151152
<listitem>
152153
<para>
153-
If the <literal>LIMIT</literal> or <literal>OFFSET</literal>
154+
If the <literal>LIMIT</literal>(or <literal>FETCH FIRST</literal>)or <literal>OFFSET</literal>
154155
clause is specified, the <command>SELECT</command> statement
155156
only returns a subset of the result rows. (See <xref
156157
linkend="sql-limit" endterm="sql-limit-title"> below.)
@@ -891,6 +892,24 @@ OFFSET <replaceable class="parameter">start</replaceable>
891892
class="parameter">count</replaceable> rows to be returned.
892893
</para>
893894

895+
<para>
896+
SQL:2008 introduced a different syntax to achieve the same thing,
897+
which PostgreSQL also supports. It is:
898+
<synopsis>
899+
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
900+
FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
901+
</synopsis>
902+
Both clauses are optional, but if present
903+
the <literal>OFFSET</literal> clause must come before
904+
the <literal>FETCH</literal> clause. <literal>ROW</literal>
905+
and <literal>ROWS</literal> as well as <literal>FIRST</literal>
906+
and <literal>NEXT</literal> are noise words that don't influence
907+
the effects of these clauses. When using expressions other than
908+
constants for the offset or fetch count, parentheses will be
909+
necessary in most cases. If the fetch count is omitted, it
910+
defaults to 1.
911+
</para>
912+
894913
<para>
895914
When using <literal>LIMIT</>, it is a good idea to use an
896915
<literal>ORDER BY</> clause that constrains the result rows into a
@@ -1337,13 +1356,30 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
13371356
</para>
13381357
</refsect2>
13391358

1359+
<refsect2>
1360+
<title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>
1361+
1362+
<para>
1363+
The clauses <literal>LIMIT</literal> and <literal>OFFSET</literal>
1364+
are <productname>PostgreSQL</productname>-specific syntax, also
1365+
used by <productname>MySQL</productname>. The SQL:2008 standard
1366+
has introduced the clauses <literal>OFFSET ... FETCH {FIRST|NEXT}
1367+
...</literal> for the same functionality, as shown above
1368+
in <xref linkend="sql-limit" endterm="sql-limit-title">, and this
1369+
syntax is also used by <productname>IBM DB2</productname>.
1370+
(Applications written for <productname>Oracle</productname>
1371+
frequently use a workaround involving the automatically
1372+
generated <literal>rownum</literal> column, not available in
1373+
PostgreSQL, to implement the effects of these clauses.)
1374+
</para>
1375+
</refsect2>
1376+
13401377
<refsect2>
13411378
<title>Nonstandard Clauses</title>
13421379

13431380
<para>
1344-
The clauses <literal>DISTINCT ON</literal>,
1345-
<literal>LIMIT</literal>, and <literal>OFFSET</literal> are not
1346-
defined in the SQL standard.
1381+
The clause <literal>DISTINCT ON</literal> is not defined in the
1382+
SQL standard.
13471383
</para>
13481384
</refsect2>
13491385
</refsect1>

‎doc/src/sgml/ref/select_into.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.41 2008/10/04 21:56:52 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/select_into.sgml,v 1.42 2008/10/22 11:00:34 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -31,7 +31,8 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
3131
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="parameter">select</replaceable> ]
3232
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
3333
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
34-
[ OFFSET <replaceable class="parameter">start</replaceable> ]
34+
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
35+
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
3536
[ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] [...] ]
3637
</synopsis>
3738
</refsynopsisdiv>

‎doc/src/sgml/ref/values.sgml

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/values.sgml,v 1.4 2007/02/0100:28:19 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/values.sgml,v 1.5 2008/10/22 11:00:34 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,7 +23,8 @@ PostgreSQL documentation
2323
VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ...]
2424
[ ORDER BY <replaceable class="parameter">sort_expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
2525
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
26-
[ OFFSET <replaceable class="parameter">start</replaceable> ]
26+
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
27+
[ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
2728
</synopsis>
2829
</refsynopsisdiv>
2930

@@ -48,8 +49,10 @@ VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ..
4849
<para>
4950
Within larger commands, <command>VALUES</> is syntactically allowed
5051
anywhere that <command>SELECT</> is. Because it is treated like a
51-
<command>SELECT</> by the grammar, it is possible to use the <literal>ORDER
52-
BY</>, <literal>LIMIT</>, and <literal>OFFSET</> clauses with a
52+
<command>SELECT</> by the grammar, it is possible to use
53+
the <literal>ORDER BY</>, <literal>LIMIT</> (or
54+
equivalently <literal>FETCH FIRST</literal>),
55+
and <literal>OFFSET</> clauses with a
5356
<command>VALUES</> command.
5457
</para>
5558
</refsect1>
@@ -227,9 +230,10 @@ WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.
227230
<title>Compatibility</title>
228231

229232
<para>
230-
<command>VALUES</command> conforms to the SQL standard, except that
233+
<command>VALUES</command> conforms to the SQL standard.
231234
<literal>LIMIT</literal> and <literal>OFFSET</literal> are
232-
<productname>PostgreSQL</productname> extensions.
235+
<productname>PostgreSQL</productname> extensions; see also
236+
under <xref linkend="sql-select" endterm="sql-select-title">.
233237
</para>
234238
</refsect1>
235239

‎src/backend/catalog/sql_features.txt

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -319,15 +319,15 @@ F851<order by clause> in subqueriesYES
319319
F852Top-level <order by clause> in viewsYES
320320
F855Nested <order by clause> in <query expression>YES
321321
F856Nested <fetch first clause> in <query expression>YES
322-
F857Top-level <fetch first clause> in <query expression>NOsame as LIMIT
323-
F858<fetch first clause> in subqueriesNOsame as LIMIT
324-
F859Top-level <fetch first clause> in viewsNOsame as LIMIT
325-
F860<fetch first row count> in <fetch first clause>NOsame as LIMIT
326-
F861Top-level <result offset clause> in <query expression>NOsame as OFFSET
327-
F862<result offset clause> in subqueriesNOsame as OFFSET
328-
F863Nested <result offset clause> in <query expression>NOsame as OFFSET
329-
F864Top-level <result offset clause> in viewsNOsame as OFFSET
330-
F865<offset row count> in <result offset clause>NOsame as OFFSET
322+
F857Top-level <fetch first clause> in <query expression>YES
323+
F858<fetch first clause> in subqueriesYES
324+
F859Top-level <fetch first clause> in viewsYES
325+
F860<fetch first row count> in <fetch first clause>YES
326+
F861Top-level <result offset clause> in <query expression>YES
327+
F862<result offset clause> in subqueriesYES
328+
F863Nested <result offset clause> in <query expression>YES
329+
F864Top-level <result offset clause> in viewsYES
330+
F865<offset row count> in <result offset clause>YES
331331
S011Distinct data typesNO
332332
S011Distinct data types01USER_DEFINED_TYPES viewNO
333333
S023Basic structured typesNO

‎src/backend/parser/gram.y

Lines changed: 42 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.627 2008/10/21 08:38:15 petere Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.628 2008/10/22 11:00:34 petere Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -308,6 +308,8 @@ static TypeName *TableFuncTypeName(List *columns);
308308
%type<objtype>reindex_typedrop_typecomment_type
309309

310310
%type<node>fetch_directionselect_limit_valueselect_offset_value
311+
select_offset_value2opt_select_fetch_first_value
312+
%type<ival>row_or_rowsfirst_or_next
311313

312314
%type<list>OptSeqOptListSeqOptList
313315
%type<defelt>SeqOptElem
@@ -6579,6 +6581,13 @@ select_limit:
65796581
errhint("Use separate LIMIT and OFFSET clauses."),
65806582
scanner_errposition(@1)));
65816583
}
6584+
/* SQL:2008 syntax variants*/
6585+
| OFFSET select_offset_value2 row_or_rows
6586+
{$$ = list_make2($2,NULL); }
6587+
| FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
6588+
{$$ = list_make2(NULL,$3); }
6589+
| OFFSET select_offset_value2 row_or_rows FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
6590+
{$$ = list_make2($2,$6); }
65826591
;
65836592

65846593
opt_select_limit:
@@ -6596,10 +6605,40 @@ select_limit_value:
65966605
}
65976606
;
65986607

6608+
/*
6609+
* Allowing full expressions without parentheses causes various parsing
6610+
* problems with the trailing ROW/ROWS key words. SQL only calls for
6611+
* constants, so we allow the rest only with parentheses.
6612+
*/
6613+
opt_select_fetch_first_value:
6614+
SignedIconst{$$ = makeIntConst($1,@1); }
6615+
|'(' a_expr')'{$$ =$2; }
6616+
|/*EMPTY*/{$$ = makeIntConst(1, -1); }
6617+
;
6618+
65996619
select_offset_value:
66006620
a_expr{$$ =$1; }
66016621
;
66026622

6623+
/*
6624+
* Again, the trailing ROW/ROWS in this case prevent the full expression
6625+
* syntax. c_expr is the best we can do.
6626+
*/
6627+
select_offset_value2:
6628+
c_expr{$$ =$1; }
6629+
;
6630+
6631+
/* noise words*/
6632+
row_or_rows:
6633+
ROW{$$ =0; }
6634+
| ROWS{$$ =0; }
6635+
6636+
/* noise words*/
6637+
first_or_next:
6638+
FIRST_P{$$ =0; }
6639+
| NEXT{$$ =0; }
6640+
6641+
66036642
group_clause:
66046643
GROUP_P BY expr_list{$$ =$3; }
66056644
|/*EMPTY*/{$$ = NIL; }
@@ -9218,6 +9257,7 @@ Sconst:SCONST{ $$ = $1; };
92189257
RoleId:ColId{ $$ = $1; };
92199258

92209259
SignedIconst: ICONST{ $$ = $1; }
9260+
|'+' ICONST{ $$ = + $2; }
92219261
|'-' ICONST{ $$ = - $2; }
92229262
;
92239263

@@ -9351,7 +9391,6 @@ unreserved_keyword:
93519391
| EXPLAIN
93529392
| EXTERNAL
93539393
| FAMILY
9354-
| FETCH
93559394
| FIRST_P
93569395
| FORCE
93579396
| FORWARD
@@ -9641,6 +9680,7 @@ reserved_keyword:
96419680
| END_P
96429681
| EXCEPT
96439682
| FALSE_P
9683+
| FETCH
96449684
| FOR
96459685
| FOREIGN
96469686
| FROM

‎src/backend/parser/keywords.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.203 2008/10/21 08:38:15 petere Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.204 2008/10/22 11:00:34 petere Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -166,7 +166,7 @@ const ScanKeyword ScanKeywords[] = {
166166
{"extract",EXTRACT,COL_NAME_KEYWORD},
167167
{"false",FALSE_P,RESERVED_KEYWORD},
168168
{"family",FAMILY,UNRESERVED_KEYWORD},
169-
{"fetch",FETCH,UNRESERVED_KEYWORD},
169+
{"fetch",FETCH,RESERVED_KEYWORD},
170170
{"first",FIRST_P,UNRESERVED_KEYWORD},
171171
{"float",FLOAT_P,COL_NAME_KEYWORD},
172172
{"for",FOR,RESERVED_KEYWORD},

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp