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

Commit1471e38

Browse files
committed
Allow SQL:2008 syntax ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE
alongside our traditional syntax.
1 parent089ae3b commit1471e38

File tree

9 files changed

+76
-32
lines changed

9 files changed

+76
-32
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.99 2008/05/09 23:32:03 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.100 2008/10/21 08:38:15 petere Exp $
33
PostgreSQL documentation
44
-->
55

@@ -33,7 +33,7 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
3333

3434
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
3535
DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
36-
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
36+
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable>[ SET DATA ]TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
3737
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
3838
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
3939
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
@@ -93,7 +93,7 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
9393
</varlistentry>
9494

9595
<varlistentry>
96-
<term><literal>ALTER COLUMN TYPE</literal></term>
96+
<term><literal>SET DATA TYPE</literal></term>
9797
<listitem>
9898
<para>
9999
This form changes the type of a column of a table. Indexes and
@@ -760,7 +760,7 @@ ALTER TABLE distributors
760760
with time zone</type> via a <literal>USING</literal> clause:
761761
<programlisting>
762762
ALTER TABLE foo
763-
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
763+
ALTER COLUMN foo_timestampSET DATATYPE timestamp with time zone
764764
USING
765765
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
766766
</programlisting>
@@ -868,8 +868,9 @@ ALTER TABLE myschema.distributors SET SCHEMA yourschema;
868868
<title>Compatibility</title>
869869

870870
<para>
871-
The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
872-
forms conform with the SQL standard. The other forms are
871+
The forms <literal>ADD</literal>, <literal>DROP</>, <literal>SET DEFAULT</>,
872+
and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)
873+
conform with the SQL standard. The other forms are
873874
<productname>PostgreSQL</productname> extensions of the SQL standard.
874875
Also, the ability to specify more than one manipulation in a single
875876
<command>ALTER TABLE</> command is an extension.

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -236,7 +236,7 @@ F381Extended schema manipulationYES
236236
F381Extended schema manipulation01ALTER TABLE statement: ALTER COLUMN clauseYES
237237
F381Extended schema manipulation02ALTER TABLE statement: ADD CONSTRAINT clauseYES
238238
F381Extended schema manipulation03ALTER TABLE statement: DROP CONSTRAINT clauseYES
239-
F382Alter column data typeNOPostgreSQL syntax differs
239+
F382Alter column data typeYES
240240
F391Long identifiersYES
241241
F392Unicode escapes in identifiersNO
242242
F393Unicode escapes in literalsNO

‎src/backend/parser/gram.y

Lines changed: 12 additions & 7 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.626 2008/10/20 14:26:28 petere Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.627 2008/10/21 08:38:15 petere Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -304,7 +304,7 @@ static TypeName *TableFuncTypeName(List *columns);
304304

305305
%type<boolean>copy_from
306306

307-
%type<ival>opt_columneventcursor_optionsopt_hold
307+
%type<ival>opt_columneventcursor_optionsopt_holdopt_set_data
308308
%type<objtype>reindex_typedrop_typecomment_type
309309

310310
%type<node>fetch_directionselect_limit_valueselect_offset_value
@@ -407,7 +407,7 @@ static TypeName *TableFuncTypeName(List *columns);
407407
CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
408408
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
409409

410-
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
410+
DATA_PDATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
411411
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
412412
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
413413

@@ -1534,16 +1534,16 @@ alter_table_cmd:
15341534
$$ = (Node *)n;
15351535
}
15361536
/*
1537-
* ALTER TABLE <name> ALTER [COLUMN] <colname> TYPE <typename>
1537+
* ALTER TABLE <name> ALTER [COLUMN] <colname>[SET DATA]TYPE <typename>
15381538
*[ USING <expression> ]
15391539
*/
1540-
|ALTERopt_columnColIdTYPE_PTypenamealter_using
1540+
|ALTERopt_columnColIdopt_set_dataTYPE_PTypenamealter_using
15411541
{
15421542
AlterTableCmd *n = makeNode(AlterTableCmd);
15431543
n->subtype = AT_AlterColumnType;
15441544
n->name =$3;
1545-
n->def = (Node *)$5;
1546-
n->transform =$6;
1545+
n->def = (Node *)$6;
1546+
n->transform =$7;
15471547
$$ = (Node *)n;
15481548
}
15491549
/* ALTER TABLE <name> ADD CONSTRAINT ...*/
@@ -4854,6 +4854,10 @@ opt_column: COLUMN{ $$ = COLUMN; }
48544854
|/*EMPTY*/{$$ =0; }
48554855
;
48564856

