Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7ECE79FBB02 for ; Sun, 1 Jul 2007 16:55:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 13514-03-2 for ; Sun, 1 Jul 2007 16:55:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fetter.org (start.fetter.org [66.92.188.65]) by postgresql.org (Postfix) with ESMTP id 2EDEE9FBB49 for ; Sun, 1 Jul 2007 16:55:37 -0300 (ADT) Received: by fetter.org (Postfix, from userid 500) id 0F04DFBC719; Sun, 1 Jul 2007 12:55:28 -0700 (PDT) Date: Sun, 1 Jul 2007 12:55:28 -0700 From: David Fetter To: PostgreSQL Patches Cc: Tom Lane , Neil Conway , Euler Taveira de Oliveira , Susanne Ebrecht Subject: Re: [DOCS] rename of a view Message-ID: <20070701195528.GX7636@fetter.org> References: <46839C05.7050307@miracee.de> <3450.1183040211@sss.pgh.pa.us> <20070629183219.GO7636@fetter.org> <20070630001653.GQ7636@fetter.org> <4685D8C8.2070808@timbira.com> <12776.1183177571@sss.pgh.pa.us> <1183181585.20942.1.camel@goldbach> <15084.1183181782@sss.pgh.pa.us> <1183182221.20942.8.camel@goldbach> <15362.1183182531@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="ZmUaFz6apKcXQszQ" Content-Disposition: inline In-Reply-To: <15362.1183182531@sss.pgh.pa.us> User-Agent: Mutt/1.5.14 (2007-02-12) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/15 X-Sequence-Number: 3013 --ZmUaFz6apKcXQszQ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Sat, Jun 30, 2007 at 01:48:51AM -0400, Tom Lane wrote: > Neil Conway writes: > > For the ALTER SEQUENCE case, I think it's also a little weird to > > have an ALTER SEQUENCE command that modifies some of the > > properties of a sequence, but not the sequence's name. While that > > argument doesn't apply to ALTER VIEW at the moment, recent history > > suggests that it may only be a matter of time before we need to > > add an ALTER VIEW command anyway... > > OK, that's a fairly convincing argument. Fire away. > > (I'm still not sure you found all the relevant places in the > documentation, however.) Here's a new patch + file. This one allows ALTER [SEQUENCE | VIEW] to work only on the respective database objects, but permits the old ALTER TABLE syntax. Cheers, David. -- David Fetter http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate --ZmUaFz6apKcXQszQ Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="alter_view_sequence_v2.diff" Index: doc/src/sgml/reference.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/reference.sgml,v retrieving revision 1.63 diff -c -r1.63 reference.sgml *** doc/src/sgml/reference.sgml 26 Apr 2007 16:13:08 -0000 1.63 --- doc/src/sgml/reference.sgml 1 Jul 2007 19:42:33 -0000 *************** *** 53,58 **** --- 53,59 ---- &alterTrigger; &alterType; &alterUser; + &alterView; &analyze; &begin; &checkpoint; Index: doc/src/sgml/ref/allfiles.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v retrieving revision 1.70 diff -c -r1.70 allfiles.sgml *** doc/src/sgml/ref/allfiles.sgml 26 Apr 2007 16:13:08 -0000 1.70 --- doc/src/sgml/ref/allfiles.sgml 1 Jul 2007 19:42:33 -0000 *************** *** 25,30 **** --- 25,31 ---- + Index: doc/src/sgml/ref/alter_sequence.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v retrieving revision 1.16 diff -c -r1.16 alter_sequence.sgml *** doc/src/sgml/ref/alter_sequence.sgml 31 Jan 2007 23:26:02 -0000 1.16 --- doc/src/sgml/ref/alter_sequence.sgml 1 Jul 2007 19:42:34 -0000 *************** *** 29,34 **** --- 29,35 ---- [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table.column | NONE } ] ALTER SEQUENCE name SET SCHEMA new_schema + ALTER SEQUENCE name RENAME TO new_name *************** *** 190,195 **** --- 191,207 ---- + + + RENAME TO new_name + + + The RENAME TO option causes the sequence to be + renamed. + + + + Index: src/backend/commands/alter.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/alter.c,v retrieving revision 1.23 diff -c -r1.23 alter.c *** src/backend/commands/alter.c 26 Mar 2007 16:58:38 -0000 1.23 --- src/backend/commands/alter.c 1 Jul 2007 19:42:34 -0000 *************** *** 83,88 **** --- 83,90 ---- break; case OBJECT_TABLE: + case OBJECT_SEQUENCE: + case OBJECT_VIEW: case OBJECT_INDEX: case OBJECT_COLUMN: case OBJECT_TRIGGER: *************** *** 96,101 **** --- 98,105 ---- switch (stmt->renameType) { case OBJECT_TABLE: + case OBJECT_SEQUENCE: + case OBJECT_VIEW: case OBJECT_INDEX: { /* *************** *** 113,119 **** aclcheck_error(aclresult, ACL_KIND_NAMESPACE, get_namespace_name(namespaceId)); ! renamerel(relid, stmt->newname); break; } case OBJECT_COLUMN: --- 117,123 ---- aclcheck_error(aclresult, ACL_KIND_NAMESPACE, get_namespace_name(namespaceId)); ! renamerel(relid, stmt->newname, stmt->renameType); break; } case OBJECT_COLUMN: Index: src/backend/commands/tablecmds.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.228 diff -c -r1.228 tablecmds.c *** src/backend/commands/tablecmds.c 23 Jun 2007 22:12:50 -0000 1.228 --- src/backend/commands/tablecmds.c 1 Jul 2007 19:42:35 -0000 *************** *** 41,46 **** --- 41,47 ---- #include "executor/executor.h" #include "miscadmin.h" #include "nodes/makefuncs.h" + #include "nodes/parsenodes.h" #include "optimizer/clauses.h" #include "optimizer/plancat.h" #include "optimizer/prep.h" *************** *** 1621,1627 **** * sequence, AFAIK there's no need for it to be there. */ void ! renamerel(Oid myrelid, const char *newrelname) { Relation targetrelation; Relation relrelation; /* for RELATION relation */ --- 1622,1628 ---- * sequence, AFAIK there's no need for it to be there. */ void ! renamerel(Oid myrelid, const char *newrelname, ObjectType newreltype) { Relation targetrelation; Relation relrelation; /* for RELATION relation */ *************** *** 1633,1640 **** bool relhastriggers; /* ! * Grab an exclusive lock on the target table or index, which we will NOT ! * release until end of transaction. */ targetrelation = relation_open(myrelid, AccessExclusiveLock); --- 1634,1641 ---- bool relhastriggers; /* ! * Grab an exclusive lock on the target table, index, sequence or ! * view, which we will NOT release until end of transaction. */ targetrelation = relation_open(myrelid, AccessExclusiveLock); *************** *** 1648,1653 **** --- 1649,1662 ---- RelationGetRelationName(targetrelation)))); relkind = targetrelation->rd_rel->relkind; + if (newreltype == OBJECT_SEQUENCE && relkind != 'S') + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("You can only call ALTER SEQUENCE on a sequence"))); + if (newreltype == OBJECT_VIEW && relkind != 'v') + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("You can only call ALTER VIEW on a view"))); relhastriggers = (targetrelation->rd_rel->reltriggers > 0); /* Index: src/backend/parser/gram.y =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v retrieving revision 2.596 diff -c -r2.596 gram.y *** src/backend/parser/gram.y 23 Jun 2007 22:12:51 -0000 2.596 --- src/backend/parser/gram.y 1 Jul 2007 19:42:36 -0000 *************** *** 4546,4551 **** --- 4546,4569 ---- n->newname = $6; $$ = (Node *)n; } + | ALTER SEQUENCE relation_expr RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_SEQUENCE; + n->relation = $3; + n->subname = NULL; + n->newname = $6; + $$ = (Node *)n; + } + | ALTER VIEW relation_expr RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_VIEW; + n->relation = $3; + n->subname = NULL; + n->newname = $6; + $$ = (Node *)n; + } | ALTER INDEX relation_expr RENAME TO name { RenameStmt *n = makeNode(RenameStmt); Index: src/backend/tcop/utility.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v retrieving revision 1.282 diff -c -r1.282 utility.c *** src/backend/tcop/utility.c 28 Jun 2007 00:02:39 -0000 1.282 --- src/backend/tcop/utility.c 1 Jul 2007 19:42:37 -0000 *************** *** 1574,1579 **** --- 1574,1582 ---- case OBJECT_SCHEMA: tag = "ALTER SCHEMA"; break; + case OBJECT_SEQUENCE: + tag = "ALTER SEQUENCE"; + break; case OBJECT_COLUMN: case OBJECT_TABLE: tag = "ALTER TABLE"; *************** *** 1584,1589 **** --- 1587,1595 ---- case OBJECT_TRIGGER: tag = "ALTER TRIGGER"; break; + case OBJECT_VIEW: + tag = "ALTER VIEW"; + break; default: tag = "???"; break; Index: src/bin/psql/tab-complete.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/psql/tab-complete.c,v retrieving revision 1.165 diff -c -r1.165 tab-complete.c *** src/bin/psql/tab-complete.c 13 Jun 2007 23:59:47 -0000 1.165 --- src/bin/psql/tab-complete.c 1 Jul 2007 19:42:37 -0000 *************** *** 602,608 **** static const char *const list_ALTER[] = {"AGGREGATE", "CONVERSION", "DATABASE", "DOMAIN", "FUNCTION", "GROUP", "INDEX", "LANGUAGE", "OPERATOR", "ROLE", "SCHEMA", "SEQUENCE", "TABLE", ! "TABLESPACE", "TRIGGER", "TYPE", "USER", NULL}; COMPLETE_WITH_LIST(list_ALTER); } --- 602,608 ---- static const char *const list_ALTER[] = {"AGGREGATE", "CONVERSION", "DATABASE", "DOMAIN", "FUNCTION", "GROUP", "INDEX", "LANGUAGE", "OPERATOR", "ROLE", "SCHEMA", "SEQUENCE", "TABLE", ! "TABLESPACE", "TRIGGER", "TYPE", "USER", "VIEW", NULL}; COMPLETE_WITH_LIST(list_ALTER); } *************** *** 714,720 **** { static const char *const list_ALTERSEQUENCE[] = {"INCREMENT", "MINVALUE", "MAXVALUE", "RESTART", "NO", "CACHE", "CYCLE", ! "SET SCHEMA", NULL}; COMPLETE_WITH_LIST(list_ALTERSEQUENCE); } --- 714,720 ---- { static const char *const list_ALTERSEQUENCE[] = {"INCREMENT", "MINVALUE", "MAXVALUE", "RESTART", "NO", "CACHE", "CYCLE", ! "SET SCHEMA", "RENAME TO", NULL}; COMPLETE_WITH_LIST(list_ALTERSEQUENCE); } *************** *** 728,733 **** --- 728,742 ---- COMPLETE_WITH_LIST(list_ALTERSEQUENCE2); } + /* ALTER VIEW */ + else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && + pg_strcasecmp(prev2_wd, "VIEW") == 0) + { + static const char *const list_ALTERVIEW[] = + {"RENAME TO"}; + + COMPLETE_WITH_LIST(list_ALTERVIEW); + } /* ALTER TRIGGER , add ON */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && pg_strcasecmp(prev2_wd, "TRIGGER") == 0) Index: src/include/commands/tablecmds.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/commands/tablecmds.h,v retrieving revision 1.33 diff -c -r1.33 tablecmds.h *** src/include/commands/tablecmds.h 11 May 2007 20:17:10 -0000 1.33 --- src/include/commands/tablecmds.h 1 Jul 2007 19:42:37 -0000 *************** *** 43,49 **** bool recursing); extern void renamerel(Oid myrelid, ! const char *newrelname); extern void find_composite_type_dependencies(Oid typeOid, const char *origTblName, --- 43,50 ---- bool recursing); extern void renamerel(Oid myrelid, ! const char *newrelname, ! ObjectType newreltype); extern void find_composite_type_dependencies(Oid typeOid, const char *origTblName, --ZmUaFz6apKcXQszQ Content-Type: text/sgml; charset=us-ascii Content-Disposition: attachment; filename="alter_view.sgml" ALTER VIEW SQL - Language Statements ALTER VIEW change a view ALTER VIEW ALTER VIEW name RENAME TO newname Description ALTER VIEW changes the definition of a view. You must own the view to use ALTER VIEW. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the view's schema. (These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the view. However, a superuser can alter ownership of any view anyway.) Parameters name The name (optionally schema-qualified) of an existing view. newname The new name of the view. Examples To rename the view foo to bar: ALTER VIEW foo RENAME TO bar; Compatibility There is no ALTER VIEW statement in the SQL standard. See Also --ZmUaFz6apKcXQszQ--