drop user/group/role if exists - Mailing list pgsql-patches

This patch provides DROP ... IF EXISTS for user/group/role.

If there's no objection I will apply it soon and document it.

cheers

andrew
Index: src/backend/commands/user.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.167
diff -c -r1.167 user.c
*** src/backend/commands/user.c    23 Dec 2005 16:46:39 -0000    1.167
--- src/backend/commands/user.c    29 Jan 2006 19:01:30 -0000
***************
*** 840,848 ****
                                 PointerGetDatum(role),
                                 0, 0, 0);
          if (!HeapTupleIsValid(tuple))
!             ereport(ERROR,
!                     (errcode(ERRCODE_UNDEFINED_OBJECT),
!                      errmsg("role \"%s\" does not exist", role)));

          roleid = HeapTupleGetOid(tuple);

--- 840,861 ----
                                 PointerGetDatum(role),
                                 0, 0, 0);
          if (!HeapTupleIsValid(tuple))
!         {
!             if (!stmt->missing_ok)
!             {
!                 ereport(ERROR,
!                         (errcode(ERRCODE_UNDEFINED_OBJECT),
!                          errmsg("role \"%s\" does not exist", role)));
!             }
!             else
!             {
!                 ereport(NOTICE,
!                         (errmsg("role \"%s\" does not exist, skipping",
!                                 role)));
!             }
!
!             continue;
!         }

          roleid = HeapTupleGetOid(tuple);

Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.324
diff -c -r1.324 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    28 Dec 2005 01:29:59 -0000    1.324
--- src/backend/nodes/copyfuncs.c    29 Jan 2006 19:01:33 -0000
***************
*** 2499,2504 ****
--- 2499,2505 ----
      DropRoleStmt *newnode = makeNode(DropRoleStmt);

      COPY_NODE_FIELD(roles);
+     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.260
diff -c -r1.260 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    28 Dec 2005 01:29:59 -0000    1.260
--- src/backend/nodes/equalfuncs.c    29 Jan 2006 19:01:34 -0000
***************
*** 1384,1389 ****
--- 1384,1390 ----
  _equalDropRoleStmt(DropRoleStmt *a, DropRoleStmt *b)
  {
      COMPARE_NODE_FIELD(roles);
+     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.524
diff -c -r2.524 gram.y
*** src/backend/parser/gram.y    22 Jan 2006 20:03:16 -0000    2.524
--- src/backend/parser/gram.y    29 Jan 2006 19:01:41 -0000
***************
*** 824,832 ****
--- 824,840 ----
              DROP ROLE name_list
                  {
                      DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->missing_ok = FALSE;
                      n->roles = $3;
                      $$ = (Node *)n;
                  }
+             | DROP ROLE IF_P EXISTS name_list
+                 {
+                     DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->missing_ok = TRUE;
+                     n->roles = $5;
+                     $$ = (Node *)n;
+                 }
              ;

  /*****************************************************************************
***************
*** 842,850 ****
--- 850,866 ----
              DROP USER name_list
                  {
                      DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->missing_ok = FALSE;
                      n->roles = $3;
                      $$ = (Node *)n;
                  }
+             | DROP USER IF_P EXISTS name_list
+                 {
+                     DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->roles = $5;
+                     n->missing_ok = TRUE;
+                     $$ = (Node *)n;
+                 }
              ;


***************
*** 900,908 ****
--- 916,932 ----
              DROP GROUP_P name_list
                  {
                      DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->missing_ok = FALSE;
                      n->roles = $3;
                      $$ = (Node *)n;
                  }
+             | DROP GROUP_P IF_P EXISTS name_list
+                 {
+                     DropRoleStmt *n = makeNode(DropRoleStmt);
+                     n->missing_ok = TRUE;
+                     n->roles = $5;
+                     $$ = (Node *)n;
+                 }
          ;


Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.299
diff -c -r1.299 parsenodes.h
*** src/include/nodes/parsenodes.h    21 Jan 2006 02:16:20 -0000    1.299
--- src/include/nodes/parsenodes.h    29 Jan 2006 19:01:43 -0000
***************
*** 1193,1198 ****
--- 1193,1199 ----
  {
      NodeTag        type;
      List       *roles;            /* List of roles to remove */
+     bool        missing_ok;        /* skip error if a role is missing? */
  } DropRoleStmt;

  /* ----------------------
Index: src/test/regress/expected/drop_if_exists.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/drop_if_exists.out,v
retrieving revision 1.1
diff -c -r1.1 drop_if_exists.out
*** src/test/regress/expected/drop_if_exists.out    19 Nov 2005 17:39:45 -0000    1.1
--- src/test/regress/expected/drop_if_exists.out    29 Jan 2006 19:01:44 -0000
***************
*** 65,67 ****
--- 65,91 ----
  DROP TABLE IF EXISTS test_exists;
  DROP TABLE test_exists;
  ERROR:  table "test_exists" does not exist
+ ---
+ --- role/user/group
+ ---
+ CREATE USER tu1;
+ CREATE ROLE tr1;
+ CREATE GROUP tg1;
+ DROP USER tu2;
+ ERROR:  role "tu2" does not exist
+ DROP USER IF EXISTS tu1, tu2;
+ NOTICE:  role "tu2" does not exist, skipping
+ DROP USER tu1;
+ ERROR:  role "tu1" does not exist
+ DROP ROLE tr2;
+ ERROR:  role "tr2" does not exist
+ DROP ROLE IF EXISTS tr1, tr2;
+ NOTICE:  role "tr2" does not exist, skipping
+ DROP ROLE tr1;
+ ERROR:  role "tr1" does not exist
+ DROP GROUP tg2;
+ ERROR:  role "tg2" does not exist
+ DROP GROUP IF EXISTS tg1, tg2;
+ NOTICE:  role "tg2" does not exist, skipping
+ DROP GROUP tg1;
+ ERROR:  role "tg1" does not exist
Index: src/test/regress/sql/drop_if_exists.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/drop_if_exists.sql,v
retrieving revision 1.1
diff -c -r1.1 drop_if_exists.sql
*** src/test/regress/sql/drop_if_exists.sql    19 Nov 2005 17:39:45 -0000    1.1
--- src/test/regress/sql/drop_if_exists.sql    29 Jan 2006 19:01:44 -0000
***************
*** 89,91 ****
--- 89,118 ----

  DROP TABLE test_exists;

+
+ ---
+ --- role/user/group
+ ---
+
+ CREATE USER tu1;
+ CREATE ROLE tr1;
+ CREATE GROUP tg1;
+
+ DROP USER tu2;
+
+ DROP USER IF EXISTS tu1, tu2;
+
+ DROP USER tu1;
+
+ DROP ROLE tr2;
+
+ DROP ROLE IF EXISTS tr1, tr2;
+
+ DROP ROLE tr1;
+
+ DROP GROUP tg2;
+
+ DROP GROUP IF EXISTS tg1, tg2;
+
+ DROP GROUP tg1;
+