From 361bfc35724aac207f7a013336e058704fbadf60 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 22 Oct 2008 11:00:34 +0000
Subject: SQL:2008 alternative syntax for LIMIT/OFFSET: OFFSET num {ROW|ROWS}
 FETCH {FIRST|NEXT} [num] {ROW|ROWS} ONLY

---
 src/backend/catalog/sql_features.txt | 18 +++++++--------
 src/backend/parser/gram.y            | 44 ++++++++++++++++++++++++++++++++++--
 src/backend/parser/keywords.c        |  4 ++--
 3 files changed, 53 insertions(+), 13 deletions(-)

(limited to 'src')

diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 4c50a22a0a..b90856fafb 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -319,15 +319,15 @@ F851	<order by clause> in subqueries			YES
 F852	Top-level <order by clause> in views			YES	
 F855	Nested <order by clause> in <query expression>			YES	
 F856	Nested <fetch first clause> in <query expression>			YES	
-F857	Top-level <fetch first clause> in <query expression>			NO	same as LIMIT
-F858	<fetch first clause> in subqueries			NO	same as LIMIT
-F859	Top-level <fetch first clause> in views			NO	same as LIMIT
-F860	<fetch first row count> in <fetch first clause>			NO	same as LIMIT
-F861	Top-level <result offset clause> in <query expression>			NO	same as OFFSET
-F862	<result offset clause> in subqueries			NO	same as OFFSET
-F863	Nested <result offset clause> in <query expression>			NO	same as OFFSET
-F864	Top-level <result offset clause> in views			NO	same as OFFSET
-F865	<offset row count> in <result offset clause>			NO	same as OFFSET
+F857	Top-level <fetch first clause> in <query expression>			YES	
+F858	<fetch first clause> in subqueries			YES	
+F859	Top-level <fetch first clause> in views			YES	
+F860	<fetch first row count> in <fetch first clause>			YES	
+F861	Top-level <result offset clause> in <query expression>			YES	
+F862	<result offset clause> in subqueries			YES	
+F863	Nested <result offset clause> in <query expression>			YES	
+F864	Top-level <result offset clause> in views			YES	
+F865	<offset row count> in <result offset clause>			YES	
 S011	Distinct data types			NO	
 S011	Distinct data types	01	USER_DEFINED_TYPES view	NO	
 S023	Basic structured types			NO	
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index aa5f33b411..3ab62b23cb 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.627 2008/10/21 08:38:15 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.628 2008/10/22 11:00:34 petere Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -308,6 +308,8 @@ static TypeName *TableFuncTypeName(List *columns);
 %type <objtype>	reindex_type drop_type comment_type
 
 %type <node>	fetch_direction select_limit_value select_offset_value
+				select_offset_value2 opt_select_fetch_first_value
+%type <ival>	row_or_rows first_or_next
 
 %type <list>	OptSeqOptList SeqOptList
 %type <defelt>	SeqOptElem
@@ -6579,6 +6581,13 @@ select_limit:
 							 errhint("Use separate LIMIT and OFFSET clauses."),
 							 scanner_errposition(@1)));
 				}
+			/* SQL:2008 syntax variants */
+			| OFFSET select_offset_value2 row_or_rows
+				{ $$ = list_make2($2, NULL); }
+			| FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
+				{ $$ = list_make2(NULL, $3); }
+			| OFFSET select_offset_value2 row_or_rows FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
+				{ $$ = list_make2($2, $6); }
 		;
 
 opt_select_limit:
@@ -6596,10 +6605,40 @@ select_limit_value:
 				}
 		;
 
+/*
+ * Allowing full expressions without parentheses causes various parsing
+ * problems with the trailing ROW/ROWS key words.  SQL only calls for
+ * constants, so we allow the rest only with parentheses.
+ */
+opt_select_fetch_first_value:
+			SignedIconst		{ $$ = makeIntConst($1, @1); }
+			| '(' a_expr ')'	{ $$ = $2; }
+			| /*EMPTY*/		{ $$ = makeIntConst(1, -1); }
+		;
+
 select_offset_value:
 			a_expr									{ $$ = $1; }
 		;
 
+/*
+ * Again, the trailing ROW/ROWS in this case prevent the full expression
+ * syntax.  c_expr is the best we can do.
+ */
+select_offset_value2:
+			c_expr									{ $$ = $1; }
+		;
+
+/* noise words */
+row_or_rows:
+			ROW		{ $$ = 0; }
+			| ROWS		{ $$ = 0; }
+
+/* noise words */
+first_or_next:
+			FIRST_P		{ $$ = 0; }
+			| NEXT		{ $$ = 0; }
+
+
 group_clause:
 			GROUP_P BY expr_list					{ $$ = $3; }
 			| /*EMPTY*/								{ $$ = NIL; }
@@ -9218,6 +9257,7 @@ Sconst:		SCONST									{ $$ = $1; };
 RoleId:		ColId									{ $$ = $1; };
 
 SignedIconst: ICONST								{ $$ = $1; }
+			| '+' ICONST							{ $$ = + $2; }
 			| '-' ICONST							{ $$ = - $2; }
 		;
 
@@ -9351,7 +9391,6 @@ unreserved_keyword:
 			| EXPLAIN
 			| EXTERNAL
 			| FAMILY
-			| FETCH
 			| FIRST_P
 			| FORCE
 			| FORWARD
@@ -9641,6 +9680,7 @@ reserved_keyword:
 			| END_P
 			| EXCEPT
 			| FALSE_P
+			| FETCH
 			| FOR
 			| FOREIGN
 			| FROM
diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c
index 7c6aa71572..608e80e0f5 100644
--- a/src/backend/parser/keywords.c
+++ b/src/backend/parser/keywords.c
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.203 2008/10/21 08:38:15 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.204 2008/10/22 11:00:34 petere Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -166,7 +166,7 @@ const ScanKeyword ScanKeywords[] = {
 	{"extract", EXTRACT, COL_NAME_KEYWORD},
 	{"false", FALSE_P, RESERVED_KEYWORD},
 	{"family", FAMILY, UNRESERVED_KEYWORD},
-	{"fetch", FETCH, UNRESERVED_KEYWORD},
+	{"fetch", FETCH, RESERVED_KEYWORD},
 	{"first", FIRST_P, UNRESERVED_KEYWORD},
 	{"float", FLOAT_P, COL_NAME_KEYWORD},
 	{"for", FOR, RESERVED_KEYWORD},
-- 
cgit v1.2.1