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