4857+
opt_set_data: SET DATA_P{$$ =1; }
4858+
|/*EMPTY*/{$$ =0; }
4859+
;
4860+
48574861
/*****************************************************************************
48584862
*
48594863
* ALTER THING name SET SCHEMA name
@@ -9317,6 +9321,7 @@ unreserved_keyword:
93179321
| CURRENT_P
93189322
| CURSOR
93199323
| CYCLE
9324+
| DATA_P
93209325
| DATABASE
93219326
| DAY_P
93229327
| DEALLOCATE

‎src/backend/parser/keywords.c

Lines changed: 2 additions & 1 deletion
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.202 2008/10/04 21:56:54 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.203 2008/10/21 08:38:15 petere Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -123,6 +123,7 @@ const ScanKeyword ScanKeywords[] = {
123123
{"current_user",CURRENT_USER,RESERVED_KEYWORD},
124124
{"cursor",CURSOR,UNRESERVED_KEYWORD},
125125
{"cycle",CYCLE,UNRESERVED_KEYWORD},
126+
{"data",DATA_P,UNRESERVED_KEYWORD},
126127
{"database",DATABASE,UNRESERVED_KEYWORD},
127128
{"day",DAY_P,UNRESERVED_KEYWORD},
128129
{"deallocate",DEALLOCATE,UNRESERVED_KEYWORD},

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

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* lexical token lookup for reserved words in postgres embedded SQL
55
*
66
* IDENTIFICATION
7-
* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/ecpg_keywords.c,v 1.38 2008/05/20 23:17:32 meskes Exp $
7+
* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/ecpg_keywords.c,v 1.39 2008/10/21 08:38:16 petere Exp $
88
*
99
*-------------------------------------------------------------------------
1010
*/
@@ -34,7 +34,6 @@ static const ScanKeyword ScanECPGKeywords[] = {
3434
{"cardinality",SQL_CARDINALITY,0},
3535
{"connect",SQL_CONNECT,0},
3636
{"count",SQL_COUNT,0},
37-
{"data",SQL_DATA,0},
3837
{"datetime_interval_code",SQL_DATETIME_INTERVAL_CODE,0},
3938
{"datetime_interval_precision",SQL_DATETIME_INTERVAL_PRECISION,0},
4039
{"describe",SQL_DESCRIBE,0},

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

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.376 2008/10/14 09:31:04 meskes Exp $*/
1+
/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.377 2008/10/21 08:38:16 petere Exp $*/
22

33
/* Copyright comment*/
44
%{
@@ -392,7 +392,7 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
392392
/* special embedded SQL token*/
393393
%tokenSQL_ALLOCATESQL_AUTOCOMMITSQL_BOOLSQL_BREAK
394394
SQL_CALLSQL_CARDINALITYSQL_CONNECT
395-
SQL_COUNTSQL_DATA
395+
SQL_COUNT
396396
SQL_DATETIME_INTERVAL_CODE
397397
SQL_DATETIME_INTERVAL_PRECISIONSQL_DESCRIBE
398398
SQL_DESCRIPTORSQL_DISCONNECTSQL_FOUND
@@ -431,7 +431,7 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
431431
CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
432432
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
433433

434-
DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
434+
DATA_PDATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
435435
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
436436
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
437437

@@ -584,7 +584,7 @@ add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
584584
%type<str>def_elemdef_listdefinitionDefineStmtselect_with_parens
585585
%type<str>opt_insteadeventRuleActionListopt_usingCreateAssertStmt
586586
%type<str>RuleActionStmtOrEmptyRuleActionMultifunc_asreindex_type
587-
%type<str>RuleStmtopt_columnoper_argtypesNumConstvar_name
587+
%type<str>RuleStmtopt_columnopt_set_dataoper_argtypesNumConstvar_name
588588
%type<str>MathOpRemoveFuncStmtECPGunreserved_conopt_database_name
589589
%type<str>RemoveAggrStmtopt_proceduralselect_no_parensCreateCastStmt
590590
%type<str>RemoveOperStmtRenameStmtall_Opopt_trustedopt_lancompiler
@@ -1398,9 +1398,9 @@ alter_table_cmd:
13981398
/* ALTER TABLE <name> DROP [COLUMN] <colname> {RESTRICT|CASCADE}*/
13991399
|DROPopt_columnColIdopt_drop_behavior
14001400
{$$ = cat_str(4, make_str("drop"),$2,$3,$4); }
1401-
/* ALTER TABLE <name> ALTER [COLUMN] <colname> TYPE <typename> [ USING <expression> ]*/
1402-
|ALTERopt_columnColIdTYPE_PTypenamealter_using
1403-
{$$ = cat_str(6, make_str("alter"),$2,$3, make_str("type"),$5,$6); }
1401+
/* ALTER TABLE <name> ALTER [COLUMN] <colname>[SET DATA]TYPE <typename> [ USING <expression> ]*/
1402+
|ALTERopt_columnColIdopt_set_dataTYPE_PTypenamealter_using
1403+
{$$ = cat_str(7, make_str("alter"),$2,$3,$4,make_str("type"),$6,$7); }
14041404
/* ALTER TABLE <name> ADD CONSTRAINT ...*/
14051405
|ADD_PTableConstraint
14061406
{$$ = cat_str(2, make_str("add"),$2); }
@@ -2891,6 +2891,10 @@ opt_column: COLUMN{ $$ = make_str("column"); }
28912891
|/*EMPTY*/{$$ = EMPTY; }
28922892
;
28932893

2894+
opt_set_data:SETDATA_P{$$ = make_str("set data"); }
2895+
|/*EMPTY*/{$$ = EMPTY; }
2896+
;
2897+
28942898
/*****************************************************************************
28952899
*
28962900
* ALTER THING name SET SCHEMA name
@@ -6140,7 +6144,7 @@ ECPGSetDescItem: descriptor_item '=' AllConstVar
61406144

61416145

61426146
descriptor_item:SQL_CARDINALITY{$$ = ECPGd_cardinality; }
6143-
|SQL_DATA{$$ = ECPGd_data; }
6147+
|DATA_P{$$ = ECPGd_data; }
61446148
|SQL_DATETIME_INTERVAL_CODE{$$ = ECPGd_di_code; }
61456149
|SQL_DATETIME_INTERVAL_PRECISION {$$ = ECPGd_di_precision; }
61466150
|SQL_INDICATOR{$$ = ECPGd_indicator; }
@@ -6360,7 +6364,6 @@ ECPGKeywords_vanames: SQL_BREAK{ $$ = make_str("break"); }
63606364
|SQL_CALL{$$ = make_str("call"); }
63616365
|SQL_CARDINALITY{$$ = make_str("cardinality"); }
63626366
|SQL_COUNT{$$ = make_str("count"); }
6363-
|SQL_DATA{$$ = make_str("data"); }
63646367
|SQL_DATETIME_INTERVAL_CODE{$$ = make_str("datetime_interval_code"); }
63656368
|SQL_DATETIME_INTERVAL_PRECISION{$$ = make_str("datetime_interval_precision"); }
63666369
|SQL_FOUND{$$ = make_str("found"); }
@@ -6557,6 +6560,7 @@ ECPGunreserved_con: ABORT_P{ $$ = make_str("abort"); }
65576560
|CTYPE{$$ = make_str("ctype"); }
65586561
|CURSOR{$$ = make_str("cursor"); }
65596562
|CYCLE{$$ = make_str("cycle"); }
6563+
|DATA_P{$$ = make_str("data"); }
65606564
|DATABASE{$$ = make_str("database"); }
65616565
/*| DAY_P{ $$ = make_str("day"); }*/
65626566
|DEALLOCATE{$$ = make_str("deallocate"); }

‎src/interfaces/ecpg/test/expected/sql-parser.c

Lines changed: 23 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -101,7 +101,7 @@ if (sqlca.sqlcode < 0) sqlprint();}
101101
for (i=0;i<3;i++)
102102
printf("item[%d] = %d\n",i,ind[i] ?-1 :item[i]);
103103

104-
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"drop table T ",ECPGt_EOIT,ECPGt_EORT);
104+
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"alter table T alter Item1 type bigint ",ECPGt_EOIT,ECPGt_EORT);
105105
#line 31 "parser.pgc"
106106

107107
if (sqlca.sqlwarn[0]=='W')sqlprint();
@@ -110,15 +110,34 @@ if (sqlca.sqlwarn[0] == 'W') sqlprint();
110110
if (sqlca.sqlcode<0)sqlprint();}
111111
#line 31 "parser.pgc"
112112

113+
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"alter table T alter column Item2 set data type smallint ",ECPGt_EOIT,ECPGt_EORT);
114+
#line 32 "parser.pgc"
115+
116+
if (sqlca.sqlwarn[0]=='W')sqlprint();
117+
#line 32 "parser.pgc"
118+
119+
if (sqlca.sqlcode<0)sqlprint();}
120+
#line 32 "parser.pgc"
121+
122+
123+
{ECPGdo(__LINE__,0,1,NULL,0,ECPGst_normal,"drop table T ",ECPGt_EOIT,ECPGt_EORT);
124+
#line 34 "parser.pgc"
125+
126+
if (sqlca.sqlwarn[0]=='W')sqlprint();
127+
#line 34 "parser.pgc"
128+
129+
if (sqlca.sqlcode<0)sqlprint();}
130+
#line 34 "parser.pgc"
131+
113132

114133
{ECPGdisconnect(__LINE__,"ALL");
115-
#line33 "parser.pgc"
134+
#line36 "parser.pgc"
116135

117136
if (sqlca.sqlwarn[0]=='W')sqlprint();
118-
#line33 "parser.pgc"
137+
#line36 "parser.pgc"
119138

120139
if (sqlca.sqlcode<0)sqlprint();}
121-
#line33 "parser.pgc"
140+
#line36 "parser.pgc"
122141

123142

124143
return0;

‎src/interfaces/ecpg/test/expected/sql-parser.stderr

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -40,11 +40,23 @@
4040
[NO_PID]: sqlca: code: 0, state: 00000
4141
[NO_PID]: ecpg_get_data on line 26: RESULT: offset: -1; array: yes
4242
[NO_PID]: sqlca: code: 0, state: 00000
43-
[NO_PID]: ecpg_execute on line 31: query:drop table T ; with 0 parameter(s) on connection regress1
43+
[NO_PID]: ecpg_execute on line 31: query:alter table T alter Item1 type bigint ; with 0 parameter(s) on connection regress1
4444
[NO_PID]: sqlca: code: 0, state: 00000
4545
[NO_PID]: ecpg_execute on line 31: using PQexec
4646
[NO_PID]: sqlca: code: 0, state: 00000
47-
[NO_PID]: ecpg_execute on line 31: OK: DROP TABLE
47+
[NO_PID]: ecpg_execute on line 31: OK: ALTER TABLE
48+
[NO_PID]: sqlca: code: 0, state: 00000
49+
[NO_PID]: ecpg_execute on line 32: query: alter table T alter column Item2 set data type smallint ; with 0 parameter(s) on connection regress1
50+
[NO_PID]: sqlca: code: 0, state: 00000
51+
[NO_PID]: ecpg_execute on line 32: using PQexec
52+
[NO_PID]: sqlca: code: 0, state: 00000
53+
[NO_PID]: ecpg_execute on line 32: OK: ALTER TABLE
54+
[NO_PID]: sqlca: code: 0, state: 00000
55+
[NO_PID]: ecpg_execute on line 34: query: drop table T ; with 0 parameter(s) on connection regress1
56+
[NO_PID]: sqlca: code: 0, state: 00000
57+
[NO_PID]: ecpg_execute on line 34: using PQexec
58+
[NO_PID]: sqlca: code: 0, state: 00000
59+
[NO_PID]: ecpg_execute on line 34: OK: DROP TABLE
4860
[NO_PID]: sqlca: code: 0, state: 00000
4961
[NO_PID]: ecpg_finish: connection regress1 closed
5062
[NO_PID]: sqlca: code: 0, state: 00000

‎src/interfaces/ecpg/test/sql/parser.pgc

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,9 @@ int main(int argc, char* argv[]) {
2828
for (i=0; i<3; i++)
2929
printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);
3030

31+
EXEC SQL ALTER TABLE T ALTER Item1 TYPE bigint;
32+
EXEC SQL ALTER TABLE T ALTER COLUMN Item2 SET DATA TYPE smallint;
33+
3134
EXEC SQL DROP TABLE T;
3235

3336
EXEC SQL DISCONNECT ALL;

0 commit comments

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