Thread: drop if exists remainder

Here's a first draft patch for DROP ... IF EXISTS for the remaining
cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS, FUNCTION,
AGGREGATE, OPERATOR, CAST and RULE.

comments welcome - working on tests/docs now.

cheers

andrew
Index: src/backend/commands/aggregatecmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/aggregatecmds.c,v
retrieving revision 1.31
diff -c -r1.31 aggregatecmds.c
*** src/backend/commands/aggregatecmds.c    22 Nov 2005 18:17:08 -0000    1.31
--- src/backend/commands/aggregatecmds.c    5 Feb 2006 14:29:51 -0000
***************
*** 180,186 ****
      else
          basetypeID = ANYOID;

!     procOid = find_aggregate_func(aggName, basetypeID, false);

      /*
       * Find the function tuple, do permissions and validity checks
--- 180,196 ----
      else
          basetypeID = ANYOID;

!     procOid = find_aggregate_func(aggName, basetypeID, stmt->missing_ok);
!
!     if (stmt->missing_ok &&!OidIsValid(procOid) )
!     {
!         ereport(NOTICE,
!                 (errmsg("aggregate %s(%s) does not exist ... skipping",
!                         NameListToString(aggName),
!                         (aggType ? format_type_be(basetypeID): "*" ))));
!
!         return;
!     }

      /*
       * Find the function tuple, do permissions and validity checks
Index: src/backend/commands/functioncmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.70
diff -c -r1.70 functioncmds.c
*** src/backend/commands/functioncmds.c    21 Nov 2005 12:49:31 -0000    1.70
--- src/backend/commands/functioncmds.c    5 Feb 2006 14:29:51 -0000
***************
*** 687,693 ****
      /*
       * Find the function, do permissions and validity checks
       */
!     funcOid = LookupFuncNameTypeNames(functionName, argTypes, false);

      tup = SearchSysCache(PROCOID,
                           ObjectIdGetDatum(funcOid),
--- 687,703 ----
      /*
       * Find the function, do permissions and validity checks
       */
!     funcOid = LookupFuncNameTypeNames(functionName, argTypes,
!                                       stmt->missing_ok);
!
!     if (stmt->missing_ok &&!OidIsValid(funcOid))
!     {
!         ereport(NOTICE,
!                 (errmsg("function %s(%s) does not exist ... skipping",
!                         NameListToString(functionName),
!                         NameListToString(argTypes))));
!         return;
!     }

      tup = SearchSysCache(PROCOID,
                           ObjectIdGetDatum(funcOid),
***************
*** 1402,1411 ****

      sourcetypeid = LookupTypeName(stmt->sourcetype);
      if (!OidIsValid(sourcetypeid))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("source data type %s does not exist",
!                         TypeNameToString(stmt->sourcetype))));

      targettypeid = LookupTypeName(stmt->targettype);
      if (!OidIsValid(targettypeid))
--- 1412,1429 ----

      sourcetypeid = LookupTypeName(stmt->sourcetype);
      if (!OidIsValid(sourcetypeid))
!     {
!         if ( ! stmt->missing_ok )
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("source data type %s does not exist",
!                             TypeNameToString(stmt->sourcetype))));
!         else
!             ereport(NOTICE,
!                     (errmsg("source data type %s does not exist ... skipping",
!                             TypeNameToString(stmt->sourcetype))));
!         return;
!     }

      targettypeid = LookupTypeName(stmt->targettype);
      if (!OidIsValid(targettypeid))
Index: src/backend/commands/opclasscmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/opclasscmds.c,v
retrieving revision 1.41
diff -c -r1.41 opclasscmds.c
*** src/backend/commands/opclasscmds.c    13 Jan 2006 18:10:25 -0000    1.41
--- src/backend/commands/opclasscmds.c    5 Feb 2006 14:29:52 -0000
***************
*** 703,724 ****
      {
          /* Unqualified opclass name, so search the search path */
          opcID = OpclassnameGetOpcid(amID, opcname);
          if (!OidIsValid(opcID))
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("operator class \"%s\" does not exist for access method \"%s\"",
!                             opcname, stmt->amname)));
          tuple = SearchSysCache(CLAOID,
                                 ObjectIdGetDatum(opcID),
                                 0, 0, 0);
      }

      if (!HeapTupleIsValid(tuple))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("operator class \"%s\" does not exist for access method \"%s\"",
!                         NameListToString(stmt->opclassname), stmt->amname)));

      opcID = HeapTupleGetOid(tuple);

      /* Permission check: must own opclass or its namespace */
