Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6d12b68

Browse files
committed
Allow IF NOT EXISTS when add a new enum label.
If the label is already in the enum the statement becomes a no-op.This will reduce the pain that comes from our not allowing thisoperation inside a transaction block.Andrew Dunstan, reviewed by Tom Lane and Magnus Hagander.
1 parent11e1318 commit6d12b68

File tree

10 files changed

+89
-14
lines changed

10 files changed

+89
-14
lines changed

‎doc/src/sgml/ref/alter_type.sgml

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceab
2828
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> TO <replaceable class="PARAMETER">new_attribute_name</replaceable>
2929
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> [ CASCADE | RESTRICT ]
3030
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
31-
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replaceable class="PARAMETER">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="PARAMETER">existing_enum_value</replaceable> ]
31+
ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE[ IF NOT EXISTS ]<replaceable class="PARAMETER">new_enum_value</replaceable> [ { BEFORE | AFTER } <replaceable class="PARAMETER">existing_enum_value</replaceable> ]
3232

3333
<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
3434

@@ -106,14 +106,19 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replacea
106106
</varlistentry>
107107

108108
<varlistentry>
109-
<term><literal>ADD VALUE [ BEFORE | AFTER ]</literal></term>
109+
<term><literal>ADD VALUE [IF NOT EXISTS ] [BEFORE | AFTER ]</literal></term>
110110
<listitem>
111111
<para>
112112
This form adds a new value to an enum type. If the new value's place in
113113
the enum's ordering is not specified using <literal>BEFORE</literal> or
114114
<literal>AFTER</literal>, then the new item is placed at the end of the
115115
list of values.
116116
</para>
117+
<para>
118+
If <literal>IF NOT EXISTS</literal is used, it is not an error if the
119+
type already contains the new value, and no action is taken. Otherwise,
120+
an error will occur if the new value is already present.
121+
</para>
117122
</listitem>
118123
</varlistentry>
119124

‎src/backend/catalog/pg_enum.c

Lines changed: 17 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -179,7 +179,8 @@ void
179179
AddEnumLabel(OidenumTypeOid,
180180
constchar*newVal,
181181
constchar*neighbor,
182-
boolnewValIsAfter)
182+
boolnewValIsAfter,
183+
boolskipIfExists)
183184
{
184185
Relationpg_enum;
185186
OidnewOid;
@@ -211,6 +212,21 @@ AddEnumLabel(Oid enumTypeOid,
211212
*/
212213
LockDatabaseObject(TypeRelationId,enumTypeOid,0,ExclusiveLock);
213214

215+
/* Do the "IF NOT EXISTS" test if specified */
216+
if (skipIfExists)
217+
{
218+
HeapTupletup;
219+
220+
tup=SearchSysCache2(ENUMTYPOIDNAME,
221+
ObjectIdGetDatum(enumTypeOid),
222+
CStringGetDatum(newVal));
223+
if (HeapTupleIsValid(tup))
224+
{
225+
ReleaseSysCache(tup);
226+
return;
227+
}
228+
}
229+
214230
pg_enum=heap_open(EnumRelationId,RowExclusiveLock);
215231

216232
/* If we have to renumber the existing members, we restart from here */

‎src/backend/commands/typecmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1188,7 +1188,8 @@ AlterEnum(AlterEnumStmt *stmt)
11881188

11891189
/* Add the new label */
11901190
AddEnumLabel(enum_type_oid,stmt->newVal,
1191-
stmt->newValNeighbor,stmt->newValIsAfter);
1191+
stmt->newValNeighbor,stmt->newValIsAfter,
1192+
stmt->skipIfExists);
11921193

11931194
ReleaseSysCache(tup);
11941195
}

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3055,6 +3055,7 @@ _copyAlterEnumStmt(const AlterEnumStmt *from)
30553055
COPY_STRING_FIELD(newVal);
30563056
COPY_STRING_FIELD(newValNeighbor);
30573057
COPY_SCALAR_FIELD(newValIsAfter);
3058+
COPY_SCALAR_FIELD(skipIfExists);
30583059

30593060
returnnewnode;
30603061
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1439,6 +1439,7 @@ _equalAlterEnumStmt(const AlterEnumStmt *a, const AlterEnumStmt *b)
14391439
COMPARE_STRING_FIELD(newVal);
14401440
COMPARE_STRING_FIELD(newValNeighbor);
14411441
COMPARE_SCALAR_FIELD(newValIsAfter);
1442+
COMPARE_SCALAR_FIELD(skipIfExists);
14421443

14431444
return true;
14441445
}

‎src/backend/parser/gram.y

Lines changed: 16 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -470,7 +470,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
470470
%type<windef>window_definitionover_clausewindow_specification
471471
opt_frame_clauseframe_extentframe_bound
472472
%type<str>opt_existing_window_name
473-
473+
%type<boolean>opt_if_not_exists
474474

475475
/*
476476
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -4618,35 +4618,42 @@ enum_val_list:Sconst
46184618
*****************************************************************************/
46194619

