Thread: ALTER OBJECT SET SCHEMA

ALTER OBJECT SET SCHEMA

From
Bernd Helmle
Date:
Here's my current patch for ALTER OBJECT SET SCHEMA: the attached patch
file implements
schema "move" for FUNCTION, SEQUENCE, TYPE, DOMAIN and TABLE with all
improvements discussed on -hackers recently. Altering OPERATOR, OPERATOR
CLASS, AGGREGATE and CONVERSION are currently not implemented (since i ran
out of time) :(

Supported syntax is

ALTER TABLE name SET SCHEMA name;
ALTER SEQUENCE name SET SCHEMA name;
ALTER FUNCTION name SET SCHEMA name;
ALTER TYPE name SET SCHEMA name;
ALTER DOMAIN name SET SCHEMA name;

TIA

--
  Bernd

Attachment

Re: ALTER OBJECT SET SCHEMA

From
Bruce Momjian
Date:
Here is an updated version of your patch.  Would you supply SGML
documentation updates to match the code changes?  Thanks.

---------------------------------------------------------------------------

Bernd Helmle wrote:
> Here's my current patch for ALTER OBJECT SET SCHEMA: the attached patch
> file implements
> schema "move" for FUNCTION, SEQUENCE, TYPE, DOMAIN and TABLE with all
> improvements discussed on -hackers recently. Altering OPERATOR, OPERATOR
> CLASS, AGGREGATE and CONVERSION are currently not implemented (since i ran
> out of time) :(
>
> Supported syntax is
>
> ALTER TABLE name SET SCHEMA name;
> ALTER SEQUENCE name SET SCHEMA name;
> ALTER FUNCTION name SET SCHEMA name;
> ALTER TYPE name SET SCHEMA name;
> ALTER DOMAIN name SET SCHEMA name;
>
> TIA
>
> --
>   Bernd

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/catalog/pg_depend.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/pg_depend.c,v
retrieving revision 1.13
diff -c -c -r1.13 pg_depend.c
*** src/backend/catalog/pg_depend.c    14 Apr 2005 20:03:23 -0000    1.13
--- src/backend/catalog/pg_depend.c    29 Jul 2005 03:11:17 -0000
***************
*** 211,213 ****
--- 211,273 ----

      return ret;
  }
+
+ bool
+ changeDependencyFor(Oid classId, Oid objectId, Oid oldrefobjectId,
+                     Oid newrefobjectId)
+ {
+     ScanKeyData key[2];
+     SysScanDesc scan;
+     HeapTuple    tup;
+     Relation    depRel;
+     bool        result = false; /* nothing changed */
+
+     Assert(OidIsValid(classId) && OidIsValid(objectId) &&
+            OidIsValid(oldrefobjectId) && OidIsValid(newrefobjectId));
+
+     depRel = heap_open(DependRelationId, RowExclusiveLock);
+
+     ScanKeyInit(&key[0], Anum_pg_depend_classid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(classId));
+     ScanKeyInit(&key[1], Anum_pg_depend_objid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(objectId));
+
+     scan = systable_beginscan(depRel, DependDependerIndexId, true,
+                   SnapshotNow, 2, key);
+
+     while (HeapTupleIsValid((tup = systable_getnext(scan))))
+     {
+         Form_pg_depend depend_class = (Form_pg_depend) GETSTRUCT(tup);
+
+         if (depend_class->refobjid == oldrefobjectId)
+         {
+             ObjectAddress objAddr;
+
+             objAddr.classId = classId;
+             objAddr.objectId = oldrefobjectId;
+             objAddr.objectSubId = 0;
+
+             if (isObjectPinned(&objAddr, depRel))
+                 elog(ERROR, "attempt to change dependency on a system object!");
+
+             tup = heap_copytuple(tup);
+             depend_class = (Form_pg_depend) GETSTRUCT(tup);
+
+             depend_class->refobjid = newrefobjectId;
+             simple_heap_update(depRel, &tup->t_self, tup);
+             CatalogUpdateIndexes(depRel, tup);
+
+             /*
+              * Assume that the specified object/classId couldn't reference the
+              * changed object twice, so exit the loop immediately.
+              */
+             result = true;
+             break;
+         }
+     }
+
+     systable_endscan(scan);
+     heap_close(depRel, RowExclusiveLock);
+
+     return result;
+ }
Index: src/backend/commands/alter.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.13
diff -c -c -r1.13 alter.c
*** src/backend/commands/alter.c    28 Jun 2005 05:08:53 -0000    1.13
--- src/backend/commands/alter.c    29 Jul 2005 03:11:17 -0000
***************
*** 38,43 ****
--- 38,75 ----


  /*
+  * Executes an ALTER OBJECT / SET SCHEMA statement
+  */
+ void
+ ExecRenameObjSchemaStmt(RenameObjSchemaStmt *stmt)
+ {
+     Oid relid;
+
+     switch (stmt->renameType)
+     {
+         case OBJECT_TYPE:
+         case OBJECT_DOMAIN:
+             AlterDomainNamespace(stmt->object, stmt->newname);
+             break;
+
+         case OBJECT_FUNCTION:
+             AlterFunctionNamespace(stmt->object, stmt->objarg, stmt->newname);
+             break;
+
+         case OBJECT_SEQUENCE:
+         case OBJECT_TABLE:
+             CheckRelationOwnership(stmt->relation, true);
+             relid = RangeVarGetRelid(stmt->relation, false);
+             AlterTableNamespace(relid, stmt->newname);
+             break;
+
+         default:
+             elog(ERROR, "unrecognized rename schema stmt type: %d",
+                  (int) stmt->renameType);
+     }
+ }
+
+ /*
   * Executes an ALTER OBJECT / RENAME TO statement.    Based on the object
   * type, the function appropriate to that type is executed.
   */
Index: src/backend/commands/functioncmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.64
diff -c -c -r1.64 functioncmds.c
*** src/backend/commands/functioncmds.c    14 Jul 2005 21:46:29 -0000    1.64
--- src/backend/commands/functioncmds.c    29 Jul 2005 03:11:18 -0000
***************
*** 1427,1429 ****
--- 1427,1493 ----
      systable_endscan(scan);
      heap_close(relation, RowExclusiveLock);
  }
