--On Donnerstag, Mai 07, 2009 12:57:56 +0200 Andres Freund
<and...@anarazel.de> wrote:
Ok, fixed that place (stupid me, I had seen that it is used there and
forgot about it) and found no other places.
Thanks,
Andres
Here is a slightly updated version of the patch. I did some (very minor)
editing on the wording in the docs and cleaned a merge conflict in
tablecmds.c. I saw no code issues so far, make check passes without errors.
Maybe someone with more bison skills can comment on the duplicated parser
stuff, but it seems consistent with the rest of the code.
--
Thanks
Bernd
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fe3f388..f065e78 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
*************** ALTER TABLE <replaceable class="PARAMETE
*** 33,39 ****
where <replaceable class="PARAMETER">action</replaceable> is one of:
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> [ 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
--- 33,39 ----
where <replaceable class="PARAMETER">action</replaceable> is one of:
ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
! DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
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
*************** where <replaceable class="PARAMETER">act
*** 41,47 ****
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
! DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
--- 41,47 ----
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
! DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
*************** where <replaceable class="PARAMETER">act
*** 82,88 ****
</varlistentry>
<varlistentry>
! <term><literal>DROP COLUMN</literal></term>
<listitem>
<para>
This form drops a column from a table. Indexes and
--- 82,88 ----
</varlistentry>
<varlistentry>
! <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term>
<listitem>
<para>
This form drops a column from a table. Indexes and
*************** where <replaceable class="PARAMETER">act
*** 90,95 ****
--- 90,98 ----
dropped as well. You will need to say <literal>CASCADE</> if
anything outside the table depends on the column, for example,
foreign key references or views.
+ If <literal>IF EXISTS</literal> is specified and the column
+ does not exist, no error is thrown. In this case a notice
+ is issued instead.
</para>
</listitem>
</varlistentry>
*************** where <replaceable class="PARAMETER">act
*** 192,201 ****
</varlistentry>
<varlistentry>
! <term><literal>DROP CONSTRAINT</literal></term>
<listitem>
<para>
This form drops the specified constraint on a table.
</para>
</listitem>
</varlistentry>
--- 195,206 ----
</varlistentry>
<varlistentry>
! <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
<listitem>
<para>
This form drops the specified constraint on a table.
+ If <literal>IF EXISTS</literal> is specified and the constraint
+ does not exist, no error is thrown. In this case a notice is issued instead.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7afe6e7..52ae408 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*************** static void ATExecSetStorage(Relation re
*** 286,292 ****
Node *newValue);
static void ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
! bool recurse, bool recursing);
static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild);
static void ATExecAddConstraint(List **wqueue,
--- 286,293 ----
Node *newValue);
static void ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
! bool recurse, bool recursing,
! bool missing_ok);
static void ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild);
static void ATExecAddConstraint(List **wqueue,
*************** static void ATAddCheckConstraint(List **
*** 299,306 ****
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
FkConstraint *fkconstraint);
static void ATExecDropConstraint(Relation rel, const char *constrName,
! DropBehavior behavior,
! bool recurse, bool recursing);
static void ATPrepAlterColumnType(List **wqueue,
AlteredTableInfo *tab, Relation rel,
bool recurse, bool recursing,
--- 300,308 ----
static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
FkConstraint *fkconstraint);
static void ATExecDropConstraint(Relation rel, const char *constrName,
! DropBehavior behavior,
! bool recurse, bool recursing,
! bool missing_ok);
static void ATPrepAlterColumnType(List **wqueue,
AlteredTableInfo *tab, Relation rel,
bool recurse, bool recursing,
*************** ATExecCmd(List **wqueue, AlteredTableInf
*** 2621,2631 ****
break;
case AT_DropColumn: /* DROP COLUMN */
ATExecDropColumn(wqueue, rel, cmd->name,
! cmd->behavior, false, false);
break;
case AT_DropColumnRecurse: /* DROP COLUMN with recursion */
ATExecDropColumn(wqueue, rel, cmd->name,
! cmd->behavior, true, false);
break;
case AT_AddIndex: /* ADD INDEX */
ATExecAddIndex(tab, rel, (IndexStmt *) cmd->def, false);
--- 2623,2633 ----
break;
case AT_DropColumn: /* DROP COLUMN */
ATExecDropColumn(wqueue, rel, cmd->name,
! 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->missing_ok);
break;
case AT_AddIndex: /* ADD INDEX */
ATExecAddIndex(tab, rel, (IndexStmt *) cmd->def, false);
*************** ATExecCmd(List **wqueue, AlteredTableInf
*** 2640,2649 ****
ATExecAddConstraint(wqueue, tab, rel, cmd->def, true);
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
! ATExecDropConstraint(rel, cmd->name, cmd->behavior, false, false);
break;
case AT_DropConstraintRecurse: /* DROP CONSTRAINT with recursion */
! ATExecDropConstraint(rel, cmd->name, cmd->behavior, true, false);
break;
case AT_AlterColumnType: /* ALTER COLUMN TYPE */
ATExecAlterColumnType(tab, rel, cmd->name, (TypeName *) cmd->def);
--- 2642,2655 ----
ATExecAddConstraint(wqueue, tab, rel, cmd->def, true);
break;
case AT_DropConstraint: /* DROP CONSTRAINT */
! 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,
! cmd->missing_ok);
break;
case AT_AlterColumnType: /* ALTER COLUMN TYPE */
ATExecAlterColumnType(tab, rel, cmd->name, (TypeName *) cmd->def);
*************** ATExecSetStorage(Relation rel, const cha
*** 4161,4167 ****
static void
ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
! bool recurse, bool recursing)
{
HeapTuple tuple;
Form_pg_attribute targetatt;
--- 4167,4174 ----
static void
ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
DropBehavior behavior,
! bool recurse, bool recursing,
! bool missing_ok)
{
HeapTuple tuple;
Form_pg_attribute targetatt;
*************** ATExecDropColumn(List **wqueue, Relation
*** 4177,4187 ****
* 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))));
targetatt = (Form_pg_attribute) GETSTRUCT(tuple);
attnum = targetatt->attnum;
--- 4184,4204 ----
* get the number of the attribute
*/
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
! 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;
*************** ATExecDropColumn(List **wqueue, Relation
*** 4247,4253 ****
{
/* Time to delete this child column, too */
ATExecDropColumn(wqueue, childrel, colName,
! behavior, true, true);
}
else
{
--- 4264,4271 ----
{
/* Time to delete this child column, too */
ATExecDropColumn(wqueue, childrel, colName,
! behavior, true, true,
! false);
}
else
{
*************** createForeignKeyTriggers(Relation rel, F
*** 5361,5367 ****
static void
ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
! bool recurse, bool recursing)
{
List *children;
ListCell *child;
--- 5379,5386 ----
static void
ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior,
! bool recurse, bool recursing,
! bool missing_ok)
{
List *children;
ListCell *child;
*************** ATExecDropConstraint(Relation rel, const
*** 5423,5434 ****
systable_endscan(scan);
! if (!found)
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("constraint \"%s\" of relation \"%s\" does not exist",
! constrName, RelationGetRelationName(rel))));
!
/*
* 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
--- 5442,5463 ----
systable_endscan(scan);
! 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
*************** ATExecDropConstraint(Relation rel, const
*** 5491,5497 ****
{
/* Time to delete this child constraint, too */
ATExecDropConstraint(childrel, constrName, behavior,
! true, true);
}
else
{
--- 5520,5527 ----
{
/* Time to delete this child constraint, too */
ATExecDropConstraint(childrel, constrName, behavior,
! true, true,
! false);
}
else
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2c7d481..d0cab57 100644
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
*************** _copyAlterTableCmd(AlterTableCmd *from)
*** 2272,2277 ****
--- 2272,2278 ----
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 c61de97..cf28313 100644
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
*************** _equalAlterTableCmd(AlterTableCmd *a, Al
*** 958,963 ****
--- 958,964 ----
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 20ab0ba..e7ee4a2 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** alter_table_cmd:
*** 1593,1598 ****
--- 1593,1608 ----
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
{
*************** alter_table_cmd:
*** 1600,1605 ****
--- 1610,1616 ----
n->subtype = AT_DropColumn;
n->name = $3;
n->behavior = $4;
+ n->missing_ok = FALSE;
$$ = (Node *)n;
}
/*
*************** alter_table_cmd:
*** 1623,1628 ****
--- 1634,1649 ----
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
{
*************** alter_table_cmd:
*** 1630,1635 ****
--- 1651,1657 ----
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 a108b80..bd8dec9 100644
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
*************** typedef struct AlterTableCmd /* one subc
*** 1145,1150 ****
--- 1145,1151 ----
* 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 b14d61f..031e59c 100644
*** a/src/test/regress/expected/alter_table.out
--- b/src/test/regress/expected/alter_table.out
*************** alter table gc1 drop column name;
*** 1150,1155 ****
--- 1150,1161 ----
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
*************** alter table anothertab alter column atco
*** 1421,1426 ****
--- 1427,1436 ----
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 e041cec..82c2e4e 100644
*** a/src/test/regress/sql/alter_table.sql
--- b/src/test/regress/sql/alter_table.sql
*************** create table dropColumnAnother (d int) i
*** 816,821 ****
--- 816,823 ----
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;
*************** alter table gc1 drop column name;
*** 913,918 ****
--- 915,925 ----
-- 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
*************** alter table anothertab alter column atco
*** 1057,1062 ****
--- 1064,1071 ----
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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
