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;
/* ----------------------
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