+
+ void
+ AlterFunctionNamespace(List *name, List *argtypes, const char *newnpname)
+ {
+     Oid             procOid;
+     Oid             oldnpOid;
+     Oid             newnpOid;
+     HeapTuple        tup;
+     HeapTuple        nptup;
+     Relation        rel;
+     Form_pg_proc        proc;
+     AclResult        aclrc;
+
+     Assert(newnpname != NULL && name != NIL);
+
+     rel = heap_open(ProcedureRelationId, RowExclusiveLock);
+     procOid = LookupFuncNameTypeNames(name, argtypes, false);
+
+     tup = SearchSysCacheCopy(PROCOID, ObjectIdGetDatum(procOid), 0, 0, 0);
+
+     if (!HeapTupleIsValid(tup))
+         elog(ERROR, "cache lookup failed for function %u", procOid);
+
+     nptup = SearchSysCacheCopy(NAMESPACENAME, CStringGetDatum(newnpname),
+                 0, 0, 0);
+     if (!HeapTupleIsValid(nptup))
+         ereport(ERROR,
+              (errcode(ERRCODE_UNDEFINED_SCHEMA),
+               errmsg("schema \"%s\" doesn't exists", newnpname)));
+
+     newnpOid = HeapTupleGetOid(nptup);
+     if ((aclrc = pg_namespace_aclcheck(newnpOid, GetUserId(), ACL_CREATE))
+             != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, newnpname);
+
+     proc = (Form_pg_proc) GETSTRUCT(tup);
+     oldnpOid = proc->pronamespace;
+
+     if ((aclrc = pg_namespace_aclcheck(oldnpOid, GetUserId(), ACL_CREATE))
+             != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, get_namespace_name(oldnpOid));
+
+     if (!pg_proc_ownercheck(HeapTupleGetOid(tup), GetUserId()))
+         aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, NameListToString(name));
+
+     if (SearchSysCacheExists(PROCNAMEARGSNSP,
+         CStringGetDatum(NameStr(proc->proname)),
+         PointerGetDatum(&proc->proargtypes), ObjectIdGetDatum(newnpOid), 0))
+         ereport(ERROR,
+              (errcode(ERRCODE_DUPLICATE_FUNCTION),
+               errmsg("function \"%s\" already exists in schema \"%s\"",
+                  NameStr(proc->proname), get_namespace_name(newnpOid))));
+
+     proc->pronamespace = newnpOid;
+     simple_heap_update(rel, &tup->t_self, tup);
+
+     CatalogUpdateIndexes(rel, tup);
+
+     if (changeDependencyFor(ProcedureRelationId, procOid, oldnpOid, newnpOid))
+         elog(NOTICE, "changed dependency to new schema \"%s\"", newnpname);
+
+     heap_freetuple(tup);
+     heap_close(rel, RowExclusiveLock);
+ }
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.164
diff -c -c -r1.164 tablecmds.c
*** src/backend/commands/tablecmds.c    14 Jul 2005 21:46:29 -0000    1.164
--- src/backend/commands/tablecmds.c    29 Jul 2005 03:11:22 -0000
***************
*** 15,20 ****
--- 15,21 ----
  #include "postgres.h"

  #include "access/genam.h"
+ #include "access/heapam.h"
  #include "access/tuptoaster.h"
  #include "catalog/catalog.h"
  #include "catalog/dependency.h"
***************
*** 22,27 ****
--- 23,29 ----
  #include "catalog/index.h"
  #include "catalog/indexing.h"
  #include "catalog/namespace.h"
+ #include "catalog/pg_attrdef.h"
  #include "catalog/pg_constraint.h"
  #include "catalog/pg_depend.h"
  #include "catalog/pg_inherits.h"
***************
*** 6198,6200 ****
--- 6200,6639 ----
          }
      }
  }
