From 8ecd5351691210ba5b536cd4c1251ea1fce090bb Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 28 Oct 2008 14:09:45 +0000 Subject: [PATCH] Add WITH [NO] DATA clause to CREATE TABLE AS, per SQL. Also, since WITH is now a reserved word, simplify the token merging code to only deal with WITH_TIME. by Tom Lane and myself --- doc/src/sgml/ref/create_table_as.sgml | 25 +++++++++++++++-------- src/backend/catalog/sql_features.txt | 2 +- src/backend/parser/gram.y | 29 ++++++++++++++++----------- src/backend/parser/parser.c | 21 ++++--------------- src/interfaces/ecpg/preproc/parser.c | 21 ++++--------------- src/interfaces/ecpg/preproc/preproc.y | 16 ++++++--------- 6 files changed, 49 insertions(+), 65 deletions(-) diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index edc9ce5e6c..c3f3c230aa 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,5 +1,5 @@ @@ -26,6 +26,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] AS query + [ WITH [ NO ] DATA ] @@ -201,6 +202,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name + + + WITH [ NO ] DATA + + + This clause specifies whether or not the data produced by the query + should be copied into the new table. If not, only the table structure + is copied. The default is to copy the data. + + + + @@ -265,7 +278,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS CREATE TABLE AS conforms to the SQL - standard, with the following exceptions: + standard. The following are nonstandard extensions: @@ -278,12 +291,8 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS - The standard defines a WITH [ NO ] DATA clause; - this is not currently implemented by PostgreSQL. - The behavior provided by PostgreSQL is equivalent - to the standard's WITH DATA case. - WITH NO DATA can be simulated by appending - LIMIT 0 to the query. + In the standard, the WITH [ NO ] DATA clause + is required; in PostgreSQL it is optional. diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index be9974af27..b795a70f3c 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -408,7 +408,7 @@ T141 SIMILAR predicate YES T151 DISTINCT predicate YES T152 DISTINCT predicate with negation YES T171 LIKE clause in table definition YES -T172 AS subquery clause in table definition NO +T172 AS subquery clause in table definition YES T173 Extended LIKE clause in table definition YES T174 Identity columns NO T175 Generated columns NO diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 6a566fd400..4759e04134 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.630 2008/10/27 09:37:47 petere Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.631 2008/10/28 14:09:45 petere Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -216,7 +216,7 @@ static TypeName *TableFuncTypeName(List *columns); %type opt_lock lock_type cast_context %type opt_force opt_or_replace opt_grant_grant_option opt_grant_admin_option - opt_nowait opt_if_exists + opt_nowait opt_if_exists opt_with_data %type OptRoleList %type OptRoleElem @@ -485,7 +485,7 @@ static TypeName *TableFuncTypeName(List *columns); * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ -%token NULLS_FIRST NULLS_LAST WITH_CASCADED WITH_LOCAL WITH_CHECK +%token NULLS_FIRST NULLS_LAST WITH_TIME /* Special token types, not actually keywords - see the "lex" file */ %token IDENT FCONST SCONST BCONST XCONST Op @@ -2416,7 +2416,7 @@ OptConsTableSpace: USING INDEX TABLESPACE name { $$ = $4; } */ CreateAsStmt: - CREATE OptTemp TABLE create_as_target AS SelectStmt + CREATE OptTemp TABLE create_as_target AS SelectStmt opt_with_data { /* * When the SelectStmt is a set-operation tree, we must @@ -2433,6 +2433,9 @@ CreateAsStmt: scanner_errposition(exprLocation((Node *) n->intoClause)))); $4->rel->istemp = $2; n->intoClause = $4; + /* Implement WITH NO DATA by forcing top-level LIMIT 0 */ + if (!$7) + ((SelectStmt *) $6)->limitCount = makeIntConst(0, -1); $$ = $6; } ; @@ -2475,6 +2478,12 @@ CreateAsElement: } ; +opt_with_data: + WITH DATA_P { $$ = TRUE; } + | WITH NO DATA_P { $$ = FALSE; } + | /*EMPTY*/ { $$ = TRUE; } + ; + /***************************************************************************** * @@ -5387,24 +5396,20 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list } ; -/* - * We use merged tokens here to avoid creating shift/reduce conflicts against - * a whole lot of other uses of WITH. - */ opt_check_option: - WITH_CHECK OPTION + WITH CHECK OPTION { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("WITH CHECK OPTION is not implemented"))); } - | WITH_CASCADED CHECK OPTION + | WITH CASCADED CHECK OPTION { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("WITH CHECK OPTION is not implemented"))); } - | WITH_LOCAL CHECK OPTION + | WITH LOCAL CHECK OPTION { ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -7509,7 +7514,7 @@ ConstInterval: ; opt_timezone: - WITH TIME ZONE { $$ = TRUE; } + WITH_TIME ZONE { $$ = TRUE; } | WITHOUT TIME ZONE { $$ = FALSE; } | /*EMPTY*/ { $$ = FALSE; } ; diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index 1535318735..979f4307fd 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -14,7 +14,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.74 2008/08/29 13:02:32 petere Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.75 2008/10/28 14:09:45 petere Exp $ * *------------------------------------------------------------------------- */ @@ -129,28 +129,15 @@ filtered_base_yylex(void) case WITH: /* - * WITH CASCADED, LOCAL, or CHECK must be reduced to one token - * - * XXX an alternative way is to recognize just WITH_TIME and put - * the ugliness into the datetime datatype productions instead of - * WITH CHECK OPTION. However that requires promoting WITH to a - * fully reserved word. If we ever have to do that anyway - * (perhaps for SQL99 recursive queries), come back and simplify - * this code. + * WITH TIME must be reduced to one token */ cur_yylval = base_yylval; cur_yylloc = base_yylloc; next_token = base_yylex(); switch (next_token) { - case CASCADED: - cur_token = WITH_CASCADED; - break; - case LOCAL: - cur_token = WITH_LOCAL; - break; - case CHECK: - cur_token = WITH_CHECK; + case TIME: + cur_token = WITH_TIME; break; default: /* save the lookahead token for next time */ diff --git a/src/interfaces/ecpg/preproc/parser.c b/src/interfaces/ecpg/preproc/parser.c index 279b53c477..9e23beaf6d 100644 --- a/src/interfaces/ecpg/preproc/parser.c +++ b/src/interfaces/ecpg/preproc/parser.c @@ -14,7 +14,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/parser.c,v 1.3 2008/01/01 19:45:59 momjian Exp $ + * $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/parser.c,v 1.4 2008/10/28 14:09:45 petere Exp $ * *------------------------------------------------------------------------- */ @@ -98,28 +98,15 @@ filtered_base_yylex(void) case WITH: /* - * WITH CASCADED, LOCAL, or CHECK must be reduced to one token - * - * XXX an alternative way is to recognize just WITH_TIME and put - * the ugliness into the datetime datatype productions instead of - * WITH CHECK OPTION. However that requires promoting WITH to a - * fully reserved word. If we ever have to do that anyway - * (perhaps for SQL99 recursive queries), come back and simplify - * this code. + * WITH TIME must be reduced to one token */ cur_yylval = base_yylval; cur_yylloc = base_yylloc; next_token = base_yylex(); switch (next_token) { - case CASCADED: - cur_token = WITH_CASCADED; - break; - case LOCAL: - cur_token = WITH_LOCAL; - break; - case CHECK: - cur_token = WITH_CHECK; + case TIME: + cur_token = WITH_TIME; break; default: /* save the lookahead token for next time */ diff --git a/src/interfaces/ecpg/preproc/preproc.y b/src/interfaces/ecpg/preproc/preproc.y index 038942da29..2fbbbd94fc 100644 --- a/src/interfaces/ecpg/preproc/preproc.y +++ b/src/interfaces/ecpg/preproc/preproc.y @@ -1,4 +1,4 @@ -/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.378 2008/10/27 09:37:47 petere Exp $ */ +/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.379 2008/10/28 14:09:45 petere Exp $ */ /* Copyright comment */ %{ @@ -505,7 +505,7 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu * list and so can never be entered directly. The filter in parser.c * creates these tokens when required. */ -%token NULLS_FIRST NULLS_LAST WITH_CASCADED WITH_LOCAL WITH_CHECK +%token NULLS_FIRST NULLS_LAST WITH_TIME /* Special token types, not actually keywords - see the "lex" file */ %token IDENT SCONST Op CSTRING CVARIABLE CPP_LINE IP BCONST @@ -3100,22 +3100,18 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list AS SelectStmt opt_ { $$ = cat_str(8, make_str("create or replace"), $4, make_str("view"), $6, $7, make_str("as"), $9, $10); } ; -/* - * We use merged tokens here to avoid creating shift/reduce conflicts against - * a whole lot of other uses of WITH. - */ opt_check_option: - WITH_CHECK OPTION + WITH CHECK OPTION { mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented"); $$ = EMPTY; } - | WITH_CASCADED CHECK OPTION + | WITH CASCADED CHECK OPTION { mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented"); $$ = EMPTY; } - | WITH_LOCAL CHECK OPTION + | WITH LOCAL CHECK OPTION { mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented"); $$ = EMPTY; @@ -4155,7 +4151,7 @@ ConstInterval: INTERVAL { $$ = make_str("interval"); } ; -opt_timezone: WITH TIME ZONE +opt_timezone: WITH_TIME ZONE { $$ = make_str("with time zone"); } | WITHOUT TIME ZONE { $$ = make_str("without time zone"); } -- 2.40.0