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

Commit8ecd535

Browse files
committed
Add WITH [NO] DATA clause to CREATE TABLE AS, per SQL.
Also, since WITH is now a reserved word, simplify the token merging code toonly deal with WITH_TIME.by Tom Lane and myself
1 parent53a5026 commit8ecd535

File tree

6 files changed

+49
-65
lines changed

6 files changed

+49
-65
lines changed

‎doc/src/sgml/ref/create_table_as.sgml‎

Lines changed: 17 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.37 2007/06/03 17:06:12 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.38 2008/10/28 14:09:44 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -26,6 +26,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
2626
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
2727
[ TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
2828
AS <replaceable>query</replaceable>
29+
[ WITH [ NO ] DATA ]
2930
</synopsis>
3031
</refsynopsisdiv>
3132

@@ -201,6 +202,18 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
201202
</para>
202203
</listitem>
203204
</varlistentry>
205+
206+
<varlistentry>
207+
<term><literal>WITH [ NO ] DATA</></term>
208+
<listitem>
209+
<para>
210+
This clause specifies whether or not the data produced by the query
211+
should be copied into the new table. If not, only the table structure
212+
is copied. The default is to copy the data.
213+
</para>
214+
</listitem>
215+
</varlistentry>
216+
204217
</variablelist>
205218
</refsect1>
206219

@@ -265,7 +278,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
265278

266279
<para>
267280
<command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym>
268-
standard, with the followingexceptions:
281+
standard. The followingare nonstandard extensions:
269282

270283
<itemizedlist spacing="compact">
271284
<listitem>
@@ -278,12 +291,8 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
278291

279292
<listitem>
280293
<para>
281-
The standard defines a <literal>WITH [ NO ] DATA</literal> clause;
282-
this is not currently implemented by <productname>PostgreSQL</>.
283-
The behavior provided by <productname>PostgreSQL</> is equivalent
284-
to the standard's <literal>WITH DATA</literal> case.
285-
<literal>WITH NO DATA</literal> can be simulated by appending
286-
<literal>LIMIT 0</> to the query.
294+
In the standard, the <literal>WITH [ NO ] DATA</literal> clause
295+
is required; in PostgreSQL it is optional.
287296
</para>
288297
</listitem>
289298

‎src/backend/catalog/sql_features.txt‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -408,7 +408,7 @@ T141SIMILAR predicateYES
408408
T151DISTINCT predicateYES
409409
T152DISTINCT predicate with negationYES
410410
T171LIKE clause in table definitionYES
411-
T172AS subquery clause in table definitionNO
411+
T172AS subquery clause in table definitionYES
412412
T173Extended LIKE clause in table definitionYES
413413
T174Identity columnsNO
414414
T175Generated columnsNO

‎src/backend/parser/gram.y‎

Lines changed: 17 additions & 12 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.630 2008/10/2709:37:47 petere Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.631 2008/10/28 14:09:45 petere Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -216,7 +216,7 @@ static TypeName *TableFuncTypeName(List *columns);
216216
%type<ival>opt_locklock_typecast_context
217217
%type<boolean>opt_forceopt_or_replace
218218
opt_grant_grant_optionopt_grant_admin_option
219-
opt_nowaitopt_if_exists
219+
opt_nowaitopt_if_existsopt_with_data
220220

221221
%type<list>OptRoleList
222222
%type<defelt>OptRoleElem
@@ -485,7 +485,7 @@ static TypeName *TableFuncTypeName(List *columns);
485485
* list and so can never be entered directly. The filter in parser.c
486486
* creates these tokens when required.
487487
*/
488-
%tokenNULLS_FIRSTNULLS_LASTWITH_CASCADEDWITH_LOCALWITH_CHECK
488+
%tokenNULLS_FIRSTNULLS_LASTWITH_TIME
489489

490490
/* Special token types, not actually keywords - see the "lex" file*/
491491
%token<str>IDENTFCONSTSCONSTBCONSTXCONSTOp
@@ -2416,7 +2416,7 @@ OptConsTableSpace: USING INDEX TABLESPACE name{ $$ = $4; }
24162416
*/
24172417

24182418
CreateAsStmt:
2419-
CREATEOptTempTABLEcreate_as_targetASSelectStmt
2419+
CREATEOptTempTABLEcreate_as_targetASSelectStmtopt_with_data
24202420
{
24212421
/*
24222422
* When the SelectStmt is a set-operation tree, we must
@@ -2433,6 +2433,9 @@ CreateAsStmt:
24332433
scanner_errposition(exprLocation((Node *) n->intoClause))));
24342434
$4->rel->istemp =$2;
24352435
n->intoClause =$4;
2436+
/* Implement WITH NO DATA by forcing top-level LIMIT 0*/
2437+
if (!$7)
2438+
((SelectStmt *)$6)->limitCount = makeIntConst(0, -1);
24362439
$$ =$6;
24372440
}
24382441
;
@@ -2475,6 +2478,12 @@ CreateAsElement:
24752478
}
24762479
;
24772480