+
+ /*
+  * Rebuilds the default expression on a SERIAL column, identified
+  * by relid and attnum. The caller has to make sure, that attnum is really
+  * a SERIAL column, no further checks are done by
+  * RebuildSequenceDefaultExpr().
+  */
+ static void
+ RebuildSequenceDefaultExpr(Oid relid, AttrNumber attnum, char *seqname,
+                             char *nspname)
+ {
+     ScanKeyData keys[2];
+     SysScanDesc scan;
+     HeapTuple    tup;
+     bool        isnull;
+     Datum        value;
+     text        *adbin;
+     Expr        *expr;
+     char *quoted_name = palloc(strlen(nspname) + strlen(seqname) + 2 /* dot */);
+     /*
+      * Open the table we need to check for default expressions. Please
+      * note that we don't need to acquire a lock here, since we assume any
+      * interface function (such as AlterTableNamespace()) have locked the
+      * relation already.
+      */
+     Relation table_relation = heap_open(relid, NoLock);
+     Relation attr_default_rel = heap_open(AttrDefaultRelationId, RowExclusiveLock);
+
+     TupleDesc attrDesc = table_relation->rd_att;
+
+     MemSet(quoted_name, 0, strlen(nspname) + strlen(seqname) + 2);
+     strncpy(quoted_name, nspname, strlen(nspname));
+     strncat(quoted_name, ".", 1);
+     strncat(quoted_name, seqname, strlen(seqname));
+
+     /*
+      * Check that there is really a default expressions. Since we assume we are
+      * called from AlterSeqNamespacesForRel() there *must* be a default
+      * expression that calls nextval()...
+      */
+     if (!attrDesc->constr && attrDesc->constr->num_defval > 0)
+         elog(ERROR, "expected column defaults, but table %u has none defined",
+               relid);
+
+     ScanKeyInit(&keys[0], Anum_pg_attrdef_adrelid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(relid));
+     ScanKeyInit(&keys[1], Anum_pg_attrdef_adnum, BTEqualStrategyNumber, F_INT2EQ,
+                 Int16GetDatum(attnum));
+
+     scan = systable_beginscan(attr_default_rel, AttrDefaultIndexId, true,
+                                SnapshotNow, 2, keys);
+     if (!HeapTupleIsValid(tup = systable_getnext(scan)))
+         elog(ERROR, "could not found pg_attrdef attribute for column %d",
+               attnum + 1);
+
+     isnull = false;
+     value = heap_getattr(tup, Anum_pg_attrdef_adbin,
+                          RelationGetDescr(attr_default_rel), &isnull);
+     if (isnull)
+         elog(ERROR, "null value in adbin default expression for column %d",
+               attnum + 1);
+
+     adbin = DatumGetTextP(value);
+
+     /* Get executable expression node */
+
+     expr = stringToNode(VARDATA(adbin));
+
+     if IsA(expr, FuncExpr)
+     {
+         FuncExpr        *func;
+         Const            *cval;
+         Expr             *arg_expr;
+
+         func = (FuncExpr *) expr;
+         arg_expr = linitial(func->args);
+         if IsA(arg_expr, FuncExpr)
+         {
+             Expr         *const_expr;
+             Datum         values[ Natts_pg_attrdef ];
+             char         replaces[ Natts_pg_attrdef ];
+             char         nulls[4] = { ' ', ' ', ' ', ' ' };
+             char         *func_expr_str;
+             char         *adsrc;
+             HeapTuple     newtup;
+
+             func = (FuncExpr *)arg_expr;
+             const_expr = linitial(func->args);
+
+             if IsA(const_expr, Const)
+             {
+                 /* modify argument to nextval() */
+                 func->args = list_delete_first(func->args);
+                 cval = makeNode(Const);
+                 cval->consttype = ((Const *)const_expr)->consttype;
+                 cval->constlen    = ((Const *)const_expr)->constlen;
+                 cval->constisnull = ((Const *)const_expr)->constisnull;
+                 cval->constbyval  = ((Const *)const_expr)->constbyval;
+                 cval->constvalue = PointerGetDatum(DirectFunctionCall1(textin,
+                                                    CStringGetDatum(quoted_name)));
+                 func->args = lappend(func->args, cval);
+
+                 /* update expressions in pg_attrdef */
+                 func_expr_str = nodeToString(expr);
+                 values[ Anum_pg_attrdef_adbin - 1 ] = DirectFunctionCall1(textin,
+                                                    CStringGetDatum(func_expr_str));
+                 adsrc = deparse_expression((Node *)expr, deparse_context_for(
+                                     RelationGetRelationName(table_relation), relid),
+                                     false, false);
+                 values[ Anum_pg_attrdef_adsrc - 1 ] = DirectFunctionCall1(textin,
+                                                            CStringGetDatum(adsrc));
+                 MemSet(replaces, ' ', sizeof(replaces));
+                 replaces[ Anum_pg_attrdef_adbin - 1 ] = 'r';
+                 replaces[ Anum_pg_attrdef_adsrc - 1 ] = 'r';
+                 newtup = heap_modifytuple(tup, attr_default_rel->rd_att,
+                                            values, nulls, replaces);
+                 simple_heap_update(attr_default_rel, &newtup->t_self, newtup);
+                 CatalogUpdateIndexes(attr_default_rel, newtup);
+             }
+         }
+     }
+
+     systable_endscan(scan);
+     heap_close(table_relation, NoLock);
+     heap_close(attr_default_rel, RowExclusiveLock);
+ }
+
+ /*
+  * Moves all sequences, based on a SERIAL column type of the
+  * specified relation to a new schema. Please note that we only move
+  * sequences to the new schema, that have tracked dependencies in
+  * pg_depend, because only those could be created via a SERIAL
+  * column type.
+  */
+ static void
+ AlterSeqNamespacesForTable (Oid relOid,
+                  Oid newnspOid)
+ {
+     Relation         dependRel;
+     Relation         classRel;
+     HeapTuple         depTup;
+     ScanKeyData        key[2];
+     SysScanDesc        scan;
+
+     Assert(OidIsValid(relOid) && OidIsValid(newnspOid));
+
+     /* Open pg_depend and grab a row share lock. */
+     dependRel = heap_open(DependRelationId, RowShareLock);
+
+     ScanKeyInit(&key[0], Anum_pg_depend_refclassid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(RelationRelationId));
+
+     ScanKeyInit(&key[1], Anum_pg_depend_refobjid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(relOid));
+
+     scan = systable_beginscan(dependRel, DependReferenceIndexId, true,
+                                 SnapshotNow, 2, key);
+
+     classRel = heap_open(RelationRelationId, RowExclusiveLock);
+
+     while (HeapTupleIsValid((depTup = systable_getnext(scan))))
+     {
+         /*
+          * The dependent object is examined wether it is a attached sequence
+          * or not. If true, we need to pull out the pg_class tuple of
+          * the dependent sequence and modify the relnamespace to reflect
+          * the new table schema.
+          */
+         Form_pg_depend        dependent = (Form_pg_depend) GETSTRUCT(depTup);
+         Form_pg_class        myclass;
+         HeapTuple               seqTup;
+
+         seqTup = SearchSysCacheCopy(RELOID, ObjectIdGetDatum(dependent->objid),
+                                     0, 0, 0);
+         if (!HeapTupleIsValid(seqTup))
+             /*
+              *    Don't stop on a cache lookup failure, since this
+              *    only means that the dependent object is _not_ in pg_class
+              */
+             continue;
+
+         myclass = (Form_pg_class) GETSTRUCT(seqTup);
+
+         if (myclass->relkind == RELKIND_SEQUENCE)
+         {
+             TypeName        *seqName;
+             Relation         seqRel;
+             char             *nspname = get_namespace_name(newnspOid);
+
+             myclass->relnamespace = newnspOid;
+
+             elog(DEBUG1, "moving sequence OID %u to new schema OID %u",
+                  HeapTupleGetOid(seqTup), newnspOid);
+
+             simple_heap_update(classRel, &seqTup->t_self, seqTup);
+             CatalogUpdateIndexes(classRel, seqTup);
+
+             /*
+              * Sequences have entries in pg_type. We need to be careful
+              * to move them to the new namespace, too.
+              */
+             seqName = makeNode(TypeName);
+             seqName->names = NIL;
+             seqName->typeid = myclass->reltype;
+             seqName->typmod = -1;
+             seqName->arrayBounds = NIL;
+
+             seqRel = heap_open(TypeRelationId, RowExclusiveLock);
+             AlterTypeNamespace(myclass->reltype, seqRel, nspname, seqName, false);
+             heap_close(seqRel, RowExclusiveLock);
+
+             /*
+              * At least we need to rebuild all column default expressions that
+              * rely on this sequence.
+              */
+             if (dependent->refobjsubid > 0)
+             {
+                 AttrNumber attnum = dependent->refobjsubid;
+                 RebuildSequenceDefaultExpr(relOid, attnum,
+                                           NameStr(myclass->relname), nspname);
+             }
+         }
+     }
+
+     systable_endscan(scan);
+     heap_close(classRel, RowExclusiveLock);
+     heap_close(dependRel, RowShareLock);
+
+ }
+
+ /*
+  * Moves all constraints for the specified relation to another
+  * namespace, specified by newnspOid. The function supports 'moving'
+  * constraints that are defined on tables and domains. Other relkinds
+  * causes the function to trap an error.
+  *
+  * NOTE: AlterConstraintNamespacesForRel() doesn't check itself for
+  * access permissions, this should be done by the caller.
+  */
+ void
+ AlterConstraintNamespacesForRel(Oid relOid, Oid newnspOid, const char relkind)
+ {
+
+     Relation        conRel;
+     HeapTuple         tup;
+     SysScanDesc     scan = NULL;
+     ScanKeyData     key[1];
+
+     Assert(OidIsValid(relOid) && OidIsValid(newnspOid));
+
+     conRel = heap_open(ConstraintRelationId, RowExclusiveLock);
+
+     /* Initialize index scan */
+     switch (relkind)
+     {
+         case RELKIND_RELATION:
+             ScanKeyInit(&key[0], Anum_pg_constraint_conrelid,
+                         BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relOid));
+             scan = systable_beginscan(conRel, ConstraintRelidIndexId, true,
+                            SnapshotNow, 1, key);
+             break;
+
+         case RELKIND_COMPOSITE_TYPE:
+             ScanKeyInit(&key[0], Anum_pg_constraint_contypid,
+                         BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relOid));
+             scan = systable_beginscan(conRel, ConstraintTypidIndexId, true,
+                            SnapshotNow, 1, key);
+             break;
+
+         default:
+             elog(ERROR, "altering constraint namespace only supported for tables and types");
+             break;
+     }
+
+     while(HeapTupleIsValid((tup = systable_getnext(scan))))
+     {
+         Form_pg_constraint     constraint;
+         HeapTuple             newtuple = NULL;
+
+         newtuple = heap_copytuple(tup);
+         constraint = (Form_pg_constraint) GETSTRUCT(newtuple);
+         constraint->connamespace = newnspOid;
+
+         simple_heap_update(conRel, &newtuple->t_self, newtuple);
+         CatalogUpdateIndexes(conRel, newtuple);
+     }
+
+     systable_endscan(scan);
+     heap_close(conRel, RowExclusiveLock);
+ }
+
+ /*
+  * Moves all indexes for the specified relation to another namespace.
+  *
+  * NOTE: AlterIndexNamespacesForTable() assumes that the caller checks wether
+  * there are enough permissions on the source and target namespace and
+  * locks to "move" the index to the new namespace. This is should be
+  * no problem, since this function is intended to be a small helper function to
+  * AlterTableNamespace() only. That's also the reason why it lives here.
+  */
+ static void
+ AlterIndexNamespacesForTable(Oid relOid, Oid newnspOid)
+ {
+     Relation            indexRel;
+     Relation            classRel;
+     HeapTuple            tup;
+     ScanKeyData            key[1];
+     SysScanDesc            scan;
+
+     Assert(OidIsValid(relOid) && OidIsValid(newnspOid));
+
+     indexRel = heap_open(IndexRelationId, RowShareLock);
+
+     classRel = heap_open(RelationRelationId, RowExclusiveLock);
+
+     ScanKeyInit(&key[0], Anum_pg_index_indrelid, BTEqualStrategyNumber, F_OIDEQ,
+                 ObjectIdGetDatum(relOid));
+
+     scan = systable_beginscan(indexRel, IndexIndrelidIndexId, true, SnapshotNow, 1, key);
+
+     while (HeapTupleIsValid((tup = systable_getnext(scan))))
+     {
+         Form_pg_index        index = (Form_pg_index) GETSTRUCT(tup);
+         Form_pg_class        indexClass;
+         HeapTuple            classTup = NULL;
+
+         classTup = SearchSysCacheCopy(RELOID, ObjectIdGetDatum(index->indexrelid),
+                                          0, 0, 0);
+
+         if (!HeapTupleIsValid(classTup))
+             elog(ERROR, "cache lookup failed for index relation OID %u",
+                   index->indexrelid);
+
+         indexClass = (Form_pg_class) GETSTRUCT(classTup);
+         indexClass->relnamespace = newnspOid;
+
+         simple_heap_update(classRel, &classTup->t_self, classTup);
+         CatalogUpdateIndexes(classRel, classTup);
+     }
+
+     systable_endscan(scan);
+     heap_close(classRel, RowExclusiveLock);
+     heap_close(indexRel, RowShareLock);
+
+ }
+
+ /*
+  * Changes the namespace of the specified table
+  * Ownership of the specified relation (relid) should be checked
+  * by the caller.
+  */
+ void
+ AlterTableNamespace(Oid relid, const char *newnpname)
+ {
+     HeapTuple            tup;
+     Relation            rel;
+     Relation            relRel;
+     char                *relname;
+     Oid                oldnpOid;
+     Oid                newnpOid;
+     Relation            relType;
+     TypeName            *typename;
+     Form_pg_class        class;
+     AclResult            aclrc;
+
+     rel = relation_open(relid, AccessExclusiveLock);
+
+     tup = SearchSysCacheCopy(NAMESPACENAME, CStringGetDatum(newnpname),
+                              0, 0, 0);
+     if (!HeapTupleIsValid(tup))
+         ereport(ERROR,
+              (errcode(ERRCODE_UNDEFINED_SCHEMA),
+               errmsg("schema \"%s\" doesn't exists", newnpname)));
+
+     newnpOid = HeapTupleGetOid(tup);
+
+     if ((aclrc = pg_namespace_aclcheck(HeapTupleGetOid(tup), GetUserId(),
+         ACL_CREATE)) != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, newnpname);
+
+     oldnpOid = get_rel_namespace(relid);
+
+     if ((aclrc = pg_namespace_aclcheck(oldnpOid, GetUserId(), ACL_CREATE))
+         != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, get_namespace_name(oldnpOid));
+
+     if (oldnpOid == newnpOid)
+         elog(ERROR, "relation %u already in schema %s", relid, newnpname);
+
+     relname = get_rel_name(relid);
+     if (get_relname_relid(relname, newnpOid) != InvalidOid)
+         ereport(ERROR,
+             (errcode(ERRCODE_DUPLICATE_TABLE),
+               errmsg("relation \"%s\" already exists in schema \"%s\"",
+                      relname, newnpname)));
+
+     relRel = heap_open(RelationRelationId, RowExclusiveLock);
+
+     tup = SearchSysCacheCopy(RELOID, PointerGetDatum(relid), 0, 0, 0);
+
+     if (!HeapTupleIsValid(tup))
+         elog(ERROR, "cache lookup failed for relation %u", relid);
+
+     class = (Form_pg_class) GETSTRUCT(tup);
+     class->relnamespace = newnpOid;
+     simple_heap_update(relRel, &tup->t_self, tup);
+
+     relType = heap_open(TypeRelationId, RowExclusiveLock);
+
+     typename = makeNode(TypeName);
+     typename->names  = NIL;
+     typename->typeid = class->reltype;
+     typename->typmod = -1;
+     typename->arrayBounds = NIL;
+
+     AlterTypeNamespace(class->reltype, relType, newnpname, typename, false);
+
+     heap_close(relType, NoLock);
+
+     if (class->relkind == RELKIND_RELATION)
+     {
+         AlterIndexNamespacesForTable(relid, newnpOid);
+         AlterConstraintNamespacesForRel(relid, newnpOid, RELKIND_RELATION);
+         AlterSeqNamespacesForTable(relid, newnpOid);
+     }
+
+
+     if (changeDependencyFor(RelationRelationId, relid, oldnpOid, newnpOid))
+         elog(DEBUG1, "changed dependency to new schema \"%s\"", newnpname);
+
+     CatalogUpdateIndexes(relRel, tup);
+
+     heap_freetuple(tup);
+     heap_close(relRel, RowExclusiveLock);
+
+     relation_close(rel, NoLock);
+ }
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.76
diff -c -c -r1.76 typecmds.c
*** src/backend/commands/typecmds.c    14 Jul 2005 21:46:29 -0000    1.76
--- src/backend/commands/typecmds.c    29 Jul 2005 03:11:24 -0000
***************
*** 2097,2102 ****
          changeDependencyOnOwner(TypeRelationId, typeOid, newOwnerId);
      }

