Обсуждение: CREATE TABLE AS WITH NO DATA
Another small piece of parser acrobatics to become standard conforming.
? src/backend/parser/gram.output
Index: doc/src/sgml/ref/create_table_as.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_table_as.sgml,v
retrieving revision 1.37
diff -u -3 -p -c -r1.37 create_table_as.sgml
*** doc/src/sgml/ref/create_table_as.sgml 3 Jun 2007 17:06:12 -0000 1.37
--- doc/src/sgml/ref/create_table_as.sgml 27 Oct 2008 16:54:29 -0000
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 26,31 ****
--- 26,32 ----
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
AS <replaceable>query</replaceable>
+ [ WITH [ NO ] DATA ]
</synopsis>
</refsynopsisdiv>
*************** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY
*** 201,206 ****
--- 202,219 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>WITH [ NO ] DATA</></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
*************** CREATE TEMP TABLE films_recent WITH (OID
*** 265,271 ****
<para>
<command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
! standard, with the following exceptions:
<itemizedlist spacing="compact">
<listitem>
--- 278,284 ----
<para>
<command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
! standard. The following are nonstandard extensions:
<itemizedlist spacing="compact">
<listitem>
*************** CREATE TEMP TABLE films_recent WITH (OID
*** 278,289 ****
<listitem>
<para>
! The standard defines a <literal>WITH [ NO ] DATA</literal> clause;
! this is not currently implemented by <productname>PostgreSQL</>.
! The behavior provided by <productname>PostgreSQL</> is equivalent
! to the standard's <literal>WITH DATA</literal> case.
! <literal>WITH NO DATA</literal> can be simulated by appending
! <literal>LIMIT 0</> to the query.
</para>
</listitem>
--- 291,298 ----
<listitem>
<para>
! In the standard, the <literal>WITH [ NO ] DATA</literal> clause
! is required; in PostgreSQL it is optional.
</para>
</listitem>
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.628
diff -u -3 -p -c -r2.628 gram.y
*** src/backend/parser/gram.y 22 Oct 2008 11:00:34 -0000 2.628
--- src/backend/parser/gram.y 27 Oct 2008 16:54:29 -0000
*************** static TypeName *TableFuncTypeName(List
*** 216,222 ****
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait opt_if_exists
%type <list> OptRoleList
%type <defelt> OptRoleElem
--- 216,222 ----
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait opt_if_exists opt_with_data
%type <list> OptRoleList
%type <defelt> OptRoleElem
*************** static TypeName *TableFuncTypeName(List
*** 484,490 ****
* 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
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT FCONST SCONST BCONST XCONST Op
--- 484,490 ----
* 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_CHECK WITH_DATA WITH_LOCAL WITH_NO
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT FCONST SCONST BCONST XCONST Op
*************** OptConsTableSpace: USING INDEX TABLESP
*** 2399,2405 ****
*/
CreateAsStmt:
! CREATE OptTemp TABLE create_as_target AS SelectStmt
{
/*
* When the SelectStmt is a set-operation tree, we must
--- 2399,2405 ----
*/
CreateAsStmt:
! CREATE OptTemp TABLE create_as_target AS SelectStmt opt_with_data
{
/*
* When the SelectStmt is a set-operation tree, we must
*************** CreateAsStmt:
*** 2416,2421 ****
--- 2416,2423 ----
scanner_errposition(exprLocation((Node *) n->intoClause))));
$4->rel->istemp = $2;
n->intoClause = $4;
+ if (!$7)
+ ((SelectStmt *)$6)->limitCount = makeIntConst(0, -1);
$$ = $6;
}
;
*************** CreateAsElement:
*** 2458,2463 ****
--- 2460,2471 ----
}
;
+ opt_with_data:
+ WITH_DATA { $$ = TRUE; }
+ | WITH_NO DATA_P { $$ = FALSE; }
+ | /*EMPTY*/ { $$ = TRUE; }
+ ;
+
/*****************************************************************************
*
Index: src/backend/parser/parser.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/parser.c,v
retrieving revision 1.74
diff -u -3 -p -c -r1.74 parser.c
*** src/backend/parser/parser.c 29 Aug 2008 13:02:32 -0000 1.74
--- src/backend/parser/parser.c 27 Oct 2008 16:54:29 -0000
*************** filtered_base_yylex(void)
*** 129,142 ****
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.
*/
cur_yylval = base_yylval;
cur_yylloc = base_yylloc;
--- 129,141 ----
case WITH:
/*
! * WITH CASCADED, DATA, 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,
! * come back and simplify this code.
*/
cur_yylval = base_yylval;
cur_yylloc = base_yylloc;
*************** filtered_base_yylex(void)
*** 146,156 ****
case CASCADED:
cur_token = WITH_CASCADED;
break;
case LOCAL:
cur_token = WITH_LOCAL;
break;
! case CHECK:
! cur_token = WITH_CHECK;
break;
default:
/* save the lookahead token for next time */
--- 145,161 ----
case CASCADED:
cur_token = WITH_CASCADED;
break;
+ case CHECK:
+ cur_token = WITH_CHECK;
+ break;
+ case DATA_P:
+ cur_token = WITH_DATA;
+ break;
case LOCAL:
cur_token = WITH_LOCAL;
break;
! case NO:
! cur_token = WITH_NO;
break;
default:
/* save the lookahead token for next time */
Peter Eisentraut <peter_e@gmx.net> writes:
> Another small piece of parser acrobatics to become standard conforming.
I think we deliberately decided that we weren't going to implement this
syntax because it required this sort of pushup to provide a uselessly
redundant behavior. Adding more special cases to base_yylex is not
free, either in maintenance or in surprises for users. An example is
that trying to use LOCAL or NO as the name of a CTE will fail if this
patch is applied.
I'd like us to be trying to get rid of the special cases in base_yylex
not add more. (It strikes me that now that WITH is fully reserved,
we might not need some of the ones that are there anymore.)
regards, tom lane
I wrote:
> I'd like us to be trying to get rid of the special cases in base_yylex
> not add more. (It strikes me that now that WITH is fully reserved,
> we might not need some of the ones that are there anymore.)
In fact, it looks like what we should do is heed the existing comment
in parser.c: remove the existing WITH_foo combined tokens and invent
WITH_TIME instead. Then, no additional combined tokens are needed to
handle WITH [NO] DATA. So I propose the attached form of the patch
instead (docs omitted).
regards, tom lane
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.630
diff -c -r2.630 gram.y
*** src/backend/parser/gram.y 27 Oct 2008 09:37:47 -0000 2.630
--- src/backend/parser/gram.y 28 Oct 2008 00:46:25 -0000
***************
*** 216,222 ****
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait opt_if_exists
%type <list> OptRoleList
%type <defelt> OptRoleElem
--- 216,222 ----
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait opt_if_exists opt_with_data
%type <list> OptRoleList
%type <defelt> OptRoleElem
***************
*** 485,491 ****
* 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
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT FCONST SCONST BCONST XCONST Op
--- 485,491 ----
* list and so can never be entered directly. The filter in parser.c
* creates these tokens when required.
*/
! %token NULLS_FIRST NULLS_LAST WITH_TIME
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT FCONST SCONST BCONST XCONST Op
***************
*** 2416,2422 ****
*/
CreateAsStmt:
! CREATE OptTemp TABLE create_as_target AS SelectStmt
{
/*
* When the SelectStmt is a set-operation tree, we must
--- 2416,2422 ----
*/
CreateAsStmt:
! CREATE OptTemp TABLE create_as_target AS SelectStmt opt_with_data
{
/*
* When the SelectStmt is a set-operation tree, we must
***************
*** 2433,2438 ****
--- 2433,2441 ----
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,2480 ****
--- 2478,2489 ----
}
;
+ opt_with_data:
+ WITH DATA_P { $$ = TRUE; }
+ | WITH NO DATA_P { $$ = FALSE; }
+ | /*EMPTY*/ { $$ = TRUE; }
+ ;
+
/*****************************************************************************
*
***************
*** 5387,5410 ****
}
;
- /*
- * 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
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("WITH CHECK OPTION is not implemented")));
}
! | WITH_CASCADED CHECK OPTION
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("WITH CHECK OPTION is not implemented")));
}
! | WITH_LOCAL CHECK OPTION
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
--- 5396,5415 ----
}
;
opt_check_option:
! WITH CHECK OPTION
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("WITH CHECK OPTION is not implemented")));
}
! | WITH CASCADED CHECK OPTION
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("WITH CHECK OPTION is not implemented")));
}
! | WITH LOCAL CHECK OPTION
{
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
***************
*** 7509,7515 ****
;
opt_timezone:
! WITH TIME ZONE { $$ = TRUE; }
| WITHOUT TIME ZONE { $$ = FALSE; }
| /*EMPTY*/ { $$ = FALSE; }
;
--- 7514,7520 ----
;
opt_timezone:
! WITH_TIME ZONE { $$ = TRUE; }
| WITHOUT TIME ZONE { $$ = FALSE; }
| /*EMPTY*/ { $$ = FALSE; }
;
Index: src/backend/parser/parser.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/parser.c,v
retrieving revision 1.74
diff -c -r1.74 parser.c
*** src/backend/parser/parser.c 29 Aug 2008 13:02:32 -0000 1.74
--- src/backend/parser/parser.c 28 Oct 2008 00:46:25 -0000
***************
*** 129,156 ****
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.
*/
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;
break;
default:
/* save the lookahead token for next time */
--- 129,143 ----
case WITH:
/*
! * WITH TIME must be reduced to one token
*/
cur_yylval = base_yylval;
cur_yylloc = base_yylloc;
next_token = base_yylex();
switch (next_token)
{
! case TIME:
! cur_token = WITH_TIME;
break;
default:
/* save the lookahead token for next time */
Index: src/interfaces/ecpg/preproc/parser.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/parser.c,v
retrieving revision 1.3
diff -c -r1.3 parser.c
*** src/interfaces/ecpg/preproc/parser.c 1 Jan 2008 19:45:59 -0000 1.3
--- src/interfaces/ecpg/preproc/parser.c 28 Oct 2008 00:46:25 -0000
***************
*** 98,125 ****
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.
*/
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;
break;
default:
/* save the lookahead token for next time */
--- 98,112 ----
case WITH:
/*
! * WITH TIME must be reduced to one token
*/
cur_yylval = base_yylval;
cur_yylloc = base_yylloc;
next_token = base_yylex();
switch (next_token)
{
! case TIME:
! cur_token = WITH_TIME;
break;
default:
/* save the lookahead token for next time */
Index: src/interfaces/ecpg/preproc/preproc.y
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/preproc.y,v
retrieving revision 1.378
diff -c -r1.378 preproc.y
*** src/interfaces/ecpg/preproc/preproc.y 27 Oct 2008 09:37:47 -0000 1.378
--- src/interfaces/ecpg/preproc/preproc.y 28 Oct 2008 00:46:25 -0000
***************
*** 505,511 ****
* 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
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT SCONST Op CSTRING CVARIABLE CPP_LINE IP BCONST
--- 505,511 ----
* list and so can never be entered directly. The filter in parser.c
* creates these tokens when required.
*/
! %token NULLS_FIRST NULLS_LAST WITH_TIME
/* Special token types, not actually keywords - see the "lex" file */
%token <str> IDENT SCONST Op CSTRING CVARIABLE CPP_LINE IP BCONST
***************
*** 3100,3121 ****
{ $$ = 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
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
}
! | WITH_CASCADED CHECK OPTION
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
}
! | WITH_LOCAL CHECK OPTION
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
--- 3100,3117 ----
{ $$ = cat_str(8, make_str("create or replace"), $4, make_str("view"), $6, $7, make_str("as"), $9, $10);
}
;
opt_check_option:
! WITH CHECK OPTION
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
}
! | WITH CASCADED CHECK OPTION
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
}
! | WITH LOCAL CHECK OPTION
{
mmerror(PARSE_ERROR, ET_ERROR, "WITH CHECK OPTION not implemented");
$$ = EMPTY;
***************
*** 4155,4161 ****
{ $$ = make_str("interval"); }
;
! opt_timezone: WITH TIME ZONE
{ $$ = make_str("with time zone"); }
| WITHOUT TIME ZONE
{ $$ = make_str("without time zone"); }
--- 4151,4157 ----
{ $$ = make_str("interval"); }
;
! opt_timezone: WITH_TIME ZONE
{ $$ = make_str("with time zone"); }
| WITHOUT TIME ZONE
{ $$ = make_str("without time zone"); }