--- 703,743 ----
      {
          /* Unqualified opclass name, so search the search path */
          opcID = OpclassnameGetOpcid(amID, opcname);
+
          if (!OidIsValid(opcID))
!         {
!             if (! stmt -> missing_ok )
!                 ereport(ERROR,
!                         (errcode(ERRCODE_UNDEFINED_OBJECT),
!                          errmsg("operator class \"%s\" does not exist for access method \"%s\"",
!                                 opcname, stmt->amname)));
!             else
!                 ereport(NOTICE,
!                         (errmsg("operator class \"%s\" does not exist for access method \"%s\"",
!                                 opcname, stmt->amname)));
!
!             return;
!         }
!
          tuple = SearchSysCache(CLAOID,
                                 ObjectIdGetDatum(opcID),
                                 0, 0, 0);
      }

      if (!HeapTupleIsValid(tuple))
!     {

+         if (! stmt->missing_ok )
+             ereport(ERROR,
+                     (errcode(ERRCODE_UNDEFINED_OBJECT),
+                      errmsg("operator class \"%s\" does not exist for access method \"%s\"",
+                             NameListToString(stmt->opclassname), stmt->amname)));
+         else
+             ereport(NOTICE,
+                     (errmsg("operator class \"%s\" does not exist for access method \"%s\"",
+                             NameListToString(stmt->opclassname), stmt->amname)));
+         return;
+     }
      opcID = HeapTupleGetOid(tuple);

      /* Permission check: must own opclass or its namespace */
Index: src/backend/commands/operatorcmds.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/operatorcmds.c,v
retrieving revision 1.27
diff -c -r1.27 operatorcmds.c
*** src/backend/commands/operatorcmds.c    21 Nov 2005 12:49:31 -0000    1.27
--- src/backend/commands/operatorcmds.c    5 Feb 2006 14:29:52 -0000
***************
*** 211,217 ****
      ObjectAddress object;

      operOid = LookupOperNameTypeNames(operatorName, typeName1, typeName2,
!                                       false);

      tup = SearchSysCache(OPEROID,
                           ObjectIdGetDatum(operOid),
--- 211,225 ----
      ObjectAddress object;

      operOid = LookupOperNameTypeNames(operatorName, typeName1, typeName2,
!                                       stmt->missing_ok);
!
!     if (stmt->missing_ok &&!OidIsValid(operOid) )
!     {
!         ereport(NOTICE,
!                 (errmsg("operator %s does not exist ... skipping",
!                         NameListToString(operatorName))));
!         return;
!     }

      tup = SearchSysCache(OPEROID,
                           ObjectIdGetDatum(operOid),
Index: src/backend/commands/proclang.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/proclang.c,v
retrieving revision 1.63
diff -c -r1.63 proclang.c
*** src/backend/commands/proclang.c    15 Oct 2005 02:49:15 -0000    1.63
--- src/backend/commands/proclang.c    5 Feb 2006 14:29:53 -0000
***************
*** 396,404 ****
                               CStringGetDatum(languageName),
                               0, 0, 0);
      if (!HeapTupleIsValid(langTup))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("language \"%s\" does not exist", languageName)));

      object.classId = LanguageRelationId;
      object.objectId = HeapTupleGetOid(langTup);
--- 396,413 ----
                               CStringGetDatum(languageName),
                               0, 0, 0);
      if (!HeapTupleIsValid(langTup))
!     {
!         if (! stmt->missing_ok)
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("language \"%s\" does not exist", languageName)));
!         else
!             ereport(NOTICE,
!                     (errmsg("language \"%s\" does not exist ... skipping",
!                            languageName)));
!
!         return;
!     }

      object.classId = LanguageRelationId;
      object.objectId = HeapTupleGetOid(langTup);
Index: src/backend/commands/tablespace.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.29
diff -c -r1.29 tablespace.c
*** src/backend/commands/tablespace.c    19 Jan 2006 04:45:38 -0000    1.29
--- src/backend/commands/tablespace.c    5 Feb 2006 14:29:53 -0000
***************
*** 403,412 ****
      tuple = heap_getnext(scandesc, ForwardScanDirection);

      if (!HeapTupleIsValid(tuple))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("tablespace \"%s\" does not exist",
!                         tablespacename)));

      tablespaceoid = HeapTupleGetOid(tuple);

--- 403,427 ----
      tuple = heap_getnext(scandesc, ForwardScanDirection);

      if (!HeapTupleIsValid(tuple))
!     {
!         if ( ! stmt->missing_ok )
!         {
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("tablespace \"%s\" does not exist",
!                             tablespacename)));
!         }
!         else
!         {
!             ereport(NOTICE,
!                     (errmsg("tablespace \"%s\" does not exist ... skipping",
!                             tablespacename)));
!             /* XXX I assume I need one or both of these next two calls */
!             heap_endscan(scandesc);
!             heap_close(rel, NoLock);
!         }
!         return;
!     }

      tablespaceoid = HeapTupleGetOid(tuple);

