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

Commitf595117

Browse files
committed
ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
Add an ALTER TABLE subcommand for dropping the generated property froma column, per SQL standard.Reviewed-by: Sergei Kornilov <sk@zsrv.org>Discussion:https://www.postgresql.org/message-id/flat/2f7f1d9c-946e-0453-d841-4f38eb9d69b6%402ndquadrant.com
1 parentd751ba5 commitf595117

File tree

9 files changed

+326
-4
lines changed

9 files changed

+326
-4
lines changed

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
4646
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable>
4747
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT
4848
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL
49+
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ]
4950
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]
5051
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]
5152
ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP IDENTITY [ IF EXISTS ]
@@ -241,6 +242,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
241242
</listitem>
242243
</varlistentry>
243244

245+
<varlistentry>
246+
<term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term>
247+
<listitem>
248+
<para>
249+
This form turns a stored generated column into a normal base column.
250+
Existing data in the columns is retained, but future changes will no
251+
longer apply the generation expression.
252+
</para>
253+
254+
<para>
255+
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
256+
column is not a stored generated column, no error is thrown. In this
257+
case a notice is issued instead.
258+
</para>
259+
</listitem>
260+
</varlistentry>
261+
244262
<varlistentry>
245263
<term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>
246264
<term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -252,7 +252,7 @@ F381Extended schema manipulation03ALTER TABLE statement: DROP CONSTRAINT clau
252252
F382Alter column data typeYES
253253
F383Set column not null clauseYES
254254
F384Drop identity property clauseYES
255-
F385Drop column generation expression clauseNO
255+
F385Drop column generation expression clauseYES
256256
F386Set identity column generation clauseYES
257257
F391Long identifiersYES
258258
F392Unicode escapes in identifiersYES

‎src/backend/commands/tablecmds.c

Lines changed: 160 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -388,6 +388,8 @@ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName,
388388
static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,
389389
Node *def, LOCKMODE lockmode);
390390
static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
391+
static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recursing);
392+
static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode);
391393
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum,
392394
Node *newValue, LOCKMODE lockmode);
393395
static ObjectAddress ATExecSetOptions(Relation rel, const char *colName,
@@ -3672,6 +3674,7 @@ AlterTableGetLockLevel(List *cmds)
36723674
case AT_AddIdentity:
36733675
case AT_DropIdentity:
36743676
case AT_SetIdentity:
3677+
case AT_DropExpression:
36753678
cmd_lockmode = AccessExclusiveLock;
36763679
break;
36773680

@@ -3946,6 +3949,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
39463949
/* No command-specific prep needed */
39473950
pass = AT_PASS_COL_ATTRS;
39483951
break;
3952+
case AT_DropExpression:/* ALTER COLUMN DROP EXPRESSION */
3953+
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
3954+
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
3955+
ATPrepDropExpression(rel, cmd, recursing);
3956+
pass = AT_PASS_DROP;
3957+
break;
39493958
case AT_SetStatistics:/* ALTER COLUMN SET STATISTICS */
39503959
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX | ATT_FOREIGN_TABLE);
39513960
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
@@ -4265,6 +4274,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
42654274
case AT_CheckNotNull:/* check column is already marked NOT NULL */
42664275
ATExecCheckNotNull(tab, rel, cmd->name, lockmode);
42674276
break;
4277+
case AT_DropExpression:
4278+
address = ATExecDropExpression(rel, cmd->name, cmd->missing_ok, lockmode);
4279+
break;
42684280
case AT_SetStatistics:/* ALTER COLUMN SET STATISTICS */
42694281
address = ATExecSetStatistics(rel, cmd->name, cmd->num, cmd->def, lockmode);
42704282
break;
@@ -6457,7 +6469,9 @@ ATExecColumnDefault(Relation rel, const char *colName,
64576469
ereport(ERROR,
64586470
(errcode(ERRCODE_SYNTAX_ERROR),
64596471
errmsg("column \"%s\" of relation \"%s\" is a generated column",
6460-
colName, RelationGetRelationName(rel))));
6472+
colName, RelationGetRelationName(rel)),
6473+
newDefault || TupleDescAttr(tupdesc, attnum - 1)->attgenerated != ATTRIBUTE_GENERATED_STORED ? 0 :
6474+
errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.")));
64616475

