drop if exists - Mailing list pgsql-patches

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;

  /* ----------------------