Index: src/backend/commands/trigger.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/trigger.c,v
retrieving revision 1.199
diff -c -r1.199 trigger.c
*** src/backend/commands/trigger.c    12 Jan 2006 21:48:52 -0000    1.199
--- src/backend/commands/trigger.c    5 Feb 2006 14:29:56 -0000
***************
*** 450,456 ****
   * DropTrigger - drop an individual trigger by name
   */
  void
! DropTrigger(Oid relid, const char *trigname, DropBehavior behavior)
  {
      Relation    tgrel;
      ScanKeyData skey[2];
--- 450,457 ----
   * DropTrigger - drop an individual trigger by name
   */
  void
! DropTrigger(Oid relid, const char *trigname, DropBehavior behavior,
!             bool missing_ok)
  {
      Relation    tgrel;
      ScanKeyData skey[2];
***************
*** 479,489 ****
      tup = systable_getnext(tgscan);

      if (!HeapTupleIsValid(tup))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("trigger \"%s\" for table \"%s\" does not exist",
!                         trigname, get_rel_name(relid))));
!
      if (!pg_class_ownercheck(relid, GetUserId()))
          aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
                         get_rel_name(relid));
--- 480,500 ----
      tup = systable_getnext(tgscan);

      if (!HeapTupleIsValid(tup))
!     {
!         if (! missing_ok)
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("trigger \"%s\" for table \"%s\" does not exist",
!                             trigname, get_rel_name(relid))));
!         else
!             ereport(NOTICE,
!                     (errmsg("trigger \"%s\" for table \"%s\" does not exist ... skipping",
!                             trigname, get_rel_name(relid))));
!         /* cleanup */
!         systable_endscan(tgscan);
!         heap_close(tgrel, AccessShareLock);
!         return;
!     }
      if (!pg_class_ownercheck(relid, GetUserId()))
          aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
                         get_rel_name(relid));
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.326
diff -c -r1.326 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    4 Feb 2006 19:06:46 -0000    1.326
--- src/backend/nodes/copyfuncs.c    5 Feb 2006 14:29:57 -0000
***************
*** 2052,2057 ****
--- 2052,2058 ----
      COPY_NODE_FIELD(aggname);
      COPY_NODE_FIELD(aggtype);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2064,2069 ****
--- 2065,2071 ----
      COPY_NODE_FIELD(funcname);
      COPY_NODE_FIELD(args);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2076,2081 ****
--- 2078,2084 ----
      COPY_NODE_FIELD(opname);
      COPY_NODE_FIELD(args);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2088,2093 ****
--- 2091,2097 ----
      COPY_NODE_FIELD(opclassname);
      COPY_STRING_FIELD(amname);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2415,2420 ****
--- 2419,2425 ----
      DropTableSpaceStmt *newnode = makeNode(DropTableSpaceStmt);

      COPY_STRING_FIELD(tablespacename);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2448,2453 ****
--- 2453,2459 ----
      COPY_STRING_FIELD(property);
      COPY_SCALAR_FIELD(removeType);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2472,2477 ****
--- 2478,2484 ----

      COPY_STRING_FIELD(plname);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
***************
*** 2607,2612 ****
--- 2614,2620 ----
      COPY_NODE_FIELD(sourcetype);
      COPY_NODE_FIELD(targettype);
      COPY_SCALAR_FIELD(behavior);
+     COPY_SCALAR_FIELD(missing_ok);

      return newnode;
  }
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.262
diff -c -r1.262 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    4 Feb 2006 19:06:46 -0000    1.262
--- src/backend/nodes/equalfuncs.c    5 Feb 2006 14:29:58 -0000
***************
*** 1007,1012 ****
--- 1007,1013 ----
      COMPARE_NODE_FIELD(aggname);
      COMPARE_NODE_FIELD(aggtype);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1017,1022 ****
--- 1018,1024 ----
      COMPARE_NODE_FIELD(funcname);
      COMPARE_NODE_FIELD(args);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1027,1032 ****
--- 1029,1035 ----
      COMPARE_NODE_FIELD(opname);
      COMPARE_NODE_FIELD(args);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1037,1042 ****
--- 1040,1046 ----
      COMPARE_NODE_FIELD(opclassname);
      COMPARE_STRING_FIELD(amname);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1310,1315 ****
--- 1314,1320 ----
  _equalDropTableSpaceStmt(DropTableSpaceStmt *a, DropTableSpaceStmt *b)
  {
      COMPARE_STRING_FIELD(tablespacename);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1340,1345 ****
--- 1345,1351 ----
      COMPARE_STRING_FIELD(property);
      COMPARE_SCALAR_FIELD(removeType);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1360,1365 ****
--- 1366,1372 ----
  {
      COMPARE_STRING_FIELD(plname);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
***************
*** 1473,1478 ****
--- 1480,1486 ----
      COMPARE_NODE_FIELD(sourcetype);
      COMPARE_NODE_FIELD(targettype);
      COMPARE_SCALAR_FIELD(behavior);
+     COMPARE_SCALAR_FIELD(missing_ok);

      return true;
  }
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.526
diff -c -r2.526 gram.y
*** src/backend/parser/gram.y    4 Feb 2006 19:06:46 -0000    2.526
--- src/backend/parser/gram.y    5 Feb 2006 14:30:03 -0000
***************
*** 2390,2395 ****
--- 2390,2405 ----
                      DropPLangStmt *n = makeNode(DropPLangStmt);
                      n->plname = $4;
                      n->behavior = $5;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *)n;
