Skip to content

Commit f8b54fe

Browse files
adunstanadunstan
committed
DROP IF EXISTS for ROLE/USER/GROUP
1 parent 3fa9c41 commit f8b54fe

File tree

10 files changed

+116
-14
lines changed

10 files changed

+116
-14
lines changed

‎doc/src/sgml/ref/drop_group.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_group.sgml,v 1.11 2005/07/26 23:24:02 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_group.sgml,v 1.12 2006/02/04 19:06:46 adunstan Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DROP GROUP <replaceable class="PARAMETER">name</replaceable> [, ...]
23+
DROP GROUP [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...]
2424
</synopsis>
2525
</refsynopsisdiv>
2626

‎doc/src/sgml/ref/drop_role.sgml

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_role.sgml,v 1.1 2005/07/26 23:24:02 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_role.sgml,v 1.2 2006/02/04 19:06:46 adunstan Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DROP ROLE <replaceable class="PARAMETER">name</replaceable> [, ...]
23+
DROP ROLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...]
2424
</synopsis>
2525
</refsynopsisdiv>
2626

@@ -52,6 +52,17 @@ DROP ROLE <replaceable class="PARAMETER">name</replaceable> [, ...]
5252
<refsect1>
5353
<title>Parameters</title>
5454

55+
<variablelist>
56+
<varlistentry>
57+
<term><literal>IF EXISTS</literal></term>
58+
<listitem>
59+
<para>
60+
Do not throw an error if the role does not exist. A notice is issued
61+
in this case.
62+
</para>
63+
</listitem>
64+
</varlistentry>
65+
5566
<variablelist>
5667
<varlistentry>
5768
<term><replaceable class="PARAMETER">name</replaceable></term>

‎doc/src/sgml/ref/drop_user.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_user.sgml,v 1.21 2005/07/26 23:24:02 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/drop_user.sgml,v 1.22 2006/02/04 19:06:46 adunstan Exp $
33
PostgreSQL documentation
44
-->
55

@@ -20,7 +20,7 @@ PostgreSQL documentation
2020

2121
<refsynopsisdiv>
2222
<synopsis>
23-
DROP USER <replaceable class="PARAMETER">name</replaceable> [, ...]
23+
DROP USER [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> [, ...]
2424
</synopsis>
2525
</refsynopsisdiv>
2626

‎src/backend/commands/user.c

Lines changed: 17 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.167 2005/12/23 16:46:39 petere Exp $
9+
* $PostgreSQL: pgsql/src/backend/commands/user.c,v 1.168 2006/02/04 19:06:46 adunstan Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -840,9 +840,22 @@ DropRole(DropRoleStmt *stmt)
840840
PointerGetDatum(role),
841841
0, 0, 0);
842842
if (!HeapTupleIsValid(tuple))
843-
ereport(ERROR,
844-
(errcode(ERRCODE_UNDEFINED_OBJECT),
845-
errmsg("role \"%s\" does not exist", role)));
843+
{
844+
if (!stmt->missing_ok)
845+
{
846+
ereport(ERROR,
847+
(errcode(ERRCODE_UNDEFINED_OBJECT),
848+
errmsg("role \"%s\" does not exist", role)));
849+
}
850+
else
851+
{
852+
ereport(NOTICE,
853+
(errmsg("role \"%s\" does not exist, skipping",
854+
role)));
855+
}
856+
857+
continue;
858+
}
846859

847860
roleid = HeapTupleGetOid(tuple);
848861

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.325 2006/01/31 21:39:23 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.326 2006/02/04 19:06:46 adunstan Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2518,6 +2518,7 @@ _copyDropRoleStmt(DropRoleStmt *from)
25182518
DropRoleStmt *newnode = makeNode(DropRoleStmt);
25192519

25202520
COPY_NODE_FIELD(roles);
2521+
COPY_SCALAR_FIELD(missing_ok);
25212522

25222523
return newnode;
25232524
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.261 2006/01/31 21:39:23 tgl Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.262 2006/02/04 19:06:46 adunstan Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -1398,6 +1398,7 @@ static bool
13981398
_equalDropRoleStmt(DropRoleStmt *a, DropRoleStmt *b)
13991399
{
14001400
COMPARE_NODE_FIELD(roles);
1401+
COMPARE_SCALAR_FIELD(missing_ok);
14011402

14021403
return true;
14031404
}

