ALTER TABLE ... SET DATA TYPE (SQL:2008)
Started by Peter Eisentrautabout 17 years ago1 messages
Here is a patch that allows the new SQL:2008 syntax (also used by IBM)
ALTER TABLE tab ALTER COLUMN col SET DATA TYPE typ
alongside our current syntax
ALTER TABLE tab ALTER COLUMN col TYPE typ
I verified that we implement a superset what the standard says. (Of
course, the standard doesn't suport the USING clause.)
There was some key word overlap with ecpg, so the patch looks bulkier
than it really is.
Attachments:
alter-set-data-type.difftext/plain; name=alter-set-data-type.diff; x-mac-creator=0; x-mac-type=0Download
diff -ur -x CVS ../cvs-pgsql/doc/src/sgml/ref/alter_table.sgml ./doc/src/sgml/ref/alter_table.sgml
--- ../cvs-pgsql/doc/src/sgml/ref/alter_table.sgml 2008-05-10 01:32:03.000000000 +0200
+++ ./doc/src/sgml/ref/alter_table.sgml 2008-10-18 10:30:41.000000000 +0200
@@ -33,7 +33,7 @@
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
- ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
@@ -93,7 +93,7 @@
</varlistentry>
<varlistentry>
- <term><literal>ALTER COLUMN TYPE</literal></term>
+ <term><literal>SET DATA TYPE</literal></term>
<listitem>
<para>
This form changes the type of a column of a table. Indexes and
@@ -760,7 +760,7 @@
with time zone</type> via a <literal>USING</literal> clause:
<programlisting>
ALTER TABLE foo
- ALTER COLUMN foo_timestamp TYPE timestamp with time zone
+ ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
</programlisting>
@@ -868,8 +868,9 @@
<title>Compatibility</title>
<para>
- The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
- forms conform with the SQL standard. The other forms are
+ The forms <literal>ADD</literal>, <literal>DROP</>, <literal>SET DEFAULT</>,
+ and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)
+ conform with the SQL standard. The other forms are
<productname>PostgreSQL</productname> extensions of the SQL standard.
Also, the ability to specify more than one manipulation in a single
<command>ALTER TABLE</> command is an extension.
diff -ur -x CVS ../cvs-pgsql/src/backend/catalog/sql_features.txt ./src/backend/catalog/sql_features.txt
--- ../cvs-pgsql/src/backend/catalog/sql_features.txt 2008-10-18 11:56:26.000000000 +0200
+++ ./src/backend/catalog/sql_features.txt 2008-10-18 10:23:46.000000000 +0200
@@ -236,7 +236,7 @@
F381 Extended schema manipulation 01 ALTER TABLE statement: ALTER COLUMN clause YES
F381 Extended schema manipulation 02 ALTER TABLE statement: ADD CONSTRAINT clause YES
F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAINT clause YES
-F382 Alter column data type NO PostgreSQL syntax differs
+F382 Alter column data type YES
F391 Long identifiers YES
F392 Unicode escapes in identifiers NO
F393 Unicode escapes in literals NO
diff -ur -x CVS ../cvs-pgsql/src/backend/parser/gram.y ./src/backend/parser/gram.y
--- ../cvs-pgsql/src/backend/parser/gram.y 2008-10-15 13:19:39.000000000 +0200
+++ ./src/backend/parser/gram.y 2008-10-18 10:14:05.000000000 +0200
@@ -304,7 +304,7 @@
%type <boolean> copy_from
-%type <ival> opt_column event cursor_options opt_hold
+%type <ival> opt_column event cursor_options opt_hold opt_set_data
%type <objtype> reindex_type drop_type comment_type
%type <node> fetch_direction select_limit_value select_offset_value
@@ -407,7 +407,7 @@
CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
- DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
+ DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
@@ -1534,16 +1534,16 @@
$$ = (Node *)n;
}
/*
- * ALTER TABLE <name> ALTER [COLUMN] <colname> TYPE <typename>
+ * ALTER TABLE <name> ALTER [COLUMN] <colname> [SET DATA] TYPE <typename>
* [ USING <expression> ]
*/
- | ALTER opt_column ColId TYPE_P Typename alter_using
+ | ALTER opt_column ColId opt_set_data TYPE_P Typename alter_using
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AlterColumnType;
n->name = $3;
- n->def = (Node *) $5;
- n->transform = $6;
+ n->def = (Node *) $6;
+ n->transform = $7;
$$ = (Node *)n;
}
/* ALTER TABLE <name> ADD CONSTRAINT ... */
@@ -4854,6 +4854,10 @@
| /*EMPTY*/ { $$ = 0; }
;
+opt_set_data: SET DATA_P { $$ = 1; }
+ | /*EMPTY*/ { $$ = 0; }
+ ;
+
/*****************************************************************************
*
* ALTER THING name SET SCHEMA name
@@ -9317,6 +9321,7 @@
| CURRENT_P
| CURSOR
| CYCLE
+ | DATA_P
| DATABASE
| DAY_P
| DEALLOCATE
diff -ur -x CVS ../cvs-pgsql/src/backend/parser/keywords.c ./src/backend/parser/keywords.c
--- ../cvs-pgsql/src/backend/parser/keywords.c 2008-10-15 13:19:39.000000000 +0200
+++ ./src/backend/parser/keywords.c 2008-10-18 10:08:22.000000000 +0200
@@ -123,6 +123,7 @@
{"current_user", CURRENT_USER, RESERVED_KEYWORD},
{"cursor", CURSOR, UNRESERVED_KEYWORD},
{"cycle", CYCLE, UNRESERVED_KEYWORD},
+ {"data", DATA_P, UNRESERVED_KEYWORD},
{"database", DATABASE, UNRESERVED_KEYWORD},
{"day", DAY_P, UNRESERVED_KEYWORD},
{"deallocate", DEALLOCATE, UNRESERVED_KEYWORD},
diff -ur -x CVS ../cvs-pgsql/src/interfaces/ecpg/preproc/ecpg_keywords.c ./src/interfaces/ecpg/preproc/ecpg_keywords.c
--- ../cvs-pgsql/src/interfaces/ecpg/preproc/ecpg_keywords.c 2008-05-21 01:17:32.000000000 +0200
+++ ./src/interfaces/ecpg/preproc/ecpg_keywords.c 2008-10-18 11:39:06.000000000 +0200
@@ -34,7 +34,6 @@
{"cardinality", SQL_CARDINALITY, 0},
{"connect", SQL_CONNECT, 0},
{"count", SQL_COUNT, 0},
- {"data", SQL_DATA, 0},
{"datetime_interval_code", SQL_DATETIME_INTERVAL_CODE, 0},
{"datetime_interval_precision", SQL_DATETIME_INTERVAL_PRECISION, 0},
{"describe", SQL_DESCRIBE, 0},
diff -ur -x CVS ../cvs-pgsql/src/interfaces/ecpg/preproc/preproc.y ./src/interfaces/ecpg/preproc/preproc.y
--- ../cvs-pgsql/src/interfaces/ecpg/preproc/preproc.y 2008-10-15 13:19:43.000000000 +0200
+++ ./src/interfaces/ecpg/preproc/preproc.y 2008-10-18 11:38:18.000000000 +0200
@@ -392,7 +392,7 @@
/* special embedded SQL token */
%token SQL_ALLOCATE SQL_AUTOCOMMIT SQL_BOOL SQL_BREAK
SQL_CALL SQL_CARDINALITY SQL_CONNECT
- SQL_COUNT SQL_DATA
+ SQL_COUNT
SQL_DATETIME_INTERVAL_CODE
SQL_DATETIME_INTERVAL_PRECISION SQL_DESCRIBE
SQL_DESCRIPTOR SQL_DISCONNECT SQL_FOUND
@@ -431,7 +431,7 @@
CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE
- DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
+ DATA_P DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
@@ -584,7 +584,7 @@
%type <str> def_elem def_list definition DefineStmt select_with_parens
%type <str> opt_instead event RuleActionList opt_using CreateAssertStmt
%type <str> RuleActionStmtOrEmpty RuleActionMulti func_as reindex_type
-%type <str> RuleStmt opt_column oper_argtypes NumConst var_name
+%type <str> RuleStmt opt_column opt_set_data oper_argtypes NumConst var_name
%type <str> MathOp RemoveFuncStmt ECPGunreserved_con opt_database_name
%type <str> RemoveAggrStmt opt_procedural select_no_parens CreateCastStmt
%type <str> RemoveOperStmt RenameStmt all_Op opt_trusted opt_lancompiler
@@ -1398,9 +1398,9 @@
/* ALTER TABLE <name> DROP [COLUMN] <colname> {RESTRICT|CASCADE} */
| DROP opt_column ColId opt_drop_behavior
{ $$ = cat_str(4, make_str("drop"), $2, $3, $4); }
-/* ALTER TABLE <name> ALTER [COLUMN] <colname> TYPE <typename> [ USING <expression> ] */
- | ALTER opt_column ColId TYPE_P Typename alter_using
- { $$ = cat_str(6, make_str("alter"), $2, $3, make_str("type"), $5, $6); }
+/* ALTER TABLE <name> ALTER [COLUMN] <colname> [SET DATA] TYPE <typename> [ USING <expression> ] */
+ | ALTER opt_column ColId opt_set_data TYPE_P Typename alter_using
+ { $$ = cat_str(7, make_str("alter"), $2, $3, $4, make_str("type"), $6, $7); }
/* ALTER TABLE <name> ADD CONSTRAINT ... */
| ADD_P TableConstraint
{ $$ = cat_str(2, make_str("add"), $2); }
@@ -2891,6 +2891,10 @@
| /*EMPTY*/ { $$ = EMPTY; }
;
+opt_set_data: SET DATA_P { $$ = make_str("set data"); }
+ | /*EMPTY*/ { $$ = EMPTY; }
+ ;
+
/*****************************************************************************
*
* ALTER THING name SET SCHEMA name
@@ -6140,7 +6144,7 @@
descriptor_item: SQL_CARDINALITY { $$ = ECPGd_cardinality; }
- | SQL_DATA { $$ = ECPGd_data; }
+ | DATA_P { $$ = ECPGd_data; }
| SQL_DATETIME_INTERVAL_CODE { $$ = ECPGd_di_code; }
| SQL_DATETIME_INTERVAL_PRECISION { $$ = ECPGd_di_precision; }
| SQL_INDICATOR { $$ = ECPGd_indicator; }
@@ -6360,7 +6364,6 @@
| SQL_CALL { $$ = make_str("call"); }
| SQL_CARDINALITY { $$ = make_str("cardinality"); }
| SQL_COUNT { $$ = make_str("count"); }
- | SQL_DATA { $$ = make_str("data"); }
| SQL_DATETIME_INTERVAL_CODE { $$ = make_str("datetime_interval_code"); }
| SQL_DATETIME_INTERVAL_PRECISION { $$ = make_str("datetime_interval_precision"); }
| SQL_FOUND { $$ = make_str("found"); }
@@ -6557,6 +6560,7 @@
| CTYPE { $$ = make_str("ctype"); }
| CURSOR { $$ = make_str("cursor"); }
| CYCLE { $$ = make_str("cycle"); }
+ | DATA_P { $$ = make_str("data"); }
| DATABASE { $$ = make_str("database"); }
/* | DAY_P { $$ = make_str("day"); }*/
| DEALLOCATE { $$ = make_str("deallocate"); }
diff -ur -x CVS ../cvs-pgsql/src/interfaces/ecpg/test/expected/sql-parser.c ./src/interfaces/ecpg/test/expected/sql-parser.c
--- ../cvs-pgsql/src/interfaces/ecpg/test/expected/sql-parser.c 2008-02-17 19:14:29.000000000 +0100
+++ ./src/interfaces/ecpg/test/expected/sql-parser.c 2008-10-18 11:53:54.000000000 +0200
@@ -101,7 +101,7 @@
for (i=0; i<3; i++)
printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);
- { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table T ", ECPGt_EOIT, ECPGt_EORT);
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "alter table T alter Item1 type bigint ", ECPGt_EOIT, ECPGt_EORT);
#line 31 "parser.pgc"
if (sqlca.sqlwarn[0] == 'W') sqlprint();
@@ -110,15 +110,34 @@
if (sqlca.sqlcode < 0) sqlprint();}
#line 31 "parser.pgc"
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "alter table T alter column Item2 set data type smallint ", ECPGt_EOIT, ECPGt_EORT);
+#line 32 "parser.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 32 "parser.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "parser.pgc"
+
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table T ", ECPGt_EOIT, ECPGt_EORT);
+#line 34 "parser.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 34 "parser.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 34 "parser.pgc"
+
{ ECPGdisconnect(__LINE__, "ALL");
-#line 33 "parser.pgc"
+#line 36 "parser.pgc"
if (sqlca.sqlwarn[0] == 'W') sqlprint();
-#line 33 "parser.pgc"
+#line 36 "parser.pgc"
if (sqlca.sqlcode < 0) sqlprint();}
-#line 33 "parser.pgc"
+#line 36 "parser.pgc"
return 0;
diff -ur -x CVS ../cvs-pgsql/src/interfaces/ecpg/test/expected/sql-parser.stderr ./src/interfaces/ecpg/test/expected/sql-parser.stderr
--- ../cvs-pgsql/src/interfaces/ecpg/test/expected/sql-parser.stderr 2008-05-16 17:20:04.000000000 +0200
+++ ./src/interfaces/ecpg/test/expected/sql-parser.stderr 2008-10-18 11:54:31.000000000 +0200
@@ -40,11 +40,23 @@
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_get_data on line 26: RESULT: offset: -1; array: yes
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_execute on line 31: query: drop table T ; with 0 parameter(s) on connection regress1
+[NO_PID]: ecpg_execute on line 31: query: alter table T alter Item1 type bigint ; with 0 parameter(s) on connection regress1
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_execute on line 31: using PQexec
[NO_PID]: sqlca: code: 0, state: 00000
-[NO_PID]: ecpg_execute on line 31: OK: DROP TABLE
+[NO_PID]: ecpg_execute on line 31: OK: ALTER TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[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
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: OK: ALTER TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 34: query: drop table T ; with 0 parameter(s) on connection regress1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 34: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 34: OK: DROP TABLE
[NO_PID]: sqlca: code: 0, state: 00000
[NO_PID]: ecpg_finish: connection regress1 closed
[NO_PID]: sqlca: code: 0, state: 00000
diff -ur -x CVS ../cvs-pgsql/src/interfaces/ecpg/test/sql/parser.pgc ./src/interfaces/ecpg/test/sql/parser.pgc
--- ../cvs-pgsql/src/interfaces/ecpg/test/sql/parser.pgc 2007-03-17 20:25:24.000000000 +0100
+++ ./src/interfaces/ecpg/test/sql/parser.pgc 2008-10-18 11:33:39.000000000 +0200
@@ -28,6 +28,9 @@
for (i=0; i<3; i++)
printf("item[%d] = %d\n", i, ind[i] ? -1 : item[i]);
+ EXEC SQL ALTER TABLE T ALTER Item1 TYPE bigint;
+ EXEC SQL ALTER TABLE T ALTER COLUMN Item2 SET DATA TYPE smallint;
+
EXEC SQL DROP TABLE T;
EXEC SQL DISCONNECT ALL;