Обсуждение: drop if exists
Ther attached patch is for comment. It implements "drop if exists" as
has recently been discussed. Illustration:
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#
If the patch is acceptable I will work up some documentation and
regression tests.
cheers
andrew
Index: src/backend/commands/conversioncmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/conversioncmds.c,v
retrieving revision 1.23
diff -c -r1.23 conversioncmds.c
*** src/backend/commands/conversioncmds.c 15 Oct 2005 02:49:15 -0000 1.23
--- src/backend/commands/conversioncmds.c 14 Nov 2005 14:09:52 -0000
***************
*** 98,113 ****
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
ConversionDrop(conversionOid, behavior);
}
--- 98,118 ----
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior, bool missing_ok)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
! }
ConversionDrop(conversionOid, behavior);
}
Index: src/backend/commands/schemacmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/schemacmds.c,v
retrieving revision 1.35
diff -c -r1.35 schemacmds.c
*** src/backend/commands/schemacmds.c 15 Oct 2005 02:49:15 -0000 1.35
--- src/backend/commands/schemacmds.c 14 Nov 2005 14:09:52 -0000
***************
*** 147,153 ****
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior)
{
char *namespaceName;
Oid namespaceId;
--- 147,153 ----
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior, bool missing_ok)
{
char *namespaceName;
Oid namespaceId;
***************
*** 163,171 ****
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
--- 163,176 ----
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
! }
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.82
diff -c -r1.82 typecmds.c
*** src/backend/commands/typecmds.c 18 Oct 2005 01:06:24 -0000 1.82
--- src/backend/commands/typecmds.c 14 Nov 2005 14:09:54 -0000
***************
*** 398,404 ****
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 398,404 ----
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 414,423 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 414,428 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
***************
*** 779,785 ****
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 784,790 ----
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 796,805 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 801,815 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.511
diff -c -r2.511 gram.y
*** src/backend/parser/gram.y 23 Sep 2005 22:25:25 -0000 2.511
--- src/backend/parser/gram.y 14 Nov 2005 14:10:00 -0000
***************
*** 362,368 ****
HANDLER HAVING HEADER HOLD HOUR_P
! ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 362,368 ----
HANDLER HAVING HEADER HOLD HOUR_P
! IF ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
***************
*** 2822,2837 ****
*
*****************************************************************************/
! DropStmt: DROP drop_type any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
--- 2822,2848 ----
*
*****************************************************************************/
! DropStmt: DROP drop_type IF EXISTS any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
+ n->missing_ok = TRUE;
+ n->objects = $5;
+ n->behavior = $6;
+ $$ = (Node *)n;
+ }
+ | DROP drop_type any_name_list opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = $2;
+ n->missing_ok = FALSE;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
+
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
***************
*** 8149,8154 ****
--- 8160,8166 ----
| HEADER
| HOLD
| HOUR_P
+ | IF
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.166
diff -c -r1.166 keywords.c
*** src/backend/parser/keywords.c 15 Oct 2005 02:49:22 -0000 1.166
--- src/backend/parser/keywords.c 14 Nov 2005 14:10:00 -0000
***************
*** 160,165 ****
--- 160,166 ----
{"header", HEADER},
{"hold", HOLD},
{"hour", HOUR_P},
+ {"if",IF},
{"ilike", ILIKE},
{"immediate", IMMEDIATE},
{"immutable", IMMUTABLE},
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.245
diff -c -r1.245 utility.c
*** src/backend/tcop/utility.c 15 Oct 2005 02:49:27 -0000 1.245
--- src/backend/tcop/utility.c 14 Nov 2005 14:10:00 -0000
***************
*** 147,154 ****
Assert(false); /* Should be impossible */
}
! static void
! CheckDropPermissions(RangeVar *rel, char rightkind)
{
Oid relOid;
HeapTuple tuple;
--- 147,161 ----
Assert(false); /* Should be impossible */
}
! /*
! * returns false if missing_ok is true and the object does not exist,
! * true if object exists and permissions are OK,
! * errors otherwise
! *
! */
!
! static bool
! CheckDropPermissions(RangeVar *rel, char rightkind, bool missing_ok)
{
Oid relOid;
HeapTuple tuple;
***************
*** 156,162 ****
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! DropErrorMsgNonExistent(rel, rightkind);
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
--- 163,174 ----
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! {
! if (!missing_ok)
! DropErrorMsgNonExistent(rel, rightkind);
! else
! return false;
! }
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
***************
*** 183,188 ****
--- 195,202 ----
rel->relname)));
ReleaseSysCache(tuple);
+
+ return true;
}
/*
***************
*** 528,558 ****
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_RELATION);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_SEQUENCE);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_VIEW);
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_INDEX);
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior);
break;
case OBJECT_DOMAIN:
--- 542,577 ----
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_RELATION,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_SEQUENCE,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_VIEW,
! stmt->missing_ok))
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_INDEX,
! stmt->missing_ok))
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_DOMAIN:
***************
*** 560,570 ****
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior);
break;
case OBJECT_SCHEMA:
--- 579,591 ----
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_SCHEMA:
***************
*** 572,578 ****
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior);
break;
default:
--- 593,600 ----
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior,
! stmt->missing_ok);
break;
default:
Index: src/include/commands/conversioncmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/conversioncmds.h,v
retrieving revision 1.10
diff -c -r1.10 conversioncmds.h
*** src/include/commands/conversioncmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/conversioncmds.h 14 Nov 2005 14:10:02 -0000
***************
*** 18,24 ****
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name, DropBehavior behavior);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
--- 18,25 ----
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name,
! DropBehavior behavior, bool missing_ok);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
Index: src/include/commands/schemacmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/schemacmds.h,v
retrieving revision 1.10
diff -c -r1.10 schemacmds.h
*** src/include/commands/schemacmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/schemacmds.h 14 Nov 2005 14:10:02 -0000
***************
*** 19,25 ****
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
--- 19,25 ----
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
Index: src/include/commands/typecmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/typecmds.h,v
retrieving revision 1.14
diff -c -r1.14 typecmds.h
*** src/include/commands/typecmds.h 15 Oct 2005 02:49:44 -0000 1.14
--- src/include/commands/typecmds.h 14 Nov 2005 14:10:02 -0000
***************
*** 20,29 ****
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
--- 20,29 ----
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior, bool missing_ok);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.292
diff -c -r1.292 parsenodes.h
*** src/include/nodes/parsenodes.h 26 Oct 2005 19:21:55 -0000 1.292
--- src/include/nodes/parsenodes.h 14 Nov 2005 14:10:04 -0000
***************
*** 1278,1283 ****
--- 1278,1284 ----
List *objects; /* list of sublists of names (as Values) */
ObjectType removeType; /* object type */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} DropStmt;
/* ----------------------
On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote: > > Ther attached patch is for comment. It implements "drop if exists" > as has recently been discussed. Illustration: Nifty! Thanks for working this up, Andrew! > andrew=# drop table blurflx; > ERROR: table "blurflx" does not exist > andrew=# drop table if exists blurflx; > DROP TABLE I'm not sure what other DBMS' return in this situation (and kindly ignore this suggestion if it's specified or otherwise determined), but perhaps the output could be TABLE "blurlx" DOES NOT EXIST (without the ERROR) or something more informative, rather than DROP TABLE. It reminds me of the old behavior of outputting COMMIT even in the case of transaction failure. I find the current behavior of outputting ROLLBACK in the case of transaction failure more useful. Michael Glaesemann grzm myrealbox com
> -----Original Message----- > From: pgsql-patches-owner@postgresql.org > [mailto:pgsql-patches-owner@postgresql.org] On Behalf Of > Michael Glaesemann > Sent: 14 November 2005 14:54 > To: Andrew Dunstan > Cc: Patches (PostgreSQL) > Subject: Re: [PATCHES] drop if exists > > > On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote: > > > > > Ther attached patch is for comment. It implements "drop if exists" > > as has recently been discussed. Illustration: > > Nifty! Thanks for working this up, Andrew! > > > > andrew=# drop table blurflx; > > ERROR: table "blurflx" does not exist > > andrew=# drop table if exists blurflx; > > DROP TABLE > > I'm not sure what other DBMS' return in this situation (and kindly > ignore this suggestion if it's specified or otherwise determined), > but perhaps the output could be TABLE "blurlx" DOES NOT EXIST > (without the ERROR) or something more informative, rather than DROP > TABLE. It reminds me of the old behavior of outputting COMMIT > even in > the case of transaction failure. I find the current behavior of > outputting ROLLBACK in the case of transaction failure more useful. Yes, a notice would certainly be nice: andrew=# drop table blurflx; ERROR: table "blurflx" does not exist andrew=# drop table if exists blurflx; NOTICE: table "blurflx" does not exist DROP TABLE Regards, Dave.
Andrew Dunstan <andrew@dunslane.net> writes:
> andrew=# drop table blurflx;
> ERROR: table "blurflx" does not exist
> andrew=# drop table if exists blurflx;
> DROP TABLE
If I read MySQL's documentation correctly, they emit a NOTE (equivalent
of a NOTICE message I suppose) when IF EXISTS does nothing because the
table doesn't exist. Seems like we should do likewise --- your second
example here seems actively misleading. That is, I'd rather see
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE
regards, tom lane
OK, now it looks like this:
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#
revised patch attached.
cheers
andrew
Tom Lane wrote:
>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>andrew=# drop table blurflx;
>>ERROR: table "blurflx" does not exist
>>andrew=# drop table if exists blurflx;
>>DROP TABLE
>>
>>
>
>If I read MySQL's documentation correctly, they emit a NOTE (equivalent
>of a NOTICE message I suppose) when IF EXISTS does nothing because the
>table doesn't exist. Seems like we should do likewise --- your second
>example here seems actively misleading. That is, I'd rather see
>
>andrew=# drop table if exists blurflx;
>NOTICE: table "blurflx" does not exist, skipping
>DROP TABLE
>
>
> regards, tom lane
>
>
>
Index: src/backend/commands/conversioncmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/conversioncmds.c,v
retrieving revision 1.23
diff -c -r1.23 conversioncmds.c
*** src/backend/commands/conversioncmds.c 15 Oct 2005 02:49:15 -0000 1.23
--- src/backend/commands/conversioncmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 98,113 ****
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
ConversionDrop(conversionOid, behavior);
}
--- 98,128 ----
* DROP CONVERSION
*/
void
! DropConversionCommand(List *name, DropBehavior behavior, bool missing_ok)
{
Oid conversionOid;
conversionOid = FindConversionByName(name);
if (!OidIsValid(conversionOid))
! {
! if (! missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist",
! NameListToString(name))));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("conversion \"%s\" does not exist, skipping",
! NameListToString(name))));
! }
!
! Assert(missing_ok);
! return;
! }
ConversionDrop(conversionOid, behavior);
}
Index: src/backend/commands/schemacmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/schemacmds.c,v
retrieving revision 1.35
diff -c -r1.35 schemacmds.c
*** src/backend/commands/schemacmds.c 15 Oct 2005 02:49:15 -0000 1.35
--- src/backend/commands/schemacmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 147,153 ****
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior)
{
char *namespaceName;
Oid namespaceId;
--- 147,153 ----
* Removes a schema.
*/
void
! RemoveSchema(List *names, DropBehavior behavior, bool missing_ok)
{
char *namespaceName;
Oid namespaceId;
***************
*** 163,171 ****
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
--- 163,186 ----
CStringGetDatum(namespaceName),
0, 0, 0);
if (!OidIsValid(namespaceId))
! {
! if (!missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist", namespaceName)));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_SCHEMA),
! errmsg("schema \"%s\" does not exist, skipping",
! namespaceName)));
! }
!
! Assert(missing_ok);
! return;
! }
/* Permission check */
if (!pg_namespace_ownercheck(namespaceId, GetUserId()))
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.82
diff -c -r1.82 typecmds.c
*** src/backend/commands/typecmds.c 18 Oct 2005 01:06:24 -0000 1.82
--- src/backend/commands/typecmds.c 14 Nov 2005 17:00:05 -0000
***************
*** 398,404 ****
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 398,404 ----
* Removes a datatype.
*/
void
! RemoveType(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 414,423 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 414,438 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (!missing_ok)
! {
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
! else
! {
! ereport(NOTICE,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist, skipping",
! TypeNameToString(typename))));
! }
!
! Assert(missing_ok);
! return;
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
***************
*** 779,785 ****
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior)
{
TypeName *typename;
Oid typeoid;
--- 794,800 ----
* This is identical to RemoveType except we insist it be a domain.
*/
void
! RemoveDomain(List *names, DropBehavior behavior, bool missing_ok)
{
TypeName *typename;
Oid typeoid;
***************
*** 796,805 ****
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
--- 811,825 ----
/* Use LookupTypeName here so that shell types can be removed. */
typeoid = LookupTypeName(typename);
if (!OidIsValid(typeoid))
! {
! if (missing_ok)
! return;
! else
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_OBJECT),
! errmsg("type \"%s\" does not exist",
! TypeNameToString(typename))));
! }
tup = SearchSysCache(TYPEOID,
ObjectIdGetDatum(typeoid),
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.511
diff -c -r2.511 gram.y
*** src/backend/parser/gram.y 23 Sep 2005 22:25:25 -0000 2.511
--- src/backend/parser/gram.y 14 Nov 2005 17:00:13 -0000
***************
*** 173,179 ****
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait
%type <boolean> like_including_defaults
--- 173,179 ----
%type <ival> opt_lock lock_type cast_context
%type <boolean> opt_force opt_or_replace
opt_grant_grant_option opt_grant_admin_option
! opt_nowait
%type <boolean> like_including_defaults
***************
*** 362,368 ****
HANDLER HAVING HEADER HOLD HOUR_P
! ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 362,368 ----
HANDLER HAVING HEADER HOLD HOUR_P
! IF ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
***************
*** 2822,2837 ****
*
*****************************************************************************/
! DropStmt: DROP drop_type any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
--- 2822,2848 ----
*
*****************************************************************************/
! DropStmt: DROP drop_type IF EXISTS any_name_list opt_drop_behavior
{
DropStmt *n = makeNode(DropStmt);
n->removeType = $2;
+ n->missing_ok = TRUE;
+ n->objects = $5;
+ n->behavior = $6;
+ $$ = (Node *)n;
+ }
+ | DROP drop_type any_name_list opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+ n->removeType = $2;
+ n->missing_ok = FALSE;
n->objects = $3;
n->behavior = $4;
$$ = (Node *)n;
}
;
+
drop_type: TABLE { $$ = OBJECT_TABLE; }
| SEQUENCE { $$ = OBJECT_SEQUENCE; }
| VIEW { $$ = OBJECT_VIEW; }
***************
*** 8149,8154 ****
--- 8160,8166 ----
| HEADER
| HOLD
| HOUR_P
+ | IF
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.166
diff -c -r1.166 keywords.c
*** src/backend/parser/keywords.c 15 Oct 2005 02:49:22 -0000 1.166
--- src/backend/parser/keywords.c 14 Nov 2005 17:00:13 -0000
***************
*** 160,165 ****
--- 160,166 ----
{"header", HEADER},
{"hold", HOLD},
{"hour", HOUR_P},
+ {"if",IF},
{"ilike", ILIKE},
{"immediate", IMMEDIATE},
{"immutable", IMMUTABLE},
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.245
diff -c -r1.245 utility.c
*** src/backend/tcop/utility.c 15 Oct 2005 02:49:27 -0000 1.245
--- src/backend/tcop/utility.c 14 Nov 2005 17:00:15 -0000
***************
*** 67,72 ****
--- 67,73 ----
char kind;
int nonexistent_code;
const char *nonexistent_msg;
+ const char *skipping_msg;
const char *nota_msg;
const char *drophint_msg;
};
***************
*** 75,100 ****
--- 76,106 ----
{RELKIND_RELATION,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("table \"%s\" does not exist"),
+ gettext_noop("table \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a table"),
gettext_noop("Use DROP TABLE to remove a table.")},
{RELKIND_SEQUENCE,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("sequence \"%s\" does not exist"),
+ gettext_noop("sequence \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a sequence"),
gettext_noop("Use DROP SEQUENCE to remove a sequence.")},
{RELKIND_VIEW,
ERRCODE_UNDEFINED_TABLE,
gettext_noop("view \"%s\" does not exist"),
+ gettext_noop("view \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a view"),
gettext_noop("Use DROP VIEW to remove a view.")},
{RELKIND_INDEX,
ERRCODE_UNDEFINED_OBJECT,
gettext_noop("index \"%s\" does not exist"),
+ gettext_noop("index \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not an index"),
gettext_noop("Use DROP INDEX to remove an index.")},
{RELKIND_COMPOSITE_TYPE,
ERRCODE_UNDEFINED_OBJECT,
gettext_noop("type \"%s\" does not exist"),
+ gettext_noop("type \"%s\" does not exist, skipping"),
gettext_noop("\"%s\" is not a type"),
gettext_noop("Use DROP TYPE to remove a type.")},
{'\0', 0, NULL, NULL, NULL}
***************
*** 132,154 ****
* non-existent relation
*/
static void
! DropErrorMsgNonExistent(RangeVar *rel, char rightkind)
{
const struct msgstrings *rentry;
for (rentry = msgstringarray; rentry->kind != '\0'; rentry++)
{
if (rentry->kind == rightkind)
! ereport(ERROR,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->nonexistent_msg, rel->relname)));
}
! Assert(false); /* Should be impossible */
}
! static void
! CheckDropPermissions(RangeVar *rel, char rightkind)
{
Oid relOid;
HeapTuple tuple;
--- 138,179 ----
* non-existent relation
*/
static void
! DropErrorMsgNonExistent(RangeVar *rel, char rightkind, bool missing_ok)
{
const struct msgstrings *rentry;
for (rentry = msgstringarray; rentry->kind != '\0'; rentry++)
{
if (rentry->kind == rightkind)
! {
! if (! missing_ok)
! {
! ereport(ERROR,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->nonexistent_msg, rel->relname)));
! }
! else
! {
! ereport(NOTICE,
! (errcode(rentry->nonexistent_code),
! errmsg(rentry->skipping_msg, rel->relname)));
! break;
! }
! }
}
! Assert(missing_ok); /* Should be impossible to get here otherwise */
}
! /*
! * returns false if missing_ok is true and the object does not exist,
! * true if object exists and permissions are OK,
! * errors otherwise
! *
! */
!
! static bool
! CheckDropPermissions(RangeVar *rel, char rightkind, bool missing_ok)
{
Oid relOid;
HeapTuple tuple;
***************
*** 156,162 ****
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! DropErrorMsgNonExistent(rel, rightkind);
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
--- 181,192 ----
relOid = RangeVarGetRelid(rel, true);
if (!OidIsValid(relOid))
! {
! DropErrorMsgNonExistent(rel, rightkind, missing_ok);
!
! Assert(missing_ok);
! return false;
! }
tuple = SearchSysCache(RELOID,
ObjectIdGetDatum(relOid),
***************
*** 183,188 ****
--- 213,220 ----
rel->relname)));
ReleaseSysCache(tuple);
+
+ return true;
}
/*
***************
*** 528,558 ****
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_RELATION);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_SEQUENCE);
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_VIEW);
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! CheckDropPermissions(rel, RELKIND_INDEX);
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior);
break;
case OBJECT_DOMAIN:
--- 560,595 ----
{
case OBJECT_TABLE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_RELATION,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_SEQUENCE:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_SEQUENCE,
! stmt->missing_ok))
! RemoveRelation(rel, stmt->behavior);
break;
case OBJECT_VIEW:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_VIEW,
! stmt->missing_ok))
! RemoveView(rel, stmt->behavior);
break;
case OBJECT_INDEX:
rel = makeRangeVarFromNameList(names);
! if (CheckDropPermissions(rel, RELKIND_INDEX,
! stmt->missing_ok))
! RemoveIndex(rel, stmt->behavior);
break;
case OBJECT_TYPE:
/* RemoveType does its own permissions checks */
! RemoveType(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_DOMAIN:
***************
*** 560,570 ****
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior);
break;
case OBJECT_SCHEMA:
--- 597,609 ----
/*
* RemoveDomain does its own permissions checks
*/
! RemoveDomain(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_CONVERSION:
! DropConversionCommand(names, stmt->behavior,
! stmt->missing_ok);
break;
case OBJECT_SCHEMA:
***************
*** 572,578 ****
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior);
break;
default:
--- 611,618 ----
/*
* RemoveSchema does its own permissions checks
*/
! RemoveSchema(names, stmt->behavior,
! stmt->missing_ok);
break;
default:
Index: src/include/commands/conversioncmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/conversioncmds.h,v
retrieving revision 1.10
diff -c -r1.10 conversioncmds.h
*** src/include/commands/conversioncmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/conversioncmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 18,24 ****
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name, DropBehavior behavior);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
--- 18,25 ----
#include "nodes/parsenodes.h"
extern void CreateConversionCommand(CreateConversionStmt *parsetree);
! extern void DropConversionCommand(List *conversion_name,
! DropBehavior behavior, bool missing_ok);
extern void RenameConversion(List *name, const char *newname);
extern void AlterConversionOwner(List *name, Oid newOwnerId);
Index: src/include/commands/schemacmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/schemacmds.h,v
retrieving revision 1.10
diff -c -r1.10 schemacmds.h
*** src/include/commands/schemacmds.h 28 Jun 2005 05:09:12 -0000 1.10
--- src/include/commands/schemacmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 19,25 ****
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
--- 19,25 ----
extern void CreateSchemaCommand(CreateSchemaStmt *parsetree);
! extern void RemoveSchema(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveSchemaById(Oid schemaOid);
extern void RenameSchema(const char *oldname, const char *newname);
Index: src/include/commands/typecmds.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/typecmds.h,v
retrieving revision 1.14
diff -c -r1.14 typecmds.h
*** src/include/commands/typecmds.h 15 Oct 2005 02:49:44 -0000 1.14
--- src/include/commands/typecmds.h 14 Nov 2005 17:00:18 -0000
***************
*** 20,29 ****
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
--- 20,29 ----
#define DEFAULT_TYPDELIM ','
extern void DefineType(List *names, List *parameters);
! extern void RemoveType(List *names, DropBehavior behavior, bool missing_ok);
extern void RemoveTypeById(Oid typeOid);
extern void DefineDomain(CreateDomainStmt *stmt);
! extern void RemoveDomain(List *names, DropBehavior behavior, bool missing_ok);
extern Oid DefineCompositeType(const RangeVar *typevar, List *coldeflist);
extern void AlterDomainDefault(List *names, Node *defaultRaw);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.292
diff -c -r1.292 parsenodes.h
*** src/include/nodes/parsenodes.h 26 Oct 2005 19:21:55 -0000 1.292
--- src/include/nodes/parsenodes.h 14 Nov 2005 17:00:20 -0000
***************
*** 1278,1283 ****
--- 1278,1284 ----
List *objects; /* list of sublists of names (as Values) */
ObjectType removeType; /* object type */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} DropStmt;
/* ----------------------
Removed from queue. Andrew is committing it. --------------------------------------------------------------------------- Andrew Dunstan wrote: > > OK, now it looks like this: > > andrew=# drop table blurflx; > ERROR: table "blurflx" does not exist > andrew=# drop table if exists blurflx; > NOTICE: table "blurflx" does not exist, skipping > DROP TABLE > andrew=# create table blurflx ( x text); > CREATE TABLE > andrew=# drop table if exists blurflx; > DROP TABLE > andrew=# drop table blurflx; > ERROR: table "blurflx" does not exist > andrew=# > > revised patch attached. > > cheers > > andrew > > Tom Lane wrote: > > >Andrew Dunstan <andrew@dunslane.net> writes: > > > > > >>andrew=# drop table blurflx; > >>ERROR: table "blurflx" does not exist > >>andrew=# drop table if exists blurflx; > >>DROP TABLE > >> > >> > > > >If I read MySQL's documentation correctly, they emit a NOTE (equivalent > >of a NOTICE message I suppose) when IF EXISTS does nothing because the > >table doesn't exist. Seems like we should do likewise --- your second > >example here seems actively misleading. That is, I'd rather see > > > >andrew=# drop table if exists blurflx; > >NOTICE: table "blurflx" does not exist, skipping > >DROP TABLE > > > > > > regards, tom lane > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- 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
Will we get this functionality for ALL objects? Bruce Momjian wrote: > Removed from queue. Andrew is committing it. > > --------------------------------------------------------------------------- > > Andrew Dunstan wrote: > >>OK, now it looks like this: >> >>andrew=# drop table blurflx; >>ERROR: table "blurflx" does not exist >>andrew=# drop table if exists blurflx; >>NOTICE: table "blurflx" does not exist, skipping >>DROP TABLE >>andrew=# create table blurflx ( x text); >>CREATE TABLE >>andrew=# drop table if exists blurflx; >>DROP TABLE >>andrew=# drop table blurflx; >>ERROR: table "blurflx" does not exist >>andrew=# >> >>revised patch attached. >> >>cheers >> >>andrew >> >>Tom Lane wrote: >> >> >>>Andrew Dunstan <andrew@dunslane.net> writes: >>> >>> >>> >>>>andrew=# drop table blurflx; >>>>ERROR: table "blurflx" does not exist >>>>andrew=# drop table if exists blurflx; >>>>DROP TABLE >>>> >>>> >>> >>>If I read MySQL's documentation correctly, they emit a NOTE (equivalent >>>of a NOTICE message I suppose) when IF EXISTS does nothing because the >>>table doesn't exist. Seems like we should do likewise --- your second >>>example here seems actively misleading. That is, I'd rather see >>> >>>andrew=# drop table if exists blurflx; >>>NOTICE: table "blurflx" does not exist, skipping >>>DROP TABLE >>> >>> >>> regards, tom lane >>> >>> >>> > > > >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org > >
Christopher Kings-Lynne said: > Will we get this functionality for ALL objects? > The patch does these: table, view, index, sequence, schema, type, domain, and conversion. The reason is that these are all dealt with using the same bit of the grammar, and the first 4 are pretty much completely done by the same code. I think anything else will have to be done individually, although the pattern can be copied. Perhaps we should take bids on what should/should not be covered. cheers andrew
> I think anything else will have to be done individually, although the > pattern can be copied. > > Perhaps we should take bids on what should/should not be covered. Everything should be covered, otherwise it's just annoying for users... Chris
On Nov 17, 2005, at 11:45 , Christopher Kings-Lynne wrote: >> I think anything else will have to be done individually, although the >> pattern can be copied. >> Perhaps we should take bids on what should/should not be covered. > > Everything should be covered, otherwise it's just annoying for > users... Including objects that already have CREATE OR REPLACE? Michael Glaesemann grzm myrealbox com
> Including objects that already have CREATE OR REPLACE? I assume so - CREATE OR REPLACE doesn't drop things - only creates or replaces them. Chris
Christopher Kings-Lynne wrote: >> I think anything else will have to be done individually, although the >> pattern can be copied. >> >> Perhaps we should take bids on what should/should not be covered. > > > Everything should be covered, otherwise it's just annoying for users... Well, that's arguably more than I originally signed up for ;-) See http://archives.postgresql.org/pgsql-hackers/2005-10/msg00632.php There are currently DROP commends for the following 21 objects (according to the docs). AGGREGATE CAST CONVERSION DATABASE DOMAIN FUNCTION GROUP INDEX LANGUAGE OPERATOR OPERATOR CLASS ROLE RULE SCHEMA SEQUENCE TABLE TABLESPACE TRIGGER TYPE USER VIEW If the consensus is to add this to all of them, then I propose to apply the patch I have (with a slight fix for an oversight in the case of domains, plus docs and tests) for the 8 cases and start working on the remaining 13 as time permits. To be honest, I have not even looked at those 13 cases. One motivation for this, besides general utility, is to ease MySQL migrations, btw, and AFAICT they only have three DROP commands and only two of them (TABLE and DATABASE) have IF EXISTS - DROP INDEX does not for some reason - probably because it is actually mapped to an ALTER TABLE statement. cheers andrew
On Nov 17, 2005, at 11:51 , Christopher Kings-Lynne wrote: >> Including objects that already have CREATE OR REPLACE? > > I assume so - CREATE OR REPLACE doesn't drop things - only creates > or replaces them. Of course. Silly me :) Michael Glaesemann grzm myrealbox com
> If the consensus is to add this to all of them, then I propose to apply > the patch I have (with a slight fix for an oversight in the case of > domains, plus docs and tests) for the 8 cases and start working on the > remaining 13 as time permits. To be honest, I have not even looked at > those 13 cases. I agree. I can have a crack at the others as well. It's in my area of ability I hope ;) (Except grammar janking) Chris