From daea4d8eaee010f41e46bb98cd1b2da2f9fb75d9 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Sat, 19 Nov 2005 17:39:45 +0000 Subject: [PATCH] DROP objecttype IF EXISTS for the following objects: table view index sequence schema type domain conversion --- doc/src/sgml/ref/drop_conversion.sgml | 14 ++- doc/src/sgml/ref/drop_domain.sgml | 18 +++- doc/src/sgml/ref/drop_index.sgml | 14 ++- doc/src/sgml/ref/drop_schema.sgml | 18 +++- doc/src/sgml/ref/drop_sequence.sgml | 18 +++- doc/src/sgml/ref/drop_table.sgml | 18 +++- doc/src/sgml/ref/drop_type.sgml | 18 +++- doc/src/sgml/ref/drop_view.sgml | 18 +++- src/backend/commands/conversioncmds.c | 25 ++++-- src/backend/commands/schemacmds.c | 23 +++-- src/backend/commands/typecmds.c | 48 ++++++++--- src/backend/parser/gram.y | 23 +++-- src/backend/parser/keywords.c | 3 +- src/backend/tcop/utility.c | 78 ++++++++++++----- src/include/commands/conversioncmds.h | 5 +- src/include/commands/schemacmds.h | 4 +- src/include/commands/typecmds.h | 6 +- src/include/nodes/parsenodes.h | 3 +- src/test/regress/expected/drop_if_exists.out | 67 ++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 3 +- src/test/regress/sql/drop_if_exists.sql | 91 ++++++++++++++++++++ 22 files changed, 436 insertions(+), 81 deletions(-) create mode 100644 src/test/regress/expected/drop_if_exists.out create mode 100644 src/test/regress/sql/drop_if_exists.sql diff --git a/doc/src/sgml/ref/drop_conversion.sgml b/doc/src/sgml/ref/drop_conversion.sgml index 52d332e42e..185290cfb2 100644 --- a/doc/src/sgml/ref/drop_conversion.sgml +++ b/doc/src/sgml/ref/drop_conversion.sgml @@ -1,4 +1,4 @@ - + @@ -17,7 +17,7 @@ -DROP CONVERSION name [ CASCADE | RESTRICT ] +DROP CONVERSION [ IF EXISTS ] name [ CASCADE | RESTRICT ] @@ -34,6 +34,16 @@ DROP CONVERSION name [ CASCADE | RESTRICT ] Parameters + + IF EXISTS + + + Do not throw an error if the conversion does not exist. + A notice is issued in this case. + + + + name diff --git a/doc/src/sgml/ref/drop_domain.sgml b/doc/src/sgml/ref/drop_domain.sgml index 9a2eb781f1..09f63873fd 100644 --- a/doc/src/sgml/ref/drop_domain.sgml +++ b/doc/src/sgml/ref/drop_domain.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ] +DROP DOMAIN [IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -37,6 +37,16 @@ DROP DOMAIN name [, ...] [ CASCADE Parameters + + IF EXISTS + + + Do not throw an error if the domain does not exist. A notice is issued + in this case. + + + + name @@ -84,7 +94,9 @@ DROP DOMAIN box; Compatibility - This command conforms to the SQL standard. + This command conforms to the SQL standard, except for the + IF EXISTS option, which is a PostgreSQL + extension. diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 2fb009eff1..17ba565c64 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP INDEX name [, ...] [ CASCADE | RESTRICT ] +DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -38,6 +38,16 @@ DROP INDEX name [, ...] [ CASCADE | Parameters + + IF EXISTS + + + Do not throw an error if the index does not exist. A notice is issued + in this case. + + + + name diff --git a/doc/src/sgml/ref/drop_schema.sgml b/doc/src/sgml/ref/drop_schema.sgml index f36b3794d2..34deba02a7 100644 --- a/doc/src/sgml/ref/drop_schema.sgml +++ b/doc/src/sgml/ref/drop_schema.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ] +DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -42,6 +42,16 @@ DROP SCHEMA name [, ...] [ CASCADE Parameters + + IF EXISTS + + + Do not throw an error if the schema does not exist. A notice is issued + in this case. + + + + name @@ -92,7 +102,9 @@ DROP SCHEMA mystuff CASCADE; DROP SCHEMA is fully conforming with the SQL standard, except that the standard only allows one schema to be - dropped per command. + dropped per command, and apart from the + IF EXISTS option, which is a PostgreSQL + extension. diff --git a/doc/src/sgml/ref/drop_sequence.sgml b/doc/src/sgml/ref/drop_sequence.sgml index 11f60b268a..074f4e7216 100644 --- a/doc/src/sgml/ref/drop_sequence.sgml +++ b/doc/src/sgml/ref/drop_sequence.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ] +DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -36,6 +36,16 @@ DROP SEQUENCE name [, ...] [ CASCAD Parameters + + IF EXISTS + + + Do not throw an error if the sequence does not exist. A notice is issued + in this case. + + + + name @@ -84,7 +94,9 @@ DROP SEQUENCE serial; DROP SEQUENCE conforms to the SQL standard, except that the standard only allows one - sequence to be dropped per command. + sequence to be dropped per command, and apart from the + IF EXISTS option, which is a PostgreSQL + extension. diff --git a/doc/src/sgml/ref/drop_table.sgml b/doc/src/sgml/ref/drop_table.sgml index 40cc8167ef..8e7cedd71d 100644 --- a/doc/src/sgml/ref/drop_table.sgml +++ b/doc/src/sgml/ref/drop_table.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP TABLE name [, ...] [ CASCADE | RESTRICT ] +DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -48,6 +48,16 @@ DROP TABLE name [, ...] [ CASCADE | Parameters + + IF EXISTS + + + Do not throw an error if the table does not exist. A notice is issued + in this case. + + + + name @@ -97,7 +107,9 @@ DROP TABLE films, distributors; This command conforms to the SQL standard, except that the standard only - allows one table to be dropped per command. + allows one table to be dropped per command, and apart from the + IF EXISTS option, which is a PostgreSQL + extension. diff --git a/doc/src/sgml/ref/drop_type.sgml b/doc/src/sgml/ref/drop_type.sgml index da0f6bc8b6..3f233a56c5 100644 --- a/doc/src/sgml/ref/drop_type.sgml +++ b/doc/src/sgml/ref/drop_type.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP TYPE name [, ...] [ CASCADE | RESTRICT ] +DROP TYPE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -37,6 +37,16 @@ DROP TYPE name [, ...] [ CASCADE | Parameters + + IF EXISTS + + + Do not throw an error if the type does not exist. A notice is issued + in this case. + + + + name @@ -84,7 +94,9 @@ DROP TYPE box; This command is similar to the corresponding command in the SQL - standard, but note that the CREATE TYPE command + standard, aapart from the IF EXISTS + option, which is a PostgreSQL extension. + But note that the CREATE TYPE command and the data type extension mechanisms in PostgreSQL differ from the SQL standard. diff --git a/doc/src/sgml/ref/drop_view.sgml b/doc/src/sgml/ref/drop_view.sgml index ee28e0ef5e..45a612114a 100644 --- a/doc/src/sgml/ref/drop_view.sgml +++ b/doc/src/sgml/ref/drop_view.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation -DROP VIEW name [, ...] [ CASCADE | RESTRICT ] +DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] @@ -37,6 +37,16 @@ DROP VIEW name [, ...] [ CASCADE | Parameters + + IF EXISTS + + + Do not throw an error if the view does not exist. A notice is issued + in this case. + + + + name @@ -84,7 +94,9 @@ DROP VIEW kinds; This command conforms to the SQL standard, except that the standard only - allows one view to be dropped per command. + allows one view to be dropped per command, and apart from the + IF EXISTS option, which is a PostgreSQL + extension. diff --git a/src/backend/commands/conversioncmds.c b/src/backend/commands/conversioncmds.c index 53b3f854ce..97abc9fc77 100644 --- a/src/backend/commands/conversioncmds.c +++ b/src/backend/commands/conversioncmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/conversioncmds.c,v 1.23 2005/10/15 02:49:15 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/conversioncmds.c,v 1.24 2005/11/19 17:39:44 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -98,16 +98,29 @@ CreateConversionCommand(CreateConversionStmt *stmt) * DROP CONVERSION */ void -DropConversionCommand(List *name, DropBehavior behavior) +DropConversionCommand(List *name, DropBehavior behavior, bool missing_ok) { Oid conversionOid; conversionOid = FindConversionByName(name); if (!OidIsValid(conversionOid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("conversion \"%s\" does not exist", - NameListToString(name)))); + { + if (! missing_ok) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("conversion \"%s\" does not exist", + NameListToString(name)))); + } + else + { + ereport(NOTICE, + (errmsg("conversion \"%s\" does not exist, skipping", + NameListToString(name)))); + } + + return; + } ConversionDrop(conversionOid, behavior); } diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index 56a3359a53..caa336d2c4 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.35 2005/10/15 02:49:15 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.36 2005/11/19 17:39:44 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -147,7 +147,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt) * Removes a schema. */ void -RemoveSchema(List *names, DropBehavior behavior) +RemoveSchema(List *names, DropBehavior behavior, bool missing_ok) { char *namespaceName; Oid namespaceId; @@ -163,9 +163,22 @@ RemoveSchema(List *names, DropBehavior behavior) CStringGetDatum(namespaceName), 0, 0, 0); if (!OidIsValid(namespaceId)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_SCHEMA), - errmsg("schema \"%s\" does not exist", namespaceName))); + { + if (!missing_ok) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_SCHEMA), + errmsg("schema \"%s\" does not exist", namespaceName))); + } + else + { + ereport(NOTICE, + (errmsg("schema \"%s\" does not exist, skipping", + namespaceName))); + } + + return; + } /* Permission check */ if (!pg_namespace_ownercheck(namespaceId, GetUserId())) diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 5cf51658ee..475c251b2f 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/typecmds.c,v 1.82 2005/10/18 01:06:24 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/typecmds.c,v 1.83 2005/11/19 17:39:44 adunstan Exp $ * * DESCRIPTION * The "DefineFoo" routines take the parse tree and pick out the @@ -398,7 +398,7 @@ DefineType(List *names, List *parameters) * Removes a datatype. */ void -RemoveType(List *names, DropBehavior behavior) +RemoveType(List *names, DropBehavior behavior, bool missing_ok) { TypeName *typename; Oid typeoid; @@ -414,10 +414,23 @@ RemoveType(List *names, DropBehavior behavior) /* 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)))); + { + if (!missing_ok) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("type \"%s\" does not exist", + TypeNameToString(typename)))); + } + else + { + ereport(NOTICE, + (errmsg("type \"%s\" does not exist, skipping", + TypeNameToString(typename)))); + } + + return; + } tup = SearchSysCache(TYPEOID, ObjectIdGetDatum(typeoid), @@ -779,7 +792,7 @@ DefineDomain(CreateDomainStmt *stmt) * This is identical to RemoveType except we insist it be a domain. */ void -RemoveDomain(List *names, DropBehavior behavior) +RemoveDomain(List *names, DropBehavior behavior, bool missing_ok) { TypeName *typename; Oid typeoid; @@ -796,10 +809,23 @@ RemoveDomain(List *names, DropBehavior behavior) /* 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)))); + { + if (!missing_ok) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("type \"%s\" does not exist", + TypeNameToString(typename)))); + } + else + { + ereport(NOTICE, + (errmsg("type \"%s\" does not exist, skipping", + TypeNameToString(typename)))); + } + + return; + } tup = SearchSysCache(TYPEOID, ObjectIdGetDatum(typeoid), diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5c9f33daf3..c91100abe4 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.512 2005/11/13 19:11:28 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.513 2005/11/19 17:39:44 adunstan Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -173,7 +173,7 @@ static void doNegateFloat(Value *v); %type opt_lock lock_type cast_context %type opt_force opt_or_replace opt_grant_grant_option opt_grant_admin_option - opt_nowait + opt_nowait %type like_including_defaults @@ -362,7 +362,7 @@ static void doNegateFloat(Value *v); HANDLER HAVING HEADER HOLD HOUR_P - ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT + IF_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 @@ -2818,20 +2818,32 @@ DropOpClassStmt: * * QUERY: * - * DROP itemtype itemname [, itemname ...] [ RESTRICT | CASCADE ] + * DROP itemtype [ IF EXISTS ] itemname [, itemname ...] + * [ RESTRICT | CASCADE ] * *****************************************************************************/ -DropStmt: DROP drop_type any_name_list opt_drop_behavior +DropStmt: DROP drop_type IF_P 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; } @@ -8159,6 +8171,7 @@ unreserved_keyword: | HEADER | HOLD | HOUR_P + | IF_P | IMMEDIATE | IMMUTABLE | IMPLICIT_P diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index f80b655280..797ea610db 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.166 2005/10/15 02:49:22 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.167 2005/11/19 17:39:44 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -160,6 +160,7 @@ static const ScanKeyword ScanKeywords[] = { {"header", HEADER}, {"hold", HOLD}, {"hour", HOUR_P}, + {"if",IF_P}, {"ilike", ILIKE}, {"immediate", IMMEDIATE}, {"immutable", IMMUTABLE}, diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 8e3c900053..75458bfce3 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.245 2005/10/15 02:49:27 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.246 2005/11/19 17:39:45 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -67,6 +67,7 @@ struct msgstrings char kind; int nonexistent_code; const char *nonexistent_msg; + const char *skipping_msg; const char *nota_msg; const char *drophint_msg; }; @@ -75,26 +76,31 @@ static const struct msgstrings msgstringarray[] = { {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,23 +138,40 @@ DropErrorMsgWrongType(char *relname, char wrongkind, char rightkind) * non-existent relation */ static void -DropErrorMsgNonExistent(RangeVar *rel, char rightkind) +DropErrorMsgNonExistent(RangeVar *rel, char rightkind, bool missing_ok) { 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))); + { + if (! missing_ok) + { + ereport(ERROR, + (errcode(rentry->nonexistent_code), + errmsg(rentry->nonexistent_msg, rel->relname))); + } + else + { + ereport(NOTICE, (errmsg(rentry->skipping_msg, rel->relname))); + break; + } + } } - Assert(false); /* Should be impossible */ + Assert(rentry->kind != '\0'); /* Should be impossible */ } -static void -CheckDropPermissions(RangeVar *rel, char rightkind) +/* + * 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,7 +179,10 @@ CheckDropPermissions(RangeVar *rel, char rightkind) relOid = RangeVarGetRelid(rel, true); if (!OidIsValid(relOid)) - DropErrorMsgNonExistent(rel, rightkind); + { + DropErrorMsgNonExistent(rel, rightkind, missing_ok); + return false; + } tuple = SearchSysCache(RELOID, ObjectIdGetDatum(relOid), @@ -183,6 +209,8 @@ CheckDropPermissions(RangeVar *rel, char rightkind) rel->relname))); ReleaseSysCache(tuple); + + return true; } /* @@ -528,31 +556,36 @@ ProcessUtility(Node *parsetree, { case OBJECT_TABLE: rel = makeRangeVarFromNameList(names); - CheckDropPermissions(rel, RELKIND_RELATION); - RemoveRelation(rel, stmt->behavior); + if (CheckDropPermissions(rel, RELKIND_RELATION, + stmt->missing_ok)) + RemoveRelation(rel, stmt->behavior); break; case OBJECT_SEQUENCE: rel = makeRangeVarFromNameList(names); - CheckDropPermissions(rel, RELKIND_SEQUENCE); - RemoveRelation(rel, stmt->behavior); + if (CheckDropPermissions(rel, RELKIND_SEQUENCE, + stmt->missing_ok)) + RemoveRelation(rel, stmt->behavior); break; case OBJECT_VIEW: rel = makeRangeVarFromNameList(names); - CheckDropPermissions(rel, RELKIND_VIEW); - RemoveView(rel, stmt->behavior); + if (CheckDropPermissions(rel, RELKIND_VIEW, + stmt->missing_ok)) + RemoveView(rel, stmt->behavior); break; case OBJECT_INDEX: rel = makeRangeVarFromNameList(names); - CheckDropPermissions(rel, RELKIND_INDEX); - RemoveIndex(rel, stmt->behavior); + 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); + RemoveType(names, stmt->behavior, + stmt->missing_ok); break; case OBJECT_DOMAIN: @@ -560,11 +593,13 @@ ProcessUtility(Node *parsetree, /* * RemoveDomain does its own permissions checks */ - RemoveDomain(names, stmt->behavior); + RemoveDomain(names, stmt->behavior, + stmt->missing_ok); break; case OBJECT_CONVERSION: - DropConversionCommand(names, stmt->behavior); + DropConversionCommand(names, stmt->behavior, + stmt->missing_ok); break; case OBJECT_SCHEMA: @@ -572,7 +607,8 @@ ProcessUtility(Node *parsetree, /* * RemoveSchema does its own permissions checks */ - RemoveSchema(names, stmt->behavior); + RemoveSchema(names, stmt->behavior, + stmt->missing_ok); break; default: diff --git a/src/include/commands/conversioncmds.h b/src/include/commands/conversioncmds.h index b64b361d27..f484ceaff2 100644 --- a/src/include/commands/conversioncmds.h +++ b/src/include/commands/conversioncmds.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/conversioncmds.h,v 1.10 2005/06/28 05:09:12 tgl Exp $ + * $PostgreSQL: pgsql/src/include/commands/conversioncmds.h,v 1.11 2005/11/19 17:39:45 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -18,7 +18,8 @@ #include "nodes/parsenodes.h" extern void CreateConversionCommand(CreateConversionStmt *parsetree); -extern void DropConversionCommand(List *conversion_name, DropBehavior behavior); +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); diff --git a/src/include/commands/schemacmds.h b/src/include/commands/schemacmds.h index 4528591b3c..f47baf6ef3 100644 --- a/src/include/commands/schemacmds.h +++ b/src/include/commands/schemacmds.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/schemacmds.h,v 1.10 2005/06/28 05:09:12 tgl Exp $ + * $PostgreSQL: pgsql/src/include/commands/schemacmds.h,v 1.11 2005/11/19 17:39:45 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -19,7 +19,7 @@ extern void CreateSchemaCommand(CreateSchemaStmt *parsetree); -extern void RemoveSchema(List *names, DropBehavior behavior); +extern void RemoveSchema(List *names, DropBehavior behavior, bool missing_ok); extern void RemoveSchemaById(Oid schemaOid); extern void RenameSchema(const char *oldname, const char *newname); diff --git a/src/include/commands/typecmds.h b/src/include/commands/typecmds.h index 53632906a1..f5f4ff0592 100644 --- a/src/include/commands/typecmds.h +++ b/src/include/commands/typecmds.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/typecmds.h,v 1.14 2005/10/15 02:49:44 momjian Exp $ + * $PostgreSQL: pgsql/src/include/commands/typecmds.h,v 1.15 2005/11/19 17:39:45 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -20,10 +20,10 @@ #define DEFAULT_TYPDELIM ',' extern void DefineType(List *names, List *parameters); -extern void RemoveType(List *names, DropBehavior behavior); +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); +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); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5adb79ef6b..8baed3e449 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.292 2005/10/26 19:21:55 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.293 2005/11/19 17:39:45 adunstan Exp $ * *------------------------------------------------------------------------- */ @@ -1278,6 +1278,7 @@ typedef struct DropStmt 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; /* ---------------------- diff --git a/src/test/regress/expected/drop_if_exists.out b/src/test/regress/expected/drop_if_exists.out new file mode 100644 index 0000000000..10feb3f986 --- /dev/null +++ b/src/test/regress/expected/drop_if_exists.out @@ -0,0 +1,67 @@ +-- +-- IF EXISTS tests +-- +-- table (will be really dropped at the end) +DROP TABLE test_exists; +ERROR: table "test_exists" does not exist +DROP TABLE IF EXISTS test_exists; +NOTICE: table "test_exists" does not exist, skipping +CREATE TABLE test_exists (a int, b text); +-- view +DROP VIEW test_view_exists; +ERROR: view "test_view_exists" does not exist +DROP VIEW IF EXISTS test_view_exists; +NOTICE: view "test_view_exists" does not exist, skipping +CREATE VIEW test_view_exists AS select * from test_exists; +DROP VIEW IF EXISTS test_view_exists; +DROP VIEW test_view_exists; +ERROR: view "test_view_exists" does not exist +-- index +DROP INDEX test_index_exists; +ERROR: index "test_index_exists" does not exist +DROP INDEX IF EXISTS test_index_exists; +NOTICE: index "test_index_exists" does not exist, skipping +CREATE INDEX test_index_exists on test_exists(a); +DROP INDEX IF EXISTS test_index_exists; +DROP INDEX test_index_exists; +ERROR: index "test_index_exists" does not exist +-- sequence +DROP SEQUENCE test_sequence_exists; +ERROR: sequence "test_sequence_exists" does not exist +DROP SEQUENCE IF EXISTS test_sequence_exists; +NOTICE: sequence "test_sequence_exists" does not exist, skipping +CREATE SEQUENCE test_sequence_exists; +DROP SEQUENCE IF EXISTS test_sequence_exists; +DROP SEQUENCE test_sequence_exists; +ERROR: sequence "test_sequence_exists" does not exist +-- schema +DROP SCHEMA test_schema_exists; +ERROR: schema "test_schema_exists" does not exist +DROP SCHEMA IF EXISTS test_schema_exists; +NOTICE: schema "test_schema_exists" does not exist, skipping +CREATE SCHEMA test_schema_exists; +DROP SCHEMA IF EXISTS test_schema_exists; +DROP SCHEMA test_schema_exists; +ERROR: schema "test_schema_exists" does not exist +-- type +DROP TYPE test_type_exists; +ERROR: type "test_type_exists" does not exist +DROP TYPE IF EXISTS test_type_exists; +NOTICE: type "test_type_exists" does not exist, skipping +CREATE type test_type_exists as (a int, b text); +DROP TYPE IF EXISTS test_type_exists; +DROP TYPE test_type_exists; +ERROR: type "test_type_exists" does not exist +-- domain +DROP DOMAIN test_domain_exists; +ERROR: type "test_domain_exists" does not exist +DROP DOMAIN IF EXISTS test_domain_exists; +NOTICE: type "test_domain_exists" does not exist, skipping +CREATE domain test_domain_exists as int not null check (value > 0); +DROP DOMAIN IF EXISTS test_domain_exists; +DROP DOMAIN test_domain_exists; +ERROR: type "test_domain_exists" does not exist +-- drop the table +DROP TABLE IF EXISTS test_exists; +DROP TABLE test_exists; +ERROR: table "test_exists" does not exist diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 3e52f6f558..d5777794bf 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -38,7 +38,7 @@ test: copy # ---------- # The third group of parallel test # ---------- -test: constraints triggers create_misc create_aggregate create_operator inherit vacuum +test: constraints triggers create_misc create_aggregate create_operator inherit vacuum drop_if_exists # Depends on the above test: create_index create_view diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bf9517fee2..dd60070433 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -1,4 +1,4 @@ -# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.28 2005/07/07 20:40:01 tgl Exp $ +# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.29 2005/11/19 17:39:45 adunstan Exp $ # This should probably be in an order similar to parallel_schedule. test: boolean test: char @@ -49,6 +49,7 @@ test: create_misc test: create_aggregate test: create_operator test: create_index +test: drop_if_exists test: inherit test: vacuum test: create_view diff --git a/src/test/regress/sql/drop_if_exists.sql b/src/test/regress/sql/drop_if_exists.sql new file mode 100644 index 0000000000..7addedaa73 --- /dev/null +++ b/src/test/regress/sql/drop_if_exists.sql @@ -0,0 +1,91 @@ +-- +-- IF EXISTS tests +-- + +-- table (will be really dropped at the end) + +DROP TABLE test_exists; + +DROP TABLE IF EXISTS test_exists; + +CREATE TABLE test_exists (a int, b text); + +-- view + +DROP VIEW test_view_exists; + +DROP VIEW IF EXISTS test_view_exists; + +CREATE VIEW test_view_exists AS select * from test_exists; + +DROP VIEW IF EXISTS test_view_exists; + +DROP VIEW test_view_exists; + +-- index + +DROP INDEX test_index_exists; + +DROP INDEX IF EXISTS test_index_exists; + +CREATE INDEX test_index_exists on test_exists(a); + +DROP INDEX IF EXISTS test_index_exists; + +DROP INDEX test_index_exists; + +-- sequence + +DROP SEQUENCE test_sequence_exists; + +DROP SEQUENCE IF EXISTS test_sequence_exists; + +CREATE SEQUENCE test_sequence_exists; + +DROP SEQUENCE IF EXISTS test_sequence_exists; + +DROP SEQUENCE test_sequence_exists; + +-- schema + +DROP SCHEMA test_schema_exists; + +DROP SCHEMA IF EXISTS test_schema_exists; + +CREATE SCHEMA test_schema_exists; + +DROP SCHEMA IF EXISTS test_schema_exists; + +DROP SCHEMA test_schema_exists; + +-- type + +DROP TYPE test_type_exists; + +DROP TYPE IF EXISTS test_type_exists; + +CREATE type test_type_exists as (a int, b text); + +DROP TYPE IF EXISTS test_type_exists; + +DROP TYPE test_type_exists; + +-- domain + +DROP DOMAIN test_domain_exists; + +DROP DOMAIN IF EXISTS test_domain_exists; + +CREATE domain test_domain_exists as int not null check (value > 0); + +DROP DOMAIN IF EXISTS test_domain_exists; + +DROP DOMAIN test_domain_exists; + +-- drop the table + + +DROP TABLE IF EXISTS test_exists; + +DROP TABLE test_exists; + -- 2.40.0