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 this
operation inside a transaction block.

Andrew Dunstan, reviewed by Tom Lane and Magnus Hagander.

diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml
index 6386085a608877bf15a6b80aa3f3d6fe0b48959b..1ff53923fb6fd6a665802a497cf81890b3fbe3c1 100644 (file)
--- a/doc/src/sgml/ref/alter_type.sgml
+++ b/doc/src/sgml/ref/alter_type.sgml
@@ -28,7 +28,7 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> OWNER TO <replaceab
 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME ATTRIBUTE <replaceable class="PARAMETER">attribute_name</replaceable> TO <replaceable class="PARAMETER">new_attribute_name</replaceable>
 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable class="PARAMETER">new_name</replaceable> [ CASCADE | RESTRICT ]
 ALTER TYPE <replaceable class="PARAMETER">name</replaceable> SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
-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> ]
+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> ]
 
 <phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase>
 
@@ -106,7 +106,7 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replacea
    </varlistentry>
 
    <varlistentry>
-    <term><literal>ADD VALUE [ BEFORE | AFTER ]</literal></term>
+    <term><literal>ADD VALUE [ IF NOT EXISTS ] [ BEFORE | AFTER ]</literal></term>
     <listitem>
      <para>
       This form adds a new value to an enum type. If the new value's place in
@@ -114,6 +114,11 @@ ALTER TYPE <replaceable class="PARAMETER">name</replaceable> ADD VALUE <replacea
       <literal>AFTER</literal>, then the new item is placed at the end of the
       list of values.
      </para>
+     <para>
+      If <literal>IF NOT EXISTS</literal is used, it is not an error if the
+      type already contains the new value, and no action  is taken. Otherwise,
+      an error will occur if the new value is already present.
+     </para>
     </listitem>
    </varlistentry>
 

diff --git a/src/backend/catalog/pg_enum.c b/src/backend/catalog/pg_enum.c
index 8ddb376bc16875130d2efea05c8959fc7c5ecdbd..f3161efb20b94ccb7b8f2707caf052b53f05c67c 100644 (file)
--- a/src/backend/catalog/pg_enum.c
+++ b/src/backend/catalog/pg_enum.c
@@ -179,7 +179,8 @@ void
 AddEnumLabel(Oid enumTypeOid,
             const char *newVal,
             const char *neighbor,
-            bool newValIsAfter)
+            bool newValIsAfter,
+            bool skipIfExists)
 {
    Relation    pg_enum;
    Oid         newOid;
@@ -211,6 +212,21 @@ AddEnumLabel(Oid enumTypeOid,
     */
    LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);
 
+   /* Do the "IF NOT EXISTS" test if specified */
+   if (skipIfExists)
+   {
+       HeapTuple tup;
+
+       tup = SearchSysCache2(ENUMTYPOIDNAME,
+                             ObjectIdGetDatum(enumTypeOid),
+                             CStringGetDatum(newVal));
+       if (HeapTupleIsValid(tup))
+       {
+           ReleaseSysCache(tup);
+           return;
+       }
+   }
+
    pg_enum = heap_open(EnumRelationId, RowExclusiveLock);
 
    /* If we have to renumber the existing members, we restart from here */

diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 4c9d00386cbb4fbcfd4ea2295e7e887f6b2e3187..6cb6fd56fd9a668bf0efa7b17b2a8ad30cfadc7c 100644 (file)
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1188,7 +1188,8 @@ AlterEnum(AlterEnumStmt *stmt)
 
    /* Add the new label */
    AddEnumLabel(enum_type_oid, stmt->newVal,
-                stmt->newValNeighbor, stmt->newValIsAfter);
+                stmt->newValNeighbor, stmt->newValIsAfter, 
+                stmt->skipIfExists);
 
    ReleaseSysCache(tup);
 }

diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index f34f7049e4331a1965153e64d707701a5fe34f24..34d4f40fe2383acb89a15e203bb08f26ae4004dd 100644 (file)
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3055,6 +3055,7 @@ _copyAlterEnumStmt(const AlterEnumStmt *from)
    COPY_STRING_FIELD(newVal);
    COPY_STRING_FIELD(newValNeighbor);
    COPY_SCALAR_FIELD(newValIsAfter);
+   COPY_SCALAR_FIELD(skipIfExists);
 
    return newnode;
 }

diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index b4b1c22336367fd37a1f201e429affd9e35540bb..f63f4973db579bef659e4b00e10587f7b025bc9d 100644 (file)
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1439,6 +1439,7 @@ _equalAlterEnumStmt(const AlterEnumStmt *a, const AlterEnumStmt *b)
    COMPARE_STRING_FIELD(newVal);
    COMPARE_STRING_FIELD(newValNeighbor);
    COMPARE_SCALAR_FIELD(newValIsAfter);
+   COMPARE_SCALAR_FIELD(skipIfExists);
 
    return true;
 }

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 5894cb0885cccf2fd4ba2a7d0d84fdfdd160b0bc..ec88b7107698e7864e206cf1fc76e000eea12621 100644 (file)
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -470,7 +470,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
 %type <windef> window_definition over_clause window_specification
                opt_frame_clause frame_extent frame_bound
 %type <str>        opt_existing_window_name
-
+%type <boolean> opt_if_not_exists
 
 /*
  * Non-keyword token types.  These are hard-wired into the "flex" lexer.
@@ -4618,35 +4618,42 @@ enum_val_list:  Sconst
  *****************************************************************************/
 
 AlterEnumStmt:
-       ALTER TYPE_P any_name ADD_P VALUE_P Sconst
+       ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst
            {
                AlterEnumStmt *n = makeNode(AlterEnumStmt);
                n->typeName = $3;
-               n->newVal = $6;
+               n->newVal = $7;
                n->newValNeighbor = NULL;
                n->newValIsAfter = true;
+               n->skipIfExists = $6;
                $$ = (Node *) n;
            }
-        | ALTER TYPE_P any_name ADD_P VALUE_P Sconst BEFORE Sconst
+        | ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst BEFORE Sconst
            {
                AlterEnumStmt *n = makeNode(AlterEnumStmt);
                n->typeName = $3;
-               n->newVal = $6;
-               n->newValNeighbor = $8;
+               n->newVal = $7;
+               n->newValNeighbor = $9;
                n->newValIsAfter = false;
+               n->skipIfExists = $6;
                $$ = (Node *) n;
            }
-        | ALTER TYPE_P any_name ADD_P VALUE_P Sconst AFTER Sconst
+        | ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst AFTER Sconst
            {
                AlterEnumStmt *n = makeNode(AlterEnumStmt);
                n->typeName = $3;
-               n->newVal = $6;
-               n->newValNeighbor = $8;
+               n->newVal = $7;
+               n->newValNeighbor = $9;
                n->newValIsAfter = true;
+               n->skipIfExists = $6;
                $$ = (Node *) n;
            }
         ;
 
+opt_if_not_exists: IF_P NOT EXISTS              { $$ = true; }
+         | /* empty */                          { $$ = false; }
+         ;
+
 
 /*****************************************************************************
  *

diff --git a/src/include/catalog/pg_enum.h b/src/include/catalog/pg_enum.h
index 91c1ab1de7d764e7823e9a78ce6754620be8c0ea..8842a8706b5647fca90cd3dc3fd98655d07e9143 100644 (file)
--- a/src/include/catalog/pg_enum.h
+++ b/src/include/catalog/pg_enum.h
@@ -65,6 +65,7 @@ typedef FormData_pg_enum *Form_pg_enum;
 extern void EnumValuesCreate(Oid enumTypeOid, List *vals);
 extern void EnumValuesDelete(Oid enumTypeOid);
 extern void AddEnumLabel(Oid enumTypeOid, const char *newVal,
-            const char *neighbor, bool newValIsAfter);
+                        const char *neighbor, bool newValIsAfter, 
+                        bool skipIfExists);
 
 #endif   /* PG_ENUM_H */

diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 19178b55512f4dd10a01929900817a0e9ffa18c8..98fe850c927c03d23dae36949dda2bf2a70166db 100644 (file)
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2306,6 +2306,7 @@ typedef struct AlterEnumStmt
    char       *newVal;         /* new enum value's name */
    char       *newValNeighbor; /* neighboring enum value, if specified */
    bool        newValIsAfter;  /* place new enum value after neighbor? */
+   bool        skipIfExists;   /* ignore statement if label already exists */
 } AlterEnumStmt;
 
 /* ----------------------

diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out
index bf94af5ef6e9c252680dc5b3292d1c496fb446a9..a14097297a10e33392cac302f355af81c41ad00f 100644 (file)
--- a/src/test/regress/expected/enum.out
+++ b/src/test/regress/expected/enum.out
@@ -95,6 +95,28 @@ ERROR:  invalid enum label "plutoplutoplutoplutoplutoplutoplutoplutoplutoplutopl
 DETAIL:  Labels must be 63 characters or less.
 ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
 ERROR:  "zeus" is not an existing enum label
+-- if not exists tests
+--  existing value gives error
+-- We can't do this test because the error contains the
+-- offending Oid value, which is unpredictable.
+-- ALTER TYPE planets ADD VALUE 'mercury';
+-- unless IF NOT EXISTS is specified
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
+-- should be neptune, not mercury
+SELECT enum_last(NULL::planets);
+ enum_last 
+-----------
+ neptune
+(1 row)
+
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
+-- should be pluto, i.e. the new value
+SELECT enum_last(NULL::planets);
+ enum_last 
+-----------
+ pluto
+(1 row)
+
 --
 -- Test inserting so many values that we have to renumber
 --

diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sql
index 7ca624830968dd8e24fbbfc2879fe1bf7b5ca133..db7bf44b408ab5970a4ede560229c09774321a77 100644 (file)
--- a/src/test/regress/sql/enum.sql
+++ b/src/test/regress/sql/enum.sql
@@ -54,6 +54,26 @@ ALTER TYPE planets ADD VALUE
 
 ALTER TYPE planets ADD VALUE 'pluto' AFTER 'zeus';
 
+-- if not exists tests
+
+--  existing value gives error
+
+-- We can't do this test because the error contains the
+-- offending Oid value, which is unpredictable.
+-- ALTER TYPE planets ADD VALUE 'mercury';
+
+-- unless IF NOT EXISTS is specified
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'mercury';
+
+-- should be neptune, not mercury
+SELECT enum_last(NULL::planets);
+
+ALTER TYPE planets ADD VALUE IF NOT EXISTS 'pluto';
+
+-- should be pluto, i.e. the new value
+SELECT enum_last(NULL::planets);
+
+
 --
 -- Test inserting so many values that we have to renumber
 --