+                 }
+             | DROP opt_procedural LANGUAGE IF_P EXISTS
+               ColId_or_Sconst opt_drop_behavior
+                 {
+                     DropPLangStmt *n = makeNode(DropPLangStmt);
+                     n->plname = $6;
+                     n->behavior = $7;
+                     n->missing_ok = TRUE;
                      $$ = (Node *)n;
                  }
          ;
***************
*** 2434,2439 ****
--- 2444,2457 ----
                  {
                      DropTableSpaceStmt *n = makeNode(DropTableSpaceStmt);
                      n->tablespacename = $3;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *) n;
+                 }
+              | DROP TABLESPACE IF_P EXISTS name
+                 {
+                     DropTableSpaceStmt *n = makeNode(DropTableSpaceStmt);
+                     n->tablespacename = $5;
+                     n->missing_ok = TRUE;
                      $$ = (Node *) n;
                  }
          ;
***************
*** 2618,2623 ****
--- 2636,2652 ----
                      n->relation = $5;
                      n->property = $3;
                      n->behavior = $6;
+                     n->missing_ok = FALSE;
+                     n->removeType = OBJECT_TRIGGER;
+                     $$ = (Node *) n;
+                 }
+             | DROP TRIGGER IF_P EXISTS name ON qualified_name opt_drop_behavior
+                 {
+                     DropPropertyStmt *n = makeNode(DropPropertyStmt);
+                     n->relation = $7;
+                     n->property = $5;
+                     n->behavior = $8;
+                     n->missing_ok = TRUE;
                      n->removeType = OBJECT_TRIGGER;
                      $$ = (Node *) n;
                  }
***************
*** 2658,2663 ****
--- 2687,2693 ----
                      n->relation = NULL;
                      n->property = $3;
                      n->behavior = $4;
+                     n->missing_ok = FALSE; /* fix if we implement assertions */
                      n->removeType = OBJECT_TRIGGER; /* XXX */
                      ereport(ERROR,
                              (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
***************
*** 2842,2847 ****
--- 2872,2888 ----
                      n->opclassname = $4;
                      n->amname = $6;
                      n->behavior = $7;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *) n;
+                 }
+             | DROP OPERATOR CLASS IF_P EXISTS any_name
+               USING access_method opt_drop_behavior
+                 {
+                     RemoveOpClassStmt *n = makeNode(RemoveOpClassStmt);
+                     n->opclassname = $6;
+                     n->amname = $8;
+                     n->behavior = $9;
+                     n->missing_ok = TRUE;
                      $$ = (Node *) n;
                  }
          ;
***************
*** 3845,3850 ****
--- 3886,3901 ----
                      n->funcname = $3;
                      n->args = extractArgTypes($4);
                      n->behavior = $5;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *)n;
+                 }
+             | DROP FUNCTION IF_P EXISTS func_name func_args opt_drop_behavior
+                 {
+                     RemoveFuncStmt *n = makeNode(RemoveFuncStmt);
+                     n->funcname = $5;
+                     n->args = extractArgTypes($6);
+                     n->behavior = $7;
+                     n->missing_ok = TRUE;
                      $$ = (Node *)n;
                  }
          ;
***************
*** 3856,3861 ****
--- 3907,3923 ----
                          n->aggname = $3;
                          n->aggtype = $5;
                          n->behavior = $7;
+                         n->missing_ok = FALSE;
+                         $$ = (Node *)n;
+                 }
+             | DROP AGGREGATE IF_P EXISTS func_name '(' aggr_argtype ')'
+               opt_drop_behavior
+                 {
+                         RemoveAggrStmt *n = makeNode(RemoveAggrStmt);
+                         n->aggname = $5;
+                         n->aggtype = $7;
+                         n->behavior = $9;
+                         n->missing_ok = TRUE;
                          $$ = (Node *)n;
                  }
          ;
***************
*** 3872,3877 ****
--- 3934,3950 ----
                      n->opname = $3;
                      n->args = $5;
                      n->behavior = $7;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *)n;
+                 }
+             | DROP OPERATOR IF_P EXISTS any_operator '(' oper_argtypes ')'
+               opt_drop_behavior
+                 {
+                     RemoveOperStmt *n = makeNode(RemoveOperStmt);
+                     n->opname = $5;
+                     n->args = $7;
+                     n->behavior = $9;
+                     n->missing_ok = TRUE;
                      $$ = (Node *)n;
                  }
          ;
