hi.
ATPrepAlterColumnType forbids us to ALTER COLUMN SET DATA TYPE USING (expr)
for generated columns.
however we can still change the generated column type from non-text to text
or text type from one collation to another collation.
In ATExecAlterColumnType, we also need to set the generation
expression collation?
We can do this by adding exprSetCollation:
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14115,6 +14115,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab,
Relation rel,
errmsg("default for
column \"%s\" cannot be cast automatically to type %s",
colName, format_type_be(targettype))));
}
+ exprSetCollation(defaultexpr, targetcollid);
---------------------
CREATE TABLE x1(a int,
b int GENERATED ALWAYS AS (a * 2) stored,
c text GENERATED ALWAYS AS ('1') stored );
ALTER TABLE x1 alter column b set data type text collate "C";
ALTER TABLE x1 alter column c set data type text collate "C";
SELECT pg_get_expr(d.adbin, d.adrelid) AS default_value, d.adbin
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum)
AND a.attrelid = 'x1'::regclass
AND a.attname in ('b', 'c');
by adding exprSetCollation, the output is
default_value | (a * 2)
adbin | {COERCEVIAIO :arg {OPEXPR :opno 514 :opfuncid 141
:opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args
({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0
:varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1
:varattnosyn 1 :location -1} {CONST :consttype 23 :consttypmod -1
:constcollid 0 :constlen 4 :constbyval true :constisnull false
:location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location -1}
:resulttype 25 :resultcollid 950 :coerceformat 2 :location -1}
-[ RECORD 2
]-+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
default_value | '1'::text COLLATE "C"
adbin | {CONST :consttype 25 :consttypmod -1 :constcollid 950
:constlen -1 :constbyval false :constisnull false :location -1
:constvalue 5 [ 20 0 0 0 49 ]}
master behavior:
default_value | (a * 2)
adbin | {COERCEVIAIO :arg {OPEXPR :opno 514 :opfuncid 141
:opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args
({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0
:varnullingrels (b) :varlevelsup 0 :varreturningtype 0 :varnosyn 1
:varattnosyn 1 :location -1} {CONST :consttype 23 :consttypmod -1
:constcollid 0 :constlen 4 :constbyval true :constisnull false
:location -1 :constvalue 4 [ 2 0 0 0 0 0 0 0 ]}) :location -1}
:resulttype 25 :resultcollid 0 :coerceformat 2 :location -1}
-[ RECORD 2
]-+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
default_value | '1'::text
adbin | {CONST :consttype 25 :consttypmod -1 :constcollid 100
:constlen -1 :constbyval false :constisnull false :location -1
:constvalue 5 [ 20 0 0 0 49 ]}