2481+
opt_with_data:
2482+
WITHDATA_P{$$ =TRUE; }
2483+
|WITHNODATA_P{$$ =FALSE; }
2484+
|/*EMPTY*/{$$ =TRUE; }
2485+
;
2486+
24782487

24792488
/*****************************************************************************
24802489
*
@@ -5387,24 +5396,20 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list
53875396
}
53885397
;
53895398

5390-
/*
5391-
* We use merged tokens here to avoid creating shift/reduce conflicts against
5392-
* a whole lot of other uses of WITH.
5393-
*/
53945399
opt_check_option:
5395-
WITH_CHECK OPTION
5400+
WITH CHECK OPTION
53965401
{
53975402
ereport(ERROR,
53985403
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
53995404
errmsg("WITH CHECK OPTION is not implemented")));
54005405
}
5401-
|WITH_CASCADED CHECK OPTION
5406+
|WITH CASCADED CHECK OPTION
54025407
{
54035408
ereport(ERROR,
54045409
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
54055410
errmsg("WITH CHECK OPTION is not implemented")));
54065411
}
5407-
|WITH_LOCAL CHECK OPTION
5412+
|WITH LOCAL CHECK OPTION
54085413
{
54095414
ereport(ERROR,
54105415
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -7509,7 +7514,7 @@ ConstInterval:
75097514
;
75107515

75117516
opt_timezone:
7512-
WITH TIME ZONE{$$ =TRUE; }
7517+
WITH_TIME ZONE{$$ =TRUE; }
75137518
| WITHOUT TIME ZONE{$$ =FALSE; }
75147519
|/*EMPTY*/{$$ =FALSE; }
75157520
;

‎src/backend/parser/parser.c‎

Lines changed: 4 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Portions Copyright (c) 1994, Regents of the University of California
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.74 2008/08/29 13:02:32 petere Exp $
17+
* $PostgreSQL: pgsql/src/backend/parser/parser.c,v 1.75 2008/10/28 14:09:45 petere Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -129,28 +129,15 @@ filtered_base_yylex(void)
129129
caseWITH:
130130

131131
/*
132-
* WITH CASCADED, LOCAL, or CHECK must be reduced to one token
133-
*
134-
* XXX an alternative way is to recognize just WITH_TIME and put
135-
* the ugliness into the datetime datatype productions instead of
136-
* WITH CHECK OPTION. However that requires promoting WITH to a
137-
* fully reserved word. If we ever have to do that anyway
138-
* (perhaps for SQL99 recursive queries), come back and simplify
139-
* this code.
132+
* WITH TIME must be reduced to one token
140133
*/
141134
cur_yylval=base_yylval;
142135
cur_yylloc=base_yylloc;
143136
next_token=base_yylex();
144137
switch (next_token)
145138
{
146-
caseCASCADED:
147-
cur_token=WITH_CASCADED;
148-
break;
149-
caseLOCAL:
150-
cur_token=WITH_LOCAL;
151-
break;
152-
caseCHECK:
153-
cur_token=WITH_CHECK;
139+
caseTIME:
140+
cur_token=WITH_TIME;
154141
break;
155142
default:
156143
/* save the lookahead token for next time */

‎src/interfaces/ecpg/preproc/parser.c‎

Lines changed: 4 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
* Portions Copyright (c) 1994, Regents of the University of California
1515
*
1616
* IDENTIFICATION
17-
* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/parser.c,v 1.3 2008/01/01 19:45:59 momjian Exp $
17+
* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/parser.c,v 1.4 2008/10/28 14:09:45 petere Exp $
1818
*
1919
*-------------------------------------------------------------------------
2020
*/
@@ -98,28 +98,15 @@ filtered_base_yylex(void)
9898
caseWITH:
9999

100100
/*
101-
* WITH CASCADED, LOCAL, or CHECK must be reduced to one token
102-
*
103-
* XXX an alternative way is to recognize just WITH_TIME and put
104-
* the ugliness into the datetime datatype productions instead of
105-
* WITH CHECK OPTION. However that requires promoting WITH to a
106-
* fully reserved word. If we ever have to do that anyway
107-
* (perhaps for SQL99 recursive queries), come back and simplify
108-
* this code.
101+
* WITH TIME must be reduced to one token
109102
*/
110103
cur_yylval=base_yylval;
111104
cur_yylloc=base_yylloc;
112105
next_token=base_yylex();
113106
switch (next_token)
114107
{
115-
caseCASCADED:
116-
cur_token=WITH_CASCADED;
117-
break;
118-
caseLOCAL:
119-
cur_token=WITH_LOCAL;
120-
break;
121-
caseCHECK:
122-
cur_token=WITH_CHECK;
108+
caseTIME:
109+
cur_token=WITH_TIME;
123110
break;
124111
default:
125112
/* save the lookahead token for next time */

‎src/interfaces/ecpg/preproc/preproc.y‎

Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.378 2008/10/2709:37:47 petere Exp $*/
1+
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.379 2008/10/28 14:09:45 petere Exp $*/
22

33
/* Copyright comment*/
44
%{
@@ -505,7 +505,7 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
505505
* list and so can never be entered directly. The filter in parser.c
506506
* creates these tokens when required.
507507
*/
508-
%tokenNULLS_FIRSTNULLS_LASTWITH_CASCADEDWITH_LOCALWITH_CHECK
508+
%tokenNULLS_FIRSTNULLS_LASTWITH_TIME
509509

510510
/* Special token types, not actually keywords - see the "lex" file*/
511511
%token<str>IDENTSCONSTOpCSTRINGCVARIABLECPP_LINEIPBCONST
@@ -3100,22 +3100,18 @@ ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list AS SelectStmt opt_
31003100
{$$ = cat_str(8, make_str("create or replace"),$4, make_str("view"),$6,$7, make_str("as"),$9,$10); }
31013101
;
31023102

3103-
/*
3104-
* We use merged tokens here to avoid creating shift/reduce conflicts against
3105-
* a whole lot of other uses of WITH.
3106-
*/
31073103
opt_check_option:
3108-
WITH_CHECKOPTION
3104+
WITHCHECKOPTION
31093105
{
31103106
mmerror(PARSE_ERROR, ET_ERROR,"WITH CHECK OPTION not implemented");
31113107
$$ = EMPTY;
31123108
}
3113-
|WITH_CASCADEDCHECKOPTION
3109+
|WITHCASCADEDCHECKOPTION
31143110
{
31153111
mmerror(PARSE_ERROR, ET_ERROR,"WITH CHECK OPTION not implemented");
31163112
$$ = EMPTY;
31173113
}
3118-
|WITH_LOCALCHECKOPTION
3114+
|WITHLOCALCHECKOPTION
31193115
{
31203116
mmerror(PARSE_ERROR, ET_ERROR,"WITH CHECK OPTION not implemented");
31213117
$$ = EMPTY;
@@ -4155,7 +4151,7 @@ ConstInterval:INTERVAL
41554151
{$$ = make_str("interval"); }
41564152
;
41574153

4158-
opt_timezone:WITHTIMEZONE
4154+
opt_timezone:WITH_TIMEZONE
41594155
{$$ = make_str("with time zone"); }
41604156
|WITHOUTTIMEZONE
41614157
{$$ = make_str("without time zone"); }

0 commit comments

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