aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/commands/tablecmds.c76
-rw-r--r--src/backend/nodes/copyfuncs.c3
-rw-r--r--src/backend/nodes/equalfuncs.c3
-rw-r--r--src/backend/parser/gram.y24
-rw-r--r--src/include/nodes/parsenodes.h3
-rw-r--r--src/test/regress/expected/alter_table.out10
-rw-r--r--src/test/regress/sql/alter_table.sql9
7 files changed, 101 insertions, 27 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 50ab811039b..20253e1523f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.290 2009/07/16 06:33:42 petere Exp $
+ * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.291 2009/07/20 02:42:27 adunstan Exp $
*
*-------------------------------------------------------------------------
*/
@@ -285,7 +285,8 @@ static void ATExecSetStorage(Relation rel, const char *colName,
Node *newValue);
static void ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
- bool recurse, bool recursing);
+ bool recurse, bool recursing,
+ bool missing_ok);
static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild);
static void ATExecAddConstraint(List **wqueue,
@@ -298,8 +299,9 @@ static void ATAddCheckConstraint(List **wqueue,
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
FkConstraint *fkconstraint);
static void ATExecDropConstraint(Relation rel, const char *constrName,
- DropBehavior behavior,
- bool recurse, bool recursing);
+ DropBehavior behavior,
+ bool recurse, bool recursing,
+ bool missing_ok);
static void ATPrepAlterColumnType(List **wqueue,
AlteredTableInfo *tab, Relation rel,
bool recurse, bool recursing,
@@ -2620,11 +2622,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
break;
case AT_DropColumn: /* DROP COLUMN */
ATExecDropColumn(wqueue, rel, cmd->name,
- cmd->behavior, false, false);
+ cmd->behavior, false, false, cmd->missing_ok);
break;
case AT_DropColumnRecurse: /* DROP COLUMN with recursion */
ATExecDropColumn(wqueue, rel, cmd->name,
- cmd->behavior, true, false);
+ cmd->behavior, true, false, cmd->missing_ok);
break;
case AT_AddIndex: /* ADD INDEX */
ATExecAddIndex(tab, rel, (IndexStmt *) cmd->def, false);
@@ -2639,10 +2641,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
ATExecAddConstraint(wqueue, tab, rel, cmd->def, true);
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
- ATExecDropConstraint(rel, cmd->name, cmd->behavior, false, false);
+ ATExecDropConstraint(rel, cmd->name, cmd->behavior,
+ false, false,
+ cmd->missing_ok);
break;
case AT_DropConstraintRecurse: /* DROP CONSTRAINT with recursion */
- ATExecDropConstraint(rel, cmd->name, cmd->behavior, true, false);
+ ATExecDropConstraint(rel, cmd->name, cmd->behavior,
+ true, false,
+ cmd->missing_ok);
break;
case AT_AlterColumnType: /* ALTER COLUMN TYPE */
ATExecAlterColumnType(tab, rel, cmd->name, (TypeName *) cmd->def);
@@ -4160,7 +4166,8 @@ ATExecSetStorage(Relation rel, const char *colName, Node *newValue)
static void
ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
- bool recurse, bool recursing)
+ bool recurse, bool recursing,
+ bool missing_ok)
{
HeapTuple tuple;
Form_pg_attribute targetatt;
@@ -4176,11 +4183,21 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
* get the number of the attribute
*/
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
- if (!HeapTupleIsValid(tuple))
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("column \"%s\" of relation \"%s\" does not exist",
- colName, RelationGetRelationName(rel))));
+ if (!HeapTupleIsValid(tuple)){
+ if (!missing_ok){
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+ }
+ else
+ {
+ ereport(NOTICE,
+ (errmsg("column \"%s\" of relation \"%s\" does not exist, skipping",
+ colName, RelationGetRelationName(rel))));
+ return;
+ }
+ }
targetatt = (Form_pg_attribute) GETSTRUCT(tuple);
attnum = targetatt->attnum;
@@ -4246,7 +4263,8 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
{
/* Time to delete this child column, too */
ATExecDropColumn(wqueue, childrel, colName,
- behavior, true, true);
+ behavior, true, true,
+ false);
}
else
{
@@ -5360,7 +5378,8 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint,
static void
ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
- bool recurse, bool recursing)
+ bool recurse, bool recursing,
+ bool missing_ok)
{
List *children;
ListCell *child;
@@ -5422,12 +5441,22 @@ ATExecDropConstraint(Relation rel, const char *constrName,
systable_endscan(scan);
- if (!found)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("constraint \"%s\" of relation \"%s\" does not exist",
- constrName, RelationGetRelationName(rel))));
-
+ if (!found){
+ if (!missing_ok){
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ constrName, RelationGetRelationName(rel))));
+ }
+ else
+ {
+ ereport(NOTICE,
+ (errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
+ constrName, RelationGetRelationName(rel))));
+ heap_close(conrel, RowExclusiveLock);
+ return;
+ }
+ }
/*
* Propagate to children as appropriate. Unlike most other ALTER
* routines, we have to do this one level of recursion at a time; we can't
@@ -5490,7 +5519,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
{
/* Time to delete this child constraint, too */
ATExecDropConstraint(childrel, constrName, behavior,
- true, true);
+ true, true,
+ false);
}
else
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index ca920d11f79..0111ac4a9d9 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.433 2009/07/16 06:33:42 petere Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.434 2009/07/20 02:42:27 adunstan Exp $
*
*-------------------------------------------------------------------------
*/
@@ -2272,6 +2272,7 @@ _copyAlterTableCmd(AlterTableCmd *from)
COPY_NODE_FIELD(def);
COPY_NODE_FIELD(transform);
COPY_SCALAR_FIELD(behavior);
+ COPY_SCALAR_FIELD(missing_ok);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index ddb92a18e8b..c2e3f643d3e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -22,7 +22,7 @@
* Portions Copyright (c) 1994, Regents of the University of California
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.356 2009/07/16 06:33:42 petere Exp $
+ * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.357 2009/07/20 02:42:27 adunstan Exp $
*
*-------------------------------------------------------------------------
*/
@@ -958,6 +958,7 @@ _equalAlterTableCmd(AlterTableCmd *a, AlterTableCmd *b)
COMPARE_NODE_FIELD(def);
COMPARE_NODE_FIELD(transform);
COMPARE_SCALAR_FIELD(behavior);
+ COMPARE_SCALAR_FIELD(missing_ok);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1e633ca73ca..7e6d55be712 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.670 2009/07/16 06:33:43 petere Exp $
+ * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.671 2009/07/20 02:42:28 adunstan Exp $
*
* HISTORY
* AUTHOR DATE MAJOR EVENT
@@ -1610,6 +1610,16 @@ alter_table_cmd:
n->def = (Node *) makeString($6);
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */
+ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropColumn;
+ n->name = $5;
+ n->behavior = $6;
+ n->missing_ok = TRUE;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> DROP [COLUMN] <colname> [RESTRICT|CASCADE] */
| DROP opt_column ColId opt_drop_behavior
{
@@ -1617,6 +1627,7 @@ alter_table_cmd:
n->subtype = AT_DropColumn;
n->name = $3;
n->behavior = $4;
+ n->missing_ok = FALSE;
$$ = (Node *)n;
}
/*
@@ -1640,6 +1651,16 @@ alter_table_cmd:
n->def = $2;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> DROP CONSTRAINT IF EXISTS <name> [RESTRICT|CASCADE] */
+ | DROP CONSTRAINT IF_P EXISTS name opt_drop_behavior
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropConstraint;
+ n->name = $5;
+ n->behavior = $6;
+ n->missing_ok = TRUE;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> DROP CONSTRAINT <name> [RESTRICT|CASCADE] */
| DROP CONSTRAINT name opt_drop_behavior
{
@@ -1647,6 +1668,7 @@ alter_table_cmd:
n->subtype = AT_DropConstraint;
n->name = $3;
n->behavior = $4;
+ n->missing_ok = FALSE;
$$ = (Node *)n;
}
/* ALTER TABLE <name> SET WITH OIDS */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ecd76d7b41c..b54b170425c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -13,7 +13,7 @@
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.396 2009/07/16 06:33:45 petere Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.397 2009/07/20 02:42:28 adunstan Exp $
*
*-------------------------------------------------------------------------
*/
@@ -1145,6 +1145,7 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
* index, constraint, or parent table */
Node *transform; /* transformation expr for ALTER TYPE */
DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
+ bool missing_ok; /* skip error if missing? */
} AlterTableCmd;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index b14d61fd34f..031e59ca8f0 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1150,6 +1150,12 @@ alter table gc1 drop column name;
ERROR: column "name" of relation "gc1" does not exist
-- should work and drop the attribute in all tables
alter table p2 drop column height;
+-- IF EXISTS test
+create table dropColumnExists ();
+alter table dropColumnExists drop column non_existing; --fail
+ERROR: column "non_existing" of relation "dropcolumnexists" does not exist
+alter table dropColumnExists drop column if exists non_existing; --succeed
+NOTICE: column "non_existing" of relation "dropcolumnexists" does not exist, skipping
select relname, attname, attinhcount, attislocal
from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
@@ -1421,6 +1427,10 @@ alter table anothertab alter column atcol1 type boolean
ERROR: operator does not exist: boolean <= integer
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
alter table anothertab drop constraint anothertab_chk;
+alter table anothertab drop constraint anothertab_chk; -- fails
+ERROR: constraint "anothertab_chk" of relation "anothertab" does not exist
+alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
+NOTICE: constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
alter table anothertab alter column atcol1 type boolean
using case when atcol1 % 2 = 0 then true else false end;
select * from anothertab;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index e041cec8439..82c2e4ee017 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -816,6 +816,8 @@ create table dropColumnAnother (d int) inherits (dropColumnChild);
alter table dropColumnchild drop column a;
alter table only dropColumnChild drop column b;
+
+
-- these three should work
alter table only dropColumn drop column e;
alter table dropColumnChild drop column c;
@@ -913,6 +915,11 @@ alter table gc1 drop column name;
-- should work and drop the attribute in all tables
alter table p2 drop column height;
+-- IF EXISTS test
+create table dropColumnExists ();
+alter table dropColumnExists drop column non_existing; --fail
+alter table dropColumnExists drop column if exists non_existing; --succeed
+
select relname, attname, attinhcount, attislocal
from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
@@ -1057,6 +1064,8 @@ alter table anothertab alter column atcol1 drop default;
alter table anothertab alter column atcol1 type boolean
using case when atcol1 % 2 = 0 then true else false end; -- fails
alter table anothertab drop constraint anothertab_chk;
+alter table anothertab drop constraint anothertab_chk; -- fails
+alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
alter table anothertab alter column atcol1 type boolean
using case when atcol1 % 2 = 0 then true else false end;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-25 06:43:22 +0000