***************
*** 3940,3945 ****
--- 4013,4028 ----
                      n->sourcetype = $4;
                      n->targettype = $6;
                      n->behavior = $8;
+                     n->missing_ok = FALSE;
+                     $$ = (Node *)n;
+                 }
+         | DROP CAST IF_P EXISTS '(' Typename AS Typename ')' opt_drop_behavior
+                 {
+                     DropCastStmt *n = makeNode(DropCastStmt);
+                     n->sourcetype = $6;
+                     n->targettype = $8;
+                     n->behavior = $10;
+                     n->missing_ok = TRUE;
                      $$ = (Node *)n;
                  }
          ;
***************
*** 4367,4372 ****
--- 4450,4466 ----
                      n->relation = $5;
                      n->property = $3;
                      n->behavior = $6;
+                     n->missing_ok = FALSE;
+                     n->removeType = OBJECT_RULE;
+                     $$ = (Node *) n;
+                 }
+             | DROP RULE IF_P EXISTS name ON qualified_name opt_drop_behavior
+                 {
+                     DropPropertyStmt *n = makeNode(DropPropertyStmt);
+                     n->relation = $7;
+                     n->property = $5;
+                     n->behavior = $8;
+                     n->missing_ok = TRUE;
                      n->removeType = OBJECT_RULE;
                      $$ = (Node *) n;
                  }
Index: src/backend/rewrite/rewriteRemove.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/rewrite/rewriteRemove.c,v
retrieving revision 1.63
diff -c -r1.63 rewriteRemove.c
*** src/backend/rewrite/rewriteRemove.c    15 Oct 2005 02:49:24 -0000    1.63
--- src/backend/rewrite/rewriteRemove.c    5 Feb 2006 14:30:04 -0000
***************
*** 34,40 ****
   * Delete a rule given its name.
   */
  void