64626476
/*
64636477
* Remove any old default for the column. We use RESTRICT here for
@@ -6725,6 +6739,151 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE
67256739
return address;
67266740
}
67276741

6742+
/*
6743+
* ALTER TABLE ALTER COLUMN DROP EXPRESSION
6744+
*/
6745+
static void
6746+
ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recursing)
6747+
{
6748+
/*
6749+
* Cannot drop generation expression from inherited columns.
6750+
*/
6751+
if (!recursing)
6752+
{
6753+
HeapTupletuple;
6754+
Form_pg_attribute attTup;
6755+
6756+
tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), cmd->name);
6757+
if (!HeapTupleIsValid(tuple))
6758+
ereport(ERROR,
6759+
(errcode(ERRCODE_UNDEFINED_COLUMN),
6760+
errmsg("column \"%s\" of relation \"%s\" does not exist",
6761+
cmd->name, RelationGetRelationName(rel))));
6762+
6763+
attTup = (Form_pg_attribute) GETSTRUCT(tuple);
6764+
6765+
if (attTup->attinhcount > 0)
6766+
ereport(ERROR,
6767+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
6768+
errmsg("cannot drop generation expression from inherited column")));
6769+
}
6770+
}
6771+
6772+
/*
6773+
* Return the address of the affected column.
6774+
*/
6775+
static ObjectAddress
6776+
ATExecDropExpression(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode)
6777+
{
6778+
HeapTupletuple;
6779+
Form_pg_attribute attTup;
6780+
AttrNumberattnum;
6781+
Relationattrelation;
6782+
ObjectAddress address;
6783+
6784+
attrelation = table_open(AttributeRelationId, RowExclusiveLock);
6785+
tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
6786+
if (!HeapTupleIsValid(tuple))
6787+
ereport(ERROR,
6788+
(errcode(ERRCODE_UNDEFINED_COLUMN),
6789+
errmsg("column \"%s\" of relation \"%s\" does not exist",
6790+
colName, RelationGetRelationName(rel))));
6791+
6792+
attTup = (Form_pg_attribute) GETSTRUCT(tuple);
6793+
attnum = attTup->attnum;
6794+
6795+
if (attnum <= 0)
6796+
ereport(ERROR,
6797+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
6798+
errmsg("cannot alter system column \"%s\"",
6799+
colName)));
6800+
6801+
if (attTup->attgenerated != ATTRIBUTE_GENERATED_STORED)
6802+
{
6803+
if (!missing_ok)
6804+
ereport(ERROR,
6805+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
6806+
errmsg("column \"%s\" of relation \"%s\" is not a stored generated column",
6807+
colName, RelationGetRelationName(rel))));
6808+
else
6809+
{
6810+
ereport(NOTICE,
6811+
(errmsg("column \"%s\" of relation \"%s\" is not a stored generated column, skipping",
6812+
colName, RelationGetRelationName(rel))));
6813+
heap_freetuple(tuple);
6814+
table_close(attrelation, RowExclusiveLock);
6815+
return InvalidObjectAddress;
6816+
}
6817+
}
6818+
6819+
attTup->attgenerated = '\0';
6820+
CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);
6821+
6822+
InvokeObjectPostAlterHook(RelationRelationId,
6823+
RelationGetRelid(rel),
6824+
attTup->attnum);
6825+
ObjectAddressSubSet(address, RelationRelationId,
6826+
RelationGetRelid(rel), attnum);
6827+
heap_freetuple(tuple);
6828+
6829+
table_close(attrelation, RowExclusiveLock);
6830+
6831+
CommandCounterIncrement();
6832+
6833+
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, false, false);
6834+
6835+
/*
6836+
* Remove all dependencies of this (formerly generated) column on other
6837+
* columns in the same table. (See StoreAttrDefault() for which
6838+
* dependencies are created.) We don't expect there to be dependencies
6839+
* between columns of the same table for other reasons, so it's okay to
6840+
* remove all of them.
6841+
*/
6842+
{
6843+
RelationdepRel;
6844+
ScanKeyData key[3];
6845+
SysScanDesc scan;
6846+
HeapTupletup;
6847+
6848+
depRel = table_open(DependRelationId, RowExclusiveLock);
6849+
6850+
ScanKeyInit(&key[0],
6851+
Anum_pg_depend_classid,
6852+
BTEqualStrategyNumber, F_OIDEQ,
6853+
ObjectIdGetDatum(RelationRelationId));
6854+
ScanKeyInit(&key[1],
6855+
Anum_pg_depend_objid,
6856+
BTEqualStrategyNumber, F_OIDEQ,
6857+
ObjectIdGetDatum(RelationGetRelid(rel)));
6858+
ScanKeyInit(&key[2],
6859+
Anum_pg_depend_objsubid,
6860+
BTEqualStrategyNumber, F_INT4EQ,
6861+
Int32GetDatum(attnum));
6862+
6863+
scan = systable_beginscan(depRel, DependDependerIndexId, true,
6864+
NULL, 3, key);
6865+
6866+
while (HeapTupleIsValid(tup = systable_getnext(scan)))
6867+
{
6868+
Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup);
6869+
6870+
if (depform->refclassid == RelationRelationId &&
6871+
depform->refobjid == RelationGetRelid(rel) &&
6872+
depform->refobjsubid != 0 &&
6873+
depform->deptype == DEPENDENCY_AUTO)
6874+
{
6875+
CatalogTupleDelete(depRel, &tup->t_self);
6876+
}
6877+
}
6878+
6879+
systable_endscan(scan);
6880+
6881+
table_close(depRel, RowExclusiveLock);
6882+
}
6883+
6884+
return address;
6885+
}
6886+
67286887
/*
67296888
* ALTER TABLE ALTER COLUMN SET STATISTICS
67306889
*

‎src/backend/parser/gram.y

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -639,7 +639,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
639639
DOUBLE_P DROP
640640

641641
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EVENT EXCEPT
642-
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN
642+
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
643643
EXTENSION EXTERNAL EXTRACT
644644

645645
FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR
@@ -2129,6 +2129,23 @@ alter_table_cmd:
21292129
n->name =$3;
21302130
$$ = (Node *)n;
21312131
}
2132+
/* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP EXPRESSION*/
2133+
|ALTERopt_columnColIdDROPEXPRESSION
2134+
{
2135+
AlterTableCmd *n = makeNode(AlterTableCmd);
2136+
n->subtype = AT_DropExpression;
2137+
n->name =$3;
2138+
$$ = (Node *)n;
2139+
}
2140+
/* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP EXPRESSION IF EXISTS*/
2141+
|ALTERopt_columnColIdDROPEXPRESSIONIF_PEXISTS
2142+
{
2143+
AlterTableCmd *n = makeNode(AlterTableCmd);
2144+
n->subtype = AT_DropExpression;
2145+
n->name =$3;
2146+
n->missing_ok =true;
2147+
$$ = (Node *)n;
2148+
}
21322149
/* ALTER TABLE <name> ALTER [COLUMN] <colname> SET STATISTICS <SignedIconst>*/
21332150
|ALTERopt_columnColIdSETSTATISTICSSignedIconst
21342151
{
@@ -15196,6 +15213,7 @@ unreserved_keyword:
1519615213
| EXCLUSIVE
1519715214
| EXECUTE
1519815215
| EXPLAIN
15216+
| EXPRESSION
1519915217
| EXTENSION
1520015218
| EXTERNAL
1520115219
| FAMILY

‎src/bin/psql/tab-complete.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2015,7 +2015,7 @@ psql_completion(const char *text, int start, int end)
20152015
/* ALTER TABLE ALTER [COLUMN] <foo> DROP */
20162016
elseif (Matches("ALTER","TABLE",MatchAny,"ALTER","COLUMN",MatchAny,"DROP")||
20172017
Matches("ALTER","TABLE",MatchAny,"ALTER",MatchAny,"DROP"))
2018-
COMPLETE_WITH("DEFAULT","IDENTITY","NOT NULL");
2018+
COMPLETE_WITH("DEFAULT","EXPRESSION","IDENTITY","NOT NULL");
20192019
elseif (Matches("ALTER","TABLE",MatchAny,"CLUSTER"))
20202020
COMPLETE_WITH("ON");
20212021
elseif (Matches("ALTER","TABLE",MatchAny,"CLUSTER","ON"))

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1785,6 +1785,7 @@ typedef enum AlterTableType
17851785
AT_ColumnDefault,/* alter column default */
17861786
AT_DropNotNull,/* alter column drop not null */
17871787
AT_SetNotNull,/* alter column set not null */
1788+
AT_DropExpression,/* alter column drop expression */
17881789
AT_CheckNotNull,/* check column is already marked not null */
17891790
AT_SetStatistics,/* alter column set statistics */
17901791
AT_SetOptions,/* alter column set ( options ) */

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -155,6 +155,7 @@ PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD)
155155
PG_KEYWORD("execute",EXECUTE,UNRESERVED_KEYWORD)
156156
PG_KEYWORD("exists",EXISTS,COL_NAME_KEYWORD)
157157
PG_KEYWORD("explain",EXPLAIN,UNRESERVED_KEYWORD)
158+
PG_KEYWORD("expression",EXPRESSION,UNRESERVED_KEYWORD)
158159
PG_KEYWORD("extension",EXTENSION,UNRESERVED_KEYWORD)
159160
PG_KEYWORD("external",EXTERNAL,UNRESERVED_KEYWORD)
160161
PG_KEYWORD("extract",EXTRACT,COL_NAME_KEYWORD)

0 commit comments

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