‎src/backend/parser/gram.y

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.525 2006/01/31 22:40:03 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.526 2006/02/04 19:06:46 adunstan Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -824,9 +824,17 @@ DropRoleStmt:
824824
DROP ROLE name_list
825825
{
826826
DropRoleStmt *n = makeNode(DropRoleStmt);
827+
n->missing_ok = FALSE;
827828
n->roles = $3;
828829
$$ = (Node *)n;
829830
}
831+
| DROP ROLE IF_P EXISTS name_list
832+
{
833+
DropRoleStmt *n = makeNode(DropRoleStmt);
834+
n->missing_ok = TRUE;
835+
n->roles = $5;
836+
$$ = (Node *)n;
837+
}
830838
;
831839

832840
/*****************************************************************************
@@ -842,9 +850,17 @@ DropUserStmt:
842850
DROP USER name_list
843851
{
844852
DropRoleStmt *n = makeNode(DropRoleStmt);
853+
n->missing_ok = FALSE;
845854
n->roles = $3;
846855
$$ = (Node *)n;
847856
}
857+
| DROP USER IF_P EXISTS name_list
858+
{
859+
DropRoleStmt *n = makeNode(DropRoleStmt);
860+
n->roles = $5;
861+
n->missing_ok = TRUE;
862+
$$ = (Node *)n;
863+
}
848864
;
849865

850866

@@ -900,9 +916,17 @@ DropGroupStmt:
900916
DROP GROUP_P name_list
901917
{
902918
DropRoleStmt *n = makeNode(DropRoleStmt);
919+
n->missing_ok = FALSE;
903920
n->roles = $3;
904921
$$ = (Node *)n;
905922
}
923+
| DROP GROUP_P IF_P EXISTS name_list
924+
{
925+
DropRoleStmt *n = makeNode(DropRoleStmt);
926+
n->missing_ok = TRUE;
927+
n->roles = $5;
928+
$$ = (Node *)n;
929+
}
906930
;
907931

908932

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.299 2006/01/21 02:16:20 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.300 2006/02/04 19:06:46 adunstan Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -1193,6 +1193,7 @@ typedef struct DropRoleStmt
11931193
{
11941194
NodeTag type;
11951195
List *roles; /* List of roles to remove */
1196+
bool missing_ok; /* skip error if a role is missing? */
11961197
} DropRoleStmt;
11971198

11981199
/* ----------------------

‎src/test/regress/expected/drop_if_exists.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -65,3 +65,27 @@ ERROR: type "test_domain_exists" does not exist
6565
DROP TABLE IF EXISTS test_exists;
6666
DROP TABLE test_exists;
6767
ERROR: table "test_exists" does not exist
68+
---
69+
--- role/user/group
70+
---
71+
CREATE USER tu1;
72+
CREATE ROLE tr1;
73+
CREATE GROUP tg1;
74+
DROP USER tu2;
75+
ERROR: role "tu2" does not exist
76+
DROP USER IF EXISTS tu1, tu2;
77+
NOTICE: role "tu2" does not exist, skipping
78+
DROP USER tu1;
79+
ERROR: role "tu1" does not exist
80+
DROP ROLE tr2;
81+
ERROR: role "tr2" does not exist
82+
DROP ROLE IF EXISTS tr1, tr2;
83+
NOTICE: role "tr2" does not exist, skipping
84+
DROP ROLE tr1;
85+
ERROR: role "tr1" does not exist
86+
DROP GROUP tg2;
87+
ERROR: role "tg2" does not exist
88+
DROP GROUP IF EXISTS tg1, tg2;
89+
NOTICE: role "tg2" does not exist, skipping
90+
DROP GROUP tg1;
91+
ERROR: role "tg1" does not exist

‎src/test/regress/sql/drop_if_exists.sql

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,3 +89,30 @@ DROP TABLE IF EXISTS test_exists;
8989

9090
DROP TABLE test_exists;
9191

92+
93+
---
94+
--- role/user/group
95+
---
96+
97+
CREATE USER tu1;
98+
CREATE ROLE tr1;
99+
CREATE GROUP tg1;
100+
101+
DROP USER tu2;
102+
103+
DROP USER IF EXISTS tu1, tu2;
104+
105+
DROP USER tu1;
106+
107+
DROP ROLE tr2;
108+
109+
DROP ROLE IF EXISTS tr1, tr2;
110+
111+
DROP ROLE tr1;
112+
113+
DROP GROUP tg2;
114+
115+
DROP GROUP IF EXISTS tg1, tg2;
116+
117+
DROP GROUP tg1;
118+

0 commit comments

Comments
 (0)