! RemoveRewriteRule(Oid owningRel, const char *ruleName, DropBehavior behavior)
  {
      HeapTuple    tuple;
      Oid            eventRelationOid;
--- 34,41 ----
   * Delete a rule given its name.
   */
  void
! RemoveRewriteRule(Oid owningRel, const char *ruleName, DropBehavior behavior,
!                   bool missing_ok)
  {
      HeapTuple    tuple;
      Oid            eventRelationOid;
***************
*** 53,63 ****
       * complain if no rule with such name exists
       */
      if (!HeapTupleIsValid(tuple))
!         ereport(ERROR,
!                 (errcode(ERRCODE_UNDEFINED_OBJECT),
!                  errmsg("rule \"%s\" for relation \"%s\" does not exist",
!                         ruleName, get_rel_name(owningRel))));
!
      /*
       * Verify user has appropriate permissions.
       */
--- 54,71 ----
       * complain if no rule with such name exists
       */
      if (!HeapTupleIsValid(tuple))
!     {
!         if (! missing_ok)
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("rule \"%s\" for relation \"%s\" does not exist",
!                             ruleName, get_rel_name(owningRel))));
!         else
!             ereport(NOTICE,
!                     (errmsg("rule \"%s\" for relation \"%s\" does not exist ... skipping",
!                             ruleName, get_rel_name(owningRel))));
!         return;
!     }
      /*
       * Verify user has appropriate permissions.
       */
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.250
diff -c -r1.250 utility.c
*** src/backend/tcop/utility.c    29 Nov 2005 01:25:49 -0000    1.250
--- src/backend/tcop/utility.c    5 Feb 2006 14:30:05 -0000
***************
*** 972,983 ****
                      case OBJECT_RULE:
                          /* RemoveRewriteRule checks permissions */
                          RemoveRewriteRule(relId, stmt->property,
!                                           stmt->behavior);
                          break;
                      case OBJECT_TRIGGER:
                          /* DropTrigger checks permissions */
                          DropTrigger(relId, stmt->property,
!                                     stmt->behavior);
                          break;
                      default:
                          elog(ERROR, "unrecognized object type: %d",
--- 972,983 ----
                      case OBJECT_RULE:
                          /* RemoveRewriteRule checks permissions */
                          RemoveRewriteRule(relId, stmt->property,
!                                           stmt->behavior, stmt->missing_ok);
                          break;
                      case OBJECT_TRIGGER:
                          /* DropTrigger checks permissions */
                          DropTrigger(relId, stmt->property,
!                                     stmt->behavior, stmt->missing_ok);
                          break;
                      default:
                          elog(ERROR, "unrecognized object type: %d",
Index: src/include/commands/trigger.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/trigger.h,v
retrieving revision 1.56
diff -c -r1.56 trigger.h
*** src/include/commands/trigger.h    15 Oct 2005 02:49:44 -0000    1.56
--- src/include/commands/trigger.h    5 Feb 2006 14:30:06 -0000
***************
*** 108,114 ****
  extern Oid    CreateTrigger(CreateTrigStmt *stmt, bool forConstraint);

  extern void DropTrigger(Oid relid, const char *trigname,
!             DropBehavior behavior);
  extern void RemoveTriggerById(Oid trigOid);

  extern void renametrig(Oid relid, const char *oldname, const char *newname);
--- 108,114 ----
  extern Oid    CreateTrigger(CreateTrigStmt *stmt, bool forConstraint);

  extern void DropTrigger(Oid relid, const char *trigname,
!             DropBehavior behavior, bool missing_ok);
  extern void RemoveTriggerById(Oid trigOid);

  extern void renametrig(Oid relid, const char *oldname, const char *newname);
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.300
diff -c -r1.300 parsenodes.h
*** src/include/nodes/parsenodes.h    4 Feb 2006 19:06:46 -0000    1.300
--- src/include/nodes/parsenodes.h    5 Feb 2006 14:30:07 -0000
***************
*** 1103,1108 ****
--- 1103,1109 ----
  {
      NodeTag        type;
      char       *tablespacename;
+     bool        missing_ok;        /* skip error if tablespace is missing? */
  } DropTableSpaceStmt;

  /* ----------------------
***************
*** 1146,1151 ****
--- 1147,1153 ----
  {
      NodeTag        type;
      char       *plname;            /* PL name */
+     bool        missing_ok;        /* skip error if language is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } DropPLangStmt;

***************
*** 1298,1303 ****
--- 1300,1306 ----
      RangeVar   *relation;        /* owning relation */
      char       *property;        /* name of rule, trigger, etc */
      ObjectType    removeType;        /* OBJECT_RULE or OBJECT_TRIGGER */
+     bool        missing_ok;        /* skip error if object is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } DropPropertyStmt;

***************
*** 1444,1449 ****
--- 1447,1453 ----
      NodeTag        type;
      List       *aggname;        /* aggregate to drop */
      TypeName   *aggtype;        /* TypeName for input datatype, or NULL */
+     bool        missing_ok;        /* skip error if a role is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } RemoveAggrStmt;

***************
*** 1456,1461 ****
--- 1460,1466 ----
      NodeTag        type;
      List       *funcname;        /* function to drop */
      List       *args;            /* types of the arguments */
+     bool        missing_ok;        /* skip error if function is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } RemoveFuncStmt;

***************
*** 1468,1473 ****
--- 1473,1479 ----
      NodeTag        type;
      List       *opname;            /* operator to drop */
      List       *args;            /* types of the arguments */
+     bool        missing_ok;        /* skip error if operator is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } RemoveOperStmt;

***************
*** 1480,1485 ****
--- 1486,1492 ----
      NodeTag        type;
      List       *opclassname;    /* qualified name (list of Value strings) */
      char       *amname;            /* name of index AM opclass is for */
+     bool        missing_ok;        /* skip error if opclass is missing? */
      DropBehavior behavior;        /* RESTRICT or CASCADE behavior */
  } RemoveOpClassStmt;

***************
*** 1837,1842 ****
--- 1844,1850 ----
      NodeTag        type;
      TypeName   *sourcetype;
      TypeName   *targettype;
+     bool        missing_ok;        /* skip error if cast is missing? */
      DropBehavior behavior;
  } DropCastStmt;

Index: src/include/rewrite/rewriteRemove.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/rewrite/rewriteRemove.h,v
retrieving revision 1.20
diff -c -r1.20 rewriteRemove.h
*** src/include/rewrite/rewriteRemove.h    31 Dec 2004 22:03:41 -0000    1.20
--- src/include/rewrite/rewriteRemove.h    5 Feb 2006 14:30:07 -0000
***************
*** 18,24 ****


  extern void RemoveRewriteRule(Oid owningRel, const char *ruleName,
!                   DropBehavior behavior);
  extern void RemoveRewriteRuleById(Oid ruleOid);

  #endif   /* REWRITEREMOVE_H */
--- 18,24 ----


  extern void RemoveRewriteRule(Oid owningRel, const char *ruleName,
!                   DropBehavior behavior, bool missing_ok);
  extern void RemoveRewriteRuleById(Oid ruleOid);

  #endif   /* REWRITEREMOVE_H */

Andrew Dunstan <andrew@dunslane.net> writes:
> Here's a first draft patch for DROP ... IF EXISTS for the remaining
> cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS, FUNCTION,
> AGGREGATE, OPERATOR, CAST and RULE.

At what point does this stop being useful and become mere bloat?
The only case I can ever recall being actually asked for was the
TABLE case ...

            regards, tom lane

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>
>
>>Here's a first draft patch for DROP ... IF EXISTS for the remaining
>>cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS, FUNCTION,
>>AGGREGATE, OPERATOR, CAST and RULE.
>>
>>
>
>At what point does this stop being useful and become mere bloat?
>The only case I can ever recall being actually asked for was the
>TABLE case ...
>
>
>
>

Chris KL said it should be done for all on the grounds of consistency.
But I will happily stop right now if that's not the general view - I'm
only doing this to complete something I started.

cheers

andrew

Andrew Dunstan wrote:
>
>
> Tom Lane wrote:
>
> >Andrew Dunstan <andrew@dunslane.net> writes:
> >
> >
> >>Here's a first draft patch for DROP ... IF EXISTS for the remaining
> >>cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS, FUNCTION,
> >>AGGREGATE, OPERATOR, CAST and RULE.
> >>
> >>
> >
> >At what point does this stop being useful and become mere bloat?
> >The only case I can ever recall being actually asked for was the
> >TABLE case ...
> >
> >
> >
> >
>
> Chris KL said it should be done for all on the grounds of consistency.
> But I will happily stop right now if that's not the general view - I'm
> only doing this to complete something I started.

I am thinking we should have IF EXISTS support for every object that has
CREATE OR REPLACE functionality, plus objects that have storage like
table and perhaps index.

However, I see CREATE ROLE doesn't have REPLACE functionality, so what
is the logic of when we need IF EXISTS and when we don't?  Perhaps they
all should have it, and the REPLACE is just for objects you want to
replace but keep existing linkage in place.

--
  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

Bruce Momjian wrote:

>However, I see CREATE ROLE doesn't have REPLACE functionality, so what
>is the logic of when we need IF EXISTS and when we don't?  Perhaps they
>all should have it, and the REPLACE is just for objects you want to
>replace but keep existing linkage in place.
>
>
>

That was my understanding. I think these are orthogonal issues.

Another issue was MySQL compatibility. AFAIK we achieved that when we
did database, following
{ table view index sequence schema type domain conversion}, which pretty
much all had to be done together, as they share the same statement node
type.

cheers

andrew

>>> Here's a first draft patch for DROP ... IF EXISTS for the remaining
>>> cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS,
>>> FUNCTION, AGGREGATE, OPERATOR, CAST and RULE.
>>>
>>
>> At what point does this stop being useful and become mere bloat?
>> The only case I can ever recall being actually asked for was the
>> TABLE case ...
>
> Chris KL said it should be done for all on the grounds of consistency.
> But I will happily stop right now if that's not the general view - I'm
> only doing this to complete something I started.

Well, my use-case was to be able to wrap "pg_dump -c" output in
begin/commit tags and being able to run and re-run such dumps without
errors.  Basically I don't like 'acceptable errors' when restoring dumps
:)  They just confuse newer users especially.

I also just like consistency :)

Chris

Christopher Kings-Lynne wrote:
> >>> Here's a first draft patch for DROP ... IF EXISTS for the remaining
> >>> cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS,
> >>> FUNCTION, AGGREGATE, OPERATOR, CAST and RULE.
> >>>
> >>
> >> At what point does this stop being useful and become mere bloat?
> >> The only case I can ever recall being actually asked for was the
> >> TABLE case ...
> >
> > Chris KL said it should be done for all on the grounds of consistency.
> > But I will happily stop right now if that's not the general view - I'm
> > only doing this to complete something I started.
>
> Well, my use-case was to be able to wrap "pg_dump -c" output in
> begin/commit tags and being able to run and re-run such dumps without
> errors.  Basically I don't like 'acceptable errors' when restoring dumps
> :)  They just confuse newer users especially.
>
> I also just like consistency :)

Makes sense.

--
  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

Bruce Momjian wrote:

>Christopher Kings-Lynne wrote:
>
>
>>>>>Here's a first draft patch for DROP ... IF EXISTS for the remaining
>>>>>cases, namely: LANGUAGE, TABLESPACE, TRIGGER OPERATOR CLASS,
>>>>>FUNCTION, AGGREGATE, OPERATOR, CAST and RULE.
>>>>>
>>>>>
>>>>>
>>>>At what point does this stop being useful and become mere bloat?
>>>>The only case I can ever recall being actually asked for was the
>>>>TABLE case ...
>>>>
>>>>
>>>Chris KL said it should be done for all on the grounds of consistency.
>>>But I will happily stop right now if that's not the general view - I'm
>>>only doing this to complete something I started.
>>>
>>>
>>Well, my use-case was to be able to wrap "pg_dump -c" output in
>>begin/commit tags and being able to run and re-run such dumps without
>>errors.  Basically I don't like 'acceptable errors' when restoring dumps
>>:)  They just confuse newer users especially.
>>
>>I also just like consistency :)
>>
>>
>
>Makes sense.
>
>
>

What's the consensus on this? Nobody else has chimed in, so I'm inclined
to do no more on the gounds of insufficient demand. Let's decide before
too much bitrot occurs, though.

cheers

andrew