46204620
AlterEnumStmt:
4621-
ALTERTYPE_Pany_nameADD_PVALUE_PSconst
4621+
ALTERTYPE_Pany_nameADD_PVALUE_Popt_if_not_existsSconst
46224622
{
46234623
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46244624
n->typeName =$3;
4625-
n->newVal =$6;
4625+
n->newVal =$7;
46264626
n->newValNeighbor =NULL;
46274627
n->newValIsAfter =true;
4628+
n->skipIfExists =$6;
46284629
$$ = (Node *) n;
46294630
}
4630-
|ALTERTYPE_Pany_nameADD_PVALUE_PSconstBEFORESconst
4631+
|ALTERTYPE_Pany_nameADD_PVALUE_Popt_if_not_existsSconstBEFORESconst
46314632
{
46324633
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46334634
n->typeName =$3;
4634-
n->newVal =$6;
4635-
n->newValNeighbor =$8;
4635+
n->newVal =$7;
4636+
n->newValNeighbor =$9;
46364637
n->newValIsAfter =false;
4638+
n->skipIfExists =$6;
46374639
$$ = (Node *) n;
46384640
}
4639-
|ALTERTYPE_Pany_nameADD_PVALUE_PSconstAFTERSconst
4641+
|ALTERTYPE_Pany_nameADD_PVALUE_Popt_if_not_existsSconstAFTERSconst
46404642
{
46414643
AlterEnumStmt *n = makeNode(AlterEnumStmt);
46424644
n->typeName =$3;
4643-
n->newVal =$6;
4644-
n->newValNeighbor =$8;
4645+
n->newVal =$7;
4646+
n->newValNeighbor =$9;
46454647
n->newValIsAfter =true;
4648+
n->skipIfExists =$6;
46464649
$$ = (Node *) n;
46474650
}
46484651
;
46494652

4653+
opt_if_not_exists:IF_PNOTEXISTS {$$ =true; }
4654+
|/* empty*/ {$$ =false; }
4655+
;
4656+
46504657

46514658
/*****************************************************************************
46524659
*

‎src/include/catalog/pg_enum.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,7 @@ typedef FormData_pg_enum *Form_pg_enum;
6565
externvoidEnumValuesCreate(OidenumTypeOid,List*vals);
6666
externvoidEnumValuesDelete(OidenumTypeOid);
6767
externvoidAddEnumLabel(OidenumTypeOid,constchar*newVal,
68-
constchar*neighbor,boolnewValIsAfter);
68+
constchar*neighbor,boolnewValIsAfter,
69+
boolskipIfExists);
6970

7071
#endif/* PG_ENUM_H */

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2306,6 +2306,7 @@ typedef struct AlterEnumStmt
23062306
char*newVal;/* new enum value's name */
23072307
char*newValNeighbor;/* neighboring enum value, if specified */
23082308
boolnewValIsAfter;/* place new enum value after neighbor? */
2309+
boolskipIfExists;/* ignore statement if label already exists */
23092310
}AlterEnumStmt;
23102311

23112312
/* ----------------------

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,28 @@ ERROR: invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutopl
9595
DETAIL: Labels must be 63 characters or less.
9696
ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
9797
ERROR: "zeus" is not an existing enum label
98+
-- if not exists tests
99+
-- existing value gives error
100+
-- We can't do this test because the error contains the
101+
-- offending Oid value, which is unpredictable.
102+
-- ALTER TYPE planets ADD VALUE 'mercury';
103+
-- unless IF NOT EXISTS is specified
104+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
105+
-- should be neptune, not mercury
106+
SELECT enum_last(NULL::planets);
107+
enum_last
108+
-----------
109+
neptune
110+
(1 row)
111+
112+
ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
113+
-- should be pluto, i.e. the new value
114+
SELECT enum_last(NULL::planets);
115+
enum_last
116+
-----------
117+
pluto
118+
(1 row)
119+
98120
--
99121
-- Test inserting so many values that we have to renumber
100122
--

‎src/test/regress/sql/enum.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,26 @@ ALTER TYPE planets ADD VALUE
5454

5555
ALTERTYPE planets ADD VALUE'pluto' AFTER'zeus';
5656

57+
-- if not exists tests
58+
59+
-- existing value gives error
60+
61+
-- We can't do this test because the error contains the
62+
-- offending Oid value, which is unpredictable.
63+
-- ALTER TYPE planets ADD VALUE 'mercury';
64+
65+
-- unless IF NOT EXISTS is specified
66+
ALTERTYPE planets ADD VALUE IF NOT EXISTS'mercury';
67+
68+
-- should be neptune, not mercury
69+
SELECT enum_last(NULL::planets);
70+
71+
ALTERTYPE planets ADD VALUE IF NOT EXISTS'pluto';
72+
73+
-- should be pluto, i.e. the new value
74+
SELECT enum_last(NULL::planets);
75+
76+
5777
--
5878
-- Test inserting so many values that we have to renumber
5979
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp