Skip to content

Commit 1471e38

Browse files
peterepetere
committed
Allow SQL:2008 syntax ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE
alongside our traditional syntax.
1 parent 089ae3b commit 1471e38

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_timestamp SET DATA TYPE 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 @@ F381 Extended schema manipulation YES
236236
F381 Extended schema manipulation 01 ALTER TABLE statement: ALTER COLUMN clause YES
237237
F381 Extended schema manipulation 02 ALTER TABLE statement: ADD CONSTRAINT clause YES
238238
F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAINT clause YES
239-
F382 Alter column data type NO PostgreSQL syntax differs
239+
F382 Alter column data type YES
240240
F391 Long identifiers YES
241241
F392 Unicode escapes in identifiers NO
242242
F393 Unicode escapes in literals NO

‎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
* AUTHOR DATE MAJOR EVENT
@@ -304,7 +304,7 @@ static TypeName *TableFuncTypeName(List *columns);
304304

305305
%type <boolean> copy_from
306306

307-
%type <ival> opt_column event cursor_options opt_hold
307+
%type <ival> opt_column event cursor_options opt_hold opt_set_data
308308
%type <objtype> reindex_type drop_type comment_type
309309

310310
%type <node> fetch_direction select_limit_value select_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_P DATABASE 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-
| ALTER opt_column ColId TYPE_P Typename alter_using
1540+
| ALTER opt_column ColId opt_set_data TYPE_P Typename alter_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
%token SQL_ALLOCATE SQL_AUTOCOMMIT SQL_BOOL SQL_BREAK
394394
SQL_CALL SQL_CARDINALITY SQL_CONNECT
395-
SQL_COUNT SQL_DATA
395+
SQL_COUNT
396396
SQL_DATETIME_INTERVAL_CODE
397397
SQL_DATETIME_INTERVAL_PRECISION SQL_DESCRIBE
398398
SQL_DESCRIPTOR SQL_DISCONNECT SQL_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_P DATABASE 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_elem def_list definition DefineStmt select_with_parens
585585
%type <str> opt_instead event RuleActionList opt_using CreateAssertStmt
586586
%type <str> RuleActionStmtOrEmpty RuleActionMulti func_as reindex_type
587-
%type <str> RuleStmt opt_column oper_argtypes NumConst var_name
587+
%type <str> RuleStmt opt_column opt_set_data oper_argtypes NumConst var_name
588588
%type <str> MathOp RemoveFuncStmt ECPGunreserved_con opt_database_name
589589
%type <str> RemoveAggrStmt opt_procedural select_no_parens CreateCastStmt
590590
%type <str> RemoveOperStmt RenameStmt all_Op opt_trusted opt_lancompiler
@@ -1398,9 +1398,9 @@ alter_table_cmd:
13981398
/* ALTER TABLE <name> DROP [COLUMN] <colname> {RESTRICT|CASCADE} */
13991399
| DROP opt_column ColId opt_drop_behavior
14001400
{ $$ = cat_str(4, make_str("drop"), $2, $3, $4); }
1401-
/* ALTER TABLE <name> ALTER [COLUMN] <colname> TYPE <typename> [ USING <expression> ] */
1402-
| ALTER opt_column ColId TYPE_P Typename alter_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+
| ALTER opt_column ColId opt_set_data TYPE_P Typename alter_using
1403+
{ $$ = cat_str(7, make_str("alter"), $2, $3, $4, make_str("type"), $6, $7); }
14041404
/* ALTER TABLE <name> ADD CONSTRAINT ... */
14051405
| ADD_P TableConstraint
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: SET DATA_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-
#line 33 "parser.pgc"
134+
#line 36 "parser.pgc"
116135

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

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

123142

124143
return 0;

‎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)