Andrew Dunstan wrote:
> >>>Chris KL said it should be done for all on the grounds of consistency.
> >>>But I will happily stop right now if that's not the general view - I'm
> >>>only doing this to complete something I started.
> >>>
> >>>
> >>Well, my use-case was to be able to wrap "pg_dump -c" output in
> >>begin/commit tags and being able to run and re-run such dumps without
> >>errors.  Basically I don't like 'acceptable errors' when restoring dumps
> >>:)  They just confuse newer users especially.
> >>
> >>I also just like consistency :)
> >>
> >>
> >
> >Makes sense.
> >
> >
> >
>
> What's the consensus on this? Nobody else has chimed in, so I'm inclined
> to do no more on the gounds of insufficient demand. Let's decide before
> too much bitrot occurs, though.

I kind of liked it, but I think I was the only one.

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

On Fri, Mar 03, 2006 at 03:35:24PM -0500, Andrew Dunstan wrote:
> Bruce Momjian wrote:
>
> >Christopher Kings-Lynne wrote:
> >
> >
> What's the consensus on this? Nobody else has chimed in, so I'm inclined
> to do no more on the gounds of insufficient demand. Let's decide before
> too much bitrot occurs, though.

+1 :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

On Saturday 04 March 2006 22:24, David Fetter wrote:
> On Fri, Mar 03, 2006 at 03:35:24PM -0500, Andrew Dunstan wrote:
> > Bruce Momjian wrote:
> > >Christopher Kings-Lynne wrote:
> >
> > What's the consensus on this? Nobody else has chimed in, so I'm inclined
> > to do no more on the gounds of insufficient demand. Let's decide before
> > too much bitrot occurs, though.
>
> +1 :)
>

+1

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Tom,

> > > What's the consensus on this? Nobody else has chimed in, so I'm
> > > inclined to do no more on the gounds of insufficient demand. Let's
> > > decide before too much bitrot occurs, though.
> >
> > +1 :)
>
> +1

We were talking about this on IRC, and I feel that if we're going to do "IF
EXISTS" for any objects, we should do it for all objects.  Otherwise we
risk a considerable amount of user confusion.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

Josh Berkus wrote:
> Tom,
>
>
>>>> What's the consensus on this? Nobody else has chimed in, so I'm
>>>> inclined to do no more on the gounds of insufficient demand. Let's
>>>> decide before too much bitrot occurs, though.
>>>>
>>> +1 :)
>>>
>> +1
>>
>
> We were talking about this on IRC, and I feel that if we're going to do "IF
> EXISTS" for any objects, we should do it for all objects.  Otherwise we
> risk a considerable amount of user confusion.
>
>

OK there does seem to be some demand for this, so I will rework the
patch, and hope to get it done by feature freeze - it has bitrotted with
7 merge conflicts, including the grammar file, so I need to look
carefully at that. Pity people didn't speak up when this was first
raised. :-)

cheers

andrew

On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote:
> OK there does seem to be some demand for this, so I will rework the
> patch, and hope to get it done by feature freeze - it has bitrotted
> with 7 merge conflicts, including the grammar file, so I need to
> look carefully at that.  Pity people didn't speak up when this was
> first raised. :-)

I did :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

David Fetter wrote:
> On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote:
>
>> OK there does seem to be some demand for this, so I will rework the
>> patch, and hope to get it done by feature freeze - it has bitrotted
>> with 7 merge conflicts, including the grammar file, so I need to
>> look carefully at that.  Pity people didn't speak up when this was
>> first raised. :-)
>>
>
> I did :)
>
>


Important as you are, "one swallow does not make a summer".

cheers

andrew

On Thu, Jun 08, 2006 at 12:34:54PM -0400, Andrew Dunstan wrote:
> David Fetter wrote:
> >On Thu, Jun 08, 2006 at 09:43:19AM -0400, Andrew Dunstan wrote:
> >
> >>OK there does seem to be some demand for this, so I will rework the
> >>patch, and hope to get it done by feature freeze - it has bitrotted
> >>with 7 merge conflicts, including the grammar file, so I need to
> >>look carefully at that.  Pity people didn't speak up when this was
> >>first raised. :-)
> >>
> >
> >I did :)
> >
> >
>
>
> Important as you are, "one swallow does not make a summer".

On the other hand, unless we want the lists filling up with a bunch of
+1 posts, it's probably better to assume that unless someone objects a
patch would be accepted.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:



>> Important as you are, "one swallow does not make a summer".
>>
>
> On the other hand, unless we want the lists filling up with a bunch of
> +1 posts, it's probably better to assume that unless someone objects a
> patch would be accepted.
>

What happened was that Tom objected to (or at least queried the need
for) the patch on the grounds that it was bloat that nobody had asked
for. And when I asked I wasn't exactly deluged with requests to commit,
so I concluded that it was not generally wanted. Since then I have had
probably 10 requests for it, so I am now going to work to update it and
will post a revised patch.

cheers

andrew