-     /* Clean up */
      heap_close(rel, RowExclusiveLock);
  }
--- 2097,2262 ----
          changeDependencyOnOwner(TypeRelationId, typeOid, newOwnerId);
      }

      heap_close(rel, RowExclusiveLock);
  }
+
+ /*
+  * Apply new namespace to specified type. Assume TypeRelationId
+  * is already opened by the caller.
+  *
+  * Specifying errorOnTableType to TRUE causes the function
+  * to error out if an attempt to rename a table type namespace occurs.
+  * This is necessary, since we want users to use ALTER TABLE to rename
+  * the table type's namespace.
+  */
+ void
+ AlterTypeNamespace(Oid typeOid, Relation rel, const char *newnpname,
+             const TypeName *typename, const bool errorOnTableType)
+ {
+     HeapTuple            tup;
+     HeapTuple            nsptup;
+     Form_pg_type        mytype;
+     AclResult            aclrc;
+     Oid                    oldnpOid;
+     Oid                    relTypeOid;
+     Oid                    classId;
+
+     Assert(OidIsValid(typeOid) && typename != NULL && rel != NULL && newnpname != NULL);
+
+     tup = SearchSysCacheCopy(TYPEOID, ObjectIdGetDatum(typeOid), 0, 0, 0);
+
+     if (!HeapTupleIsValid(tup))
+         elog(ERROR, "cache lookup failed for type %u", typeOid);
+
+     mytype = (Form_pg_type) GETSTRUCT(tup);
+
+     if (!pg_type_ownercheck(typeOid, GetUserId()))
+         aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE, TypeNameToString(typename));
+
+     if ((aclrc = pg_namespace_aclcheck(mytype->typnamespace, GetUserId(),
+             ACL_CREATE)) != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, get_namespace_name(mytype->typnamespace));
+
+     nsptup = SearchSysCacheCopy(NAMESPACENAME, CStringGetDatum(newnpname), 0, 0, 0);
+
+     if (!HeapTupleIsValid(nsptup))
+         ereport(ERROR,
+              (errcode(ERRCODE_UNDEFINED_SCHEMA),
+               errmsg("schema \"%s\" doesn't exists", newnpname)));
+
+     if ((aclrc = pg_namespace_aclcheck(HeapTupleGetOid(nsptup), GetUserId(),
+                     ACL_CREATE)) != ACLCHECK_OK)
+         aclcheck_error(aclrc, ACL_KIND_NAMESPACE, newnpname);
+
+     /*
+      * if errorOnTableType is requested, we don't want to allow renaming
+      * the namespace of a table type. If such an attempt occurs, we error
+      * out....
+      */
+     if (mytype->typtype == RELKIND_COMPOSITE_TYPE && errorOnTableType &&
+         get_rel_relkind(mytype->typrelid) != RELKIND_COMPOSITE_TYPE)
+         ereport(ERROR,
+             (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+              errmsg("\"%s\" is a table's row type",
+                 TypeNameToString(typename))));
+
+     if (mytype->typnamespace == HeapTupleGetOid(nsptup))
+         ereport(ERROR,
+             (errcode(ERRCODE_DUPLICATE_OBJECT),
+              errmsg("type \"%s\" already in schema \"%s\"",
+                 TypeNameToString(typename), newnpname)));
+
+     oldnpOid = mytype->typnamespace; /* save for dependency refactoring */
+     mytype->typnamespace = HeapTupleGetOid(nsptup);
+     simple_heap_update(rel, &tup->t_self, tup);
+
+     CatalogUpdateIndexes(rel, tup);
+
+     /*
+      * Composite types has pg_class entries.
+      * We need to modify the pg_class tuple as well to
+      * reflect the changes of its schema.
+      */
+     if (mytype->typtype == 'c' && get_rel_relkind(mytype->typrelid) == 'c')
+     {
+         Form_pg_class pg_class;
+         Relation  relClass = heap_open(RelationRelationId, RowExclusiveLock);
+         HeapTuple relTup   = SearchSysCacheCopy(RELOID,
+                                     ObjectIdGetDatum(mytype->typrelid), 0, 0, 0);
+
+         if (!HeapTupleIsValid(relTup))
+             elog(ERROR, "cache lookup for relation \"%s\" failed",
+               TypeNameToString(typename));
+
+         pg_class = (Form_pg_class) GETSTRUCT(relTup);
+         pg_class->relnamespace = HeapTupleGetOid(nsptup);
+
+         simple_heap_update(relClass, &relTup->t_self, relTup);
+         CatalogUpdateIndexes(relClass, relTup);
+
+         heap_close(relClass, RowExclusiveLock);
+         heap_freetuple(relTup);
+
+         relTypeOid = mytype->typrelid;
+         classId = RelationRelationId;
+     }
+     else
+     {
+         relTypeOid = typeOid;
+         classId = TypeRelationId;
+     }
+
+     if (changeDependencyFor(classId, relTypeOid, oldnpOid, HeapTupleGetOid(nsptup)))
+         elog(DEBUG1, "changed dependency to new schema \"%s\"", newnpname);
+
+     heap_freetuple(tup);
+     heap_freetuple(nsptup);
+ }
+
+ void
+ AlterDomainNamespace(List *names, const char *newnpname)
+ {
+     Oid                    typeOid;
+     Oid                    nspOid;
+     TypeName            *typename;
+     Relation            rel;
+     HeapTuple            nspTup;
+
+     Assert(names != NIL && newnpname != NULL);
+
+     /*
+      * As usual, create the type stuff so we can use
+      * standard type functions.
+      */
+
+     typename = makeNode(TypeName);
+     typename->names = names;
+     typename->typmod = -1;
+     typename->arrayBounds = NIL;
+
+     rel = heap_open(TypeRelationId, RowExclusiveLock);
+
+     /* get type OID */
+     typeOid = LookupTypeName(typename);
+
+     if (!OidIsValid(typeOid))
+         ereport(ERROR,
+             (errcode(ERRCODE_UNDEFINED_OBJECT),
+              errmsg("type \"%s\" does not exist",
+                 TypeNameToString(typename))));
+
+     AlterTypeNamespace(typeOid, rel, newnpname, typename, true);
+
+     nspTup = SearchSysCacheCopy(NAMESPACENAME, CStringGetDatum(newnpname), 0, 0, 0);
+
+     if (!HeapTupleIsValid(nspTup))
+         elog(ERROR, "cache lookup failure for schema %s", newnpname);
+
+     nspOid = HeapTupleGetOid(nspTup);
+
+     AlterConstraintNamespacesForRel(typeOid, nspOid, RELKIND_COMPOSITE_TYPE);
+
+     heap_close(rel, RowExclusiveLock);
+ }
+
+
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.504
diff -c -c -r2.504 gram.y
*** src/backend/parser/gram.y    26 Jul 2005 22:37:50 -0000    2.504
--- src/backend/parser/gram.y    29 Jul 2005 03:11:27 -0000
***************
*** 132,138 ****

  %type <node>    stmt schema_stmt
          AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
!         AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
          AlterRoleStmt AlterRoleSetStmt
          AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
          ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
--- 132,138 ----

  %type <node>    stmt schema_stmt
          AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
!         AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt AlterObjectSchemaStmt
          AlterRoleStmt AlterRoleSetStmt
          AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
          ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
***************
*** 487,492 ****
--- 487,493 ----

  stmt :
              AlterDatabaseSetStmt
+             | AlterObjectSchemaStmt
              | AlterDomainStmt
              | AlterFunctionStmt
              | AlterGroupStmt
***************
*** 3997,4002 ****
--- 3998,4060 ----
              | /*EMPTY*/                                { $$ = 0; }
          ;

+ /*****************************************************************************
+  *
+  * ALTER THING name SET SCHEMA name
+  *
+  *****************************************************************************/
+
+ AlterObjectSchemaStmt: ALTER TABLE relation_expr SET SCHEMA name
+                 {
+                     RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+                     n->renameType = OBJECT_TABLE;
+                     n->relation = $3;
+                     n->object = NIL;
+                     n->objarg = NIL;
+                     n->newname = $6;
+                     $$ = (Node *)n;
+                 }
+            | ALTER SEQUENCE relation_expr SET SCHEMA name
+                 {
+                     RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+                     n->renameType = OBJECT_SEQUENCE;
+                     n->relation = $3;
+                     n->object = NIL;
+                     n->objarg = NIL;
+                     n->newname = $6;
+                     $$ = (Node *)n;
+                 }
+            | ALTER FUNCTION func_name func_args SET SCHEMA name
+                {
+                     RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+                     n->renameType = OBJECT_FUNCTION;
+                     n->relation = NULL;
+                     n->object = $3;
+                     n->objarg = extractArgTypes($4);
+                     n->newname = $7;
+                     $$ = (Node *)n;
+                }
+            | ALTER DOMAIN_P any_name SET SCHEMA name
+                {
+                     RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+                     n->renameType = OBJECT_DOMAIN;
+                     n->relation = NULL;
+                     n->object = $3;
+                     n->objarg = NIL;
+                     n->newname = $6;
+                     $$ = (Node *)n;
+                 }
+            | ALTER TYPE_P any_name SET SCHEMA name
+                {
+                     RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+                     n->renameType = OBJECT_TYPE;
+                     n->relation = NULL;
+                     n->object = $3;
+                     n->objarg = NIL;
+                     n->newname = $6;
+                     $$ = (Node *)n;
+                }
+         ;

  /*****************************************************************************
   *
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.241
diff -c -c -r1.241 utility.c
*** src/backend/tcop/utility.c    14 Jul 2005 05:13:41 -0000    1.241
--- src/backend/tcop/utility.c    29 Jul 2005 03:11:29 -0000
***************
*** 284,289 ****
--- 284,290 ----
          case T_AlterSeqStmt:
          case T_AlterTableStmt:
          case T_RenameStmt:
+         case T_RenameObjSchemaStmt:
          case T_CommentStmt:
          case T_DefineStmt:
          case T_CreateCastStmt:
***************
*** 624,629 ****
--- 625,634 ----
              ExecRenameStmt((RenameStmt *) parsetree);
              break;

+         case T_RenameObjSchemaStmt:
+                 ExecRenameObjSchemaStmt((RenameObjSchemaStmt *)parsetree);
+                 break;
+
          case T_AlterOwnerStmt:
              ExecAlterOwnerStmt((AlterOwnerStmt *) parsetree);
              break;
***************
*** 1323,1328 ****
--- 1328,1360 ----
              tag = "COPY";
              break;

+         case T_RenameObjSchemaStmt:
+             switch(((RenameObjSchemaStmt *) parsetree)->renameType)
+             {
+                 case OBJECT_AGGREGATE:
+                     tag = "ALTER AGGREGATE";
+                     break;
+
+                 case OBJECT_CONVERSION:
+                     tag = "ALTER CONVERSION";
+
+                 case OBJECT_DOMAIN:
+                     tag = "ALTER DOMAIN";
+                     break;
+
+                 case OBJECT_FUNCTION:
+                     tag = "ALTER FUNCTION";
+                     break;
+
+                 case OBJECT_SEQUENCE:
+                     tag = "ALTER SEQUENCE";
+                     break;
+
+                 default:
+                     tag = "ALTER TABLE";
+                     break;
+             }
+
          case T_RenameStmt:
              switch (((RenameStmt *) parsetree)->renameType)
              {
Index: src/include/catalog/dependency.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/dependency.h,v
retrieving revision 1.15
diff -c -c -r1.15 dependency.h
*** src/include/catalog/dependency.h    7 Jul 2005 20:39:59 -0000    1.15
--- src/include/catalog/dependency.h    29 Jul 2005 03:11:30 -0000
***************
*** 198,201 ****
--- 198,206 ----

  extern void dropDatabaseDependencies(Oid databaseId);

+ extern bool changeDependencyFor(Oid classId,
+                                  Oid objectId,
+                                  Oid oldrefobjectId,
+                                  Oid newrefobjectId);
+
  #endif   /* DEPENDENCY_H */
Index: src/include/commands/alter.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/alter.h,v
retrieving revision 1.6
diff -c -c -r1.6 alter.h
*** src/include/commands/alter.h    31 Dec 2004 22:03:28 -0000    1.6
--- src/include/commands/alter.h    29 Jul 2005 03:11:30 -0000
***************
*** 16,21 ****
--- 16,23 ----

  #include "nodes/parsenodes.h"

+ extern void ExecRenameObjSchemaStmt(RenameObjSchemaStmt *stmt);
+
  extern void ExecRenameStmt(RenameStmt *stmt);

  extern void ExecAlterOwnerStmt(AlterOwnerStmt *stmt);
Index: src/include/commands/defrem.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/defrem.h,v
retrieving revision 1.66
diff -c -c -r1.66 defrem.h
*** src/include/commands/defrem.h    28 Jun 2005 05:09:12 -0000    1.66
--- src/include/commands/defrem.h    29 Jul 2005 03:11:30 -0000
***************
*** 55,60 ****
--- 55,62 ----
  extern void CreateCast(CreateCastStmt *stmt);
  extern void DropCast(DropCastStmt *stmt);
  extern void DropCastById(Oid castOid);
+ extern void AlterFunctionNamespace(List *name, List *argtypes,
+                     const char *newnpname);

  /* commands/operatorcmds.c */
  extern void DefineOperator(List *names, List *parameters);
Index: src/include/commands/tablecmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/tablecmds.h,v
retrieving revision 1.22
diff -c -c -r1.22 tablecmds.h
*** src/include/commands/tablecmds.h    27 Jan 2005 03:18:24 -0000    1.22
--- src/include/commands/tablecmds.h    29 Jul 2005 03:11:30 -0000
***************
*** 16,21 ****
--- 16,26 ----

  #include "nodes/parsenodes.h"

+ extern void AlterConstraintNamespacesForRel(Oid relOid,
+                          Oid newnspOid,
+                          const char relkind);
+ extern void AlterTableNamespace(Oid relid,
+                   const char *newnpname);

  extern Oid    DefineRelation(CreateStmt *stmt, char relkind);

Index: src/include/commands/typecmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/typecmds.h,v
retrieving revision 1.11
diff -c -c -r1.11 typecmds.h
*** src/include/commands/typecmds.h    28 Jun 2005 05:09:12 -0000    1.11
--- src/include/commands/typecmds.h    29 Jul 2005 03:11:30 -0000
***************
*** 19,24 ****
--- 19,27 ----

  #define DEFAULT_TYPDELIM        ','

+ extern void AlterTypeNamespace(Oid typeOid, Relation rel, const char *newnpname,
+                 const TypeName *typename, const bool errorOnTableType);
+ extern void AlterDomainNamespace(List *names, const char *newnpname);
  extern void DefineType(List *names, List *parameters);
  extern void RemoveType(List *names, DropBehavior behavior);
  extern void RemoveTypeById(Oid typeOid);
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.172
diff -c -c -r1.172 nodes.h
*** src/include/nodes/nodes.h    28 Jun 2005 05:09:13 -0000    1.172
--- src/include/nodes/nodes.h    29 Jul 2005 03:11:30 -0000
***************
*** 284,289 ****
--- 284,290 ----
      T_CreateTableSpaceStmt,
      T_DropTableSpaceStmt,
      T_AlterOwnerStmt,
+     T_RenameObjSchemaStmt,

      T_A_Expr = 800,
      T_ColumnRef,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.286
diff -c -c -r1.286 parsenodes.h
*** src/include/nodes/parsenodes.h    26 Jul 2005 16:38:28 -0000    1.286
--- src/include/nodes/parsenodes.h    29 Jul 2005 03:11:34 -0000
***************
*** 1471,1476 ****
--- 1471,1491 ----
  } RemoveOpClassStmt;

  /* ----------------------
+  *        Alter Object Rename Schema
+  * ----------------------
+  */
+ typedef struct RenameObjSchemaStmt
+ {
+     NodeTag    type;
+     RangeVar   *relation;
+     List       *object;
+     List       *objarg;
+
+     char       *newname;
+     ObjectType renameType;
+ } RenameObjSchemaStmt;
+
+ /* ----------------------
   *        Alter Object Rename Statement
   * ----------------------
   */

Re: ALTER OBJECT SET SCHEMA

From
Bernd Helmle
Date:
--On Donnerstag, Juli 28, 2005 23:12:37 -0400 Bruce Momjian
<pgman@candle.pha.pa.us> wrote:

> Here is an updated version of your patch.  Would you supply SGML
> documentation updates to match the code changes?  Thanks.

Bruce, is there any requirement to add some regression tests, too?

--
  Bernd

Re: ALTER OBJECT SET SCHEMA

From
Bruce Momjian
Date:
Bernd Helmle wrote:
> --On Donnerstag, Juli 28, 2005 23:12:37 -0400 Bruce Momjian
> <pgman@candle.pha.pa.us> wrote:
>
> > Here is an updated version of your patch.  Would you supply SGML
> > documentation updates to match the code changes?  Thanks.
>
> Bruce, is there any requirement to add some regression tests, too?

It would be nice, yes.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: ALTER OBJECT SET SCHEMA

From
Bernd Helmle
Date:
--On Donnerstag, Juli 28, 2005 23:12:37 -0400 Bruce Momjian
<pgman@candle.pha.pa.us> wrote:

> Here is an updated version of your patch.  Would you supply SGML
> documentation updates to match the code changes?  Thanks.

Here's my first shot on this. Let me know if there's somenthing missing or
broken (note: English is not my native language, i hope there aren't too
much faults....).

--
  Bernd

Attachment

Re: ALTER OBJECT SET SCHEMA

From
Tom Lane
Date:
Bernd Helmle <mailings@oopsware.de> writes:
>> Here is an updated version of your patch.  Would you supply SGML
>> documentation updates to match the code changes?  Thanks.

> Here's my first shot on this.

Applied with additional minor editing.

            regards, tom lane