ALTER TABLE ... SET DATA TYPE (SQL:2008) - Mailing list pgsql-hackers

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.
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;