Here is a patch to add a command
ALTER TABLE ... RENAME CONSTRAINT ...
Currently, it only supports table constraints. I have an almost
finished patch for renaming domain constraints, but it is easier to keep
it separate.
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1976f6d..cd9ee97 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -25,6 +25,8 @@ ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
<replaceable class="PARAMETER">action</replaceable> [, ... ]
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
+ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
+ RENAME CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> TO <replaceable class="PARAMETER">new_constraint_name</replaceable>
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
@@ -555,8 +557,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
<listitem>
<para>
The <literal>RENAME</literal> forms change the name of a table
- (or an index, sequence, or view) or the name of an individual column in
- a table. There is no effect on the stored data.
+ (or an index, sequence, or view), the name of an individual column in
+ a table, or the name of a constraint of the table. There is no effect on the stored data.
</para>
</listitem>
</varlistentry>
@@ -869,7 +871,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
<para>
If a table has any descendant tables, it is not permitted to add,
- rename, or change the type of a column in the parent table without doing
+ rename, or change the type of a column, or rename an inherited constraint
+ in the parent table without doing
the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
will be rejected. This ensures that the descendants always have
columns matching the parent.
@@ -969,6 +972,13 @@ ALTER TABLE distributors RENAME TO suppliers;
</para>
<para>
+ To rename an existing constraint:
+<programlisting>
+ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
+</programlisting>
+ </para>
+
+ <para>
To add a not-null constraint to a column:
<programlisting>
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index 7c658c0..ef8c5fb 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -57,6 +57,10 @@ ExecRenameStmt(RenameStmt *stmt)
RenameCollation(stmt->object, stmt->newname);
break;
+ case OBJECT_CONSTRAINT:
+ RenameConstraint(stmt);
+ break;
+
case OBJECT_CONVERSION:
RenameConversion(stmt->object, stmt->newname);
break;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c373016..33ba611 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -2320,6 +2320,108 @@ renameatt(RenameStmt *stmt)
stmt->behavior);
}
+
+/*
+ * same logic as renameatt_internal
+ */
+static void
+rename_constraint_internal(Oid myrelid,
+ const char *oldconname,
+ const char *newconname,
+ bool recurse,
+ bool recursing,
+ int expected_parents)
+{
+ Relation targetrelation;
+ Oid constraintOid;
+ HeapTuple tuple;
+ Form_pg_constraint con;
+
+ targetrelation = relation_open(myrelid, AccessExclusiveLock);
+ /* don't tell it whether we're recursing; we allow changing typed tables here */
+ renameatt_check(myrelid, RelationGetForm(targetrelation), false);
+
+ constraintOid = get_constraint_oid(myrelid, oldconname, false);
+
+ tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constraintOid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for constraint %u",
+ constraintOid);
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ if (!con->conisonly)
+ {
+ if (recurse)
+ {
+ List *child_oids,
+ *child_numparents;
+ ListCell *lo,
+ *li;
+
+ child_oids = find_all_inheritors(myrelid, AccessExclusiveLock,
+ &child_numparents);
+
+ forboth(lo, child_oids, li, child_numparents)
+ {
+ Oid childrelid = lfirst_oid(lo);
+ int numparents = lfirst_int(li);
+
+ if (childrelid == myrelid)
+ continue;
+
+ rename_constraint_internal(childrelid, oldconname, newconname, false, true, numparents);
+ }
+ }
+ else
+ {
+ if (expected_parents == 0 &&
+ find_inheritance_children(myrelid, NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("inherited constraint \"%s\" must be renamed in child tables too",
+ oldconname)));
+ }
+
+ if (con->coninhcount > expected_parents)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot rename inherited constraint \"%s\"",
+ oldconname)));
+ }
+
+ if (con->conindid
+ && (con->contype == CONSTRAINT_PRIMARY
+ || con->contype == CONSTRAINT_UNIQUE
+ || con->contype == CONSTRAINT_EXCLUSION))
+ /* rename the index; this renames the constraint as well */
+ RenameRelationInternal(con->conindid, newconname);
+ else
+ RenameConstraintById(constraintOid, newconname);
+
+ ReleaseSysCache(tuple);
+
+ relation_close(targetrelation, NoLock); /* close rel but keep lock */
+}
+
+void
+RenameConstraint(RenameStmt *stmt)
+{
+ Oid relid;
+
+ /* lock level taken here should match rename_constraint_internal */
+ relid = RangeVarGetRelidExtended(stmt->relation, AccessExclusiveLock,
+ false, false,
+ RangeVarCallbackForRenameAttribute,
+ NULL);
+
+ rename_constraint_internal(relid,
+ stmt->subname,
+ stmt->newname,
+ interpretInhOption(stmt->relation->inhOpt), /* recursive? */
+ false, /* recursing? */
+ 0 /* expected inhcount */);
+}
+
/*
* Execute ALTER TABLE/INDEX/SEQUENCE/VIEW/FOREIGN TABLE RENAME
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0ec039b..01cb1c2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6573,6 +6573,16 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name
n->newname = $8;
$$ = (Node *)n;
}
+ | ALTER TABLE relation_expr RENAME CONSTRAINT name TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_CONSTRAINT;
+ n->relationType = OBJECT_TABLE;
+ n->relation = $3;
+ n->subname = $6;
+ n->newname = $8;
+ $$ = (Node *)n;
+ }
| ALTER FOREIGN TABLE relation_expr RENAME opt_column name TO name
{
RenameStmt *n = makeNode(RenameStmt);
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 03f397d..47b0cdd 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -48,6 +48,8 @@ extern void SetRelationHasSubclass(Oid relationId, bool relhassubclass);
extern void renameatt(RenameStmt *stmt);
+extern void RenameConstraint(RenameStmt *stmt);
+
extern void RenameRelation(RenameStmt *stmt);
extern void RenameRelationInternal(Oid myrelid,
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 57096f2..f82c2c2 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -154,6 +154,110 @@ DROP VIEW tmp_view_new;
-- toast-like relation name
alter table stud_emp rename to pg_toast_stud_emp;
alter table pg_toast_stud_emp rename to stud_emp;
+-- renaming index should rename constraint as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek"
+ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+-- renaming constraint
+ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
+ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
+-- renaming constraint should rename index as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek"
+DROP INDEX onek_unique1_constraint; -- to see whether it's there
+ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
+HINT: You can drop constraint onek_unique1_constraint on table onek instead.
+ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
+DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
+ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
+HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead.
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+-- renaming constraints vs. inheritance
+CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con1" CHECK (a > 0)
+
+CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging constraint "con1" with inherited definition
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
+ERROR: cannot rename inherited constraint "con1"
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
+ERROR: inherited constraint "con1" must be renamed in child tables too
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
+\d constraint_rename_test
+Table "public.constraint_rename_test"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+Check constraints:
+ "con2bar" (ONLY) CHECK (b > 0)
+ "con1foo" CHECK (a > 0)
+Number of child tables: 1 (Use \d+ to list them.)
+
+\d constraint_rename_test2
+Table "public.constraint_rename_test2"
+ Column | Type | Modifiers
+--------+---------+-----------
+ a | integer |
+ b | integer |
+ c | integer |
+ d | integer |
+Check constraints:
+ "con1foo" CHECK (a > 0)
+Inherits: constraint_rename_test
+
+DROP TABLE constraint_rename_test2;
+DROP TABLE constraint_rename_test;
-- FOREIGN KEY CONSTRAINT adding TEST
CREATE TABLE tmp2 (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index faafb22..c6942b8 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -186,6 +186,41 @@ DROP VIEW tmp_view_new;
alter table stud_emp rename to pg_toast_stud_emp;
alter table pg_toast_stud_emp rename to stud_emp;
+-- renaming index should rename constraint as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+
+-- renaming constraint
+ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
+ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
+ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
+
+-- renaming constraint should rename index as well
+ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
+DROP INDEX onek_unique1_constraint; -- to see whether it's there
+ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
+DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
+ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
+
+-- renaming constraints vs. inheritance
+CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
+\d constraint_rename_test
+CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
+\d constraint_rename_test2
+ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
+\d constraint_rename_test
+\d constraint_rename_test2
+ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);
+ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
+ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
+\d constraint_rename_test
+\d constraint_rename_test2
+DROP TABLE constraint_rename_test2;
+DROP TABLE constraint_rename_test;
+
-- FOREIGN KEY CONSTRAINT adding TEST
CREATE TABLE tmp2 (a int primary key);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
