Greetings, everyone!
I would like to offer my patch on the problem of removing values from enums
It adds support for expression ALTER TYPE <enum_name> DROP VALUE
<value_name>
Added:
1. expression in grammar
2. function to drop enum values
3. regression tests
4. documentation
Subject: [PATCH] Add DROP VALUE for ALTER TYPE with enum
---
Index: doc/src/sgml/datatype.sgml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
--- a/doc/src/sgml/datatype.sgml (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/doc/src/sgml/datatype.sgml (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -3294,10 +3294,10 @@
<para>
Although enum types are primarily intended for static sets of values,
- there is support for adding new values to an existing enum type, and for
- renaming values (see <xref linkend="sql-altertype"/>). Existing values
- cannot be removed from an enum type, nor can the sort ordering of such
- values be changed, short of dropping and re-creating the enum type.
+ there is support for adding new values to an existing enum type, and
+ also for dropping and renaming values (see <xref linkend="sql-altertype"/>).
+ The sorting order of such values cannot be changed, except for deleting
+ and re-creating the enum type.
</para>
<para>
Index: doc/src/sgml/ref/alter_type.sgml
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml
--- a/doc/src/sgml/ref/alter_type.sgml (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/doc/src/sgml/ref/alter_type.sgml (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -30,6 +30,7 @@
ALTER TYPE <replaceable class="parameter">name</replaceable> <replaceable class="parameter">action</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">neighbor_enum_value</replaceable> ]
ALTER TYPE <replaceable class="parameter">name</replaceable> RENAME VALUE <replaceable class="parameter">existing_enum_value</replaceable> TO <replaceable class="parameter">new_enum_value</replaceable>
+ALTER TYPE <replaceable class="parameter">name</replaceable> DROP VALUE <replaceable class="parameter">existing_enum_value</replaceable>
ALTER TYPE <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">property</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )
<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase>
@@ -145,6 +146,15 @@
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>DROP VALUE</literal></term>
+ <listitem>
+ <para>
+ This form drops a value of an enum type.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term>
@@ -462,6 +472,13 @@
ALTER TYPE colors RENAME VALUE 'purple' TO 'mauve';
</programlisting>
</para>
+
+ <para>
+ To drop an enum value:
+<programlisting>
+ALTER TYPE colors DROP VALUE 'red';
+</programlisting>
+ </para>
<para>
To create binary I/O functions for an existing base type:
Index: src/backend/catalog/pg_enum.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/catalog/pg_enum.c b/src/backend/catalog/pg_enum.c
--- a/src/backend/catalog/pg_enum.c (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/backend/catalog/pg_enum.c (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -612,6 +612,62 @@
table_close(pg_enum, RowExclusiveLock);
}
+void
+DropEnumLabel(Oid enumTypeOid, const char *oldVal)
+{
+ Relation pg_enum;
+ HeapTuple enum_tup;
+ Form_pg_enum en;
+ CatCList *list;
+ int nelems;
+ HeapTuple old_tup;
+ int i;
+
+
+ /*
+ * Acquire a lock on the enum type, which we won't release until commit.
+ * This ensures that two backends aren't concurrently modifying the same
+ * enum type. Since we are not changing the type's sort order, this is
+ * probably not really necessary, but there seems no reason not to take
+ * the lock to be sure.
+ */
+ LockDatabaseObject(TypeRelationId, enumTypeOid, 0, ExclusiveLock);
+
+ pg_enum = table_open(EnumRelationId, RowExclusiveLock);
+
+ /* Get the list of existing members of the enum */
+ list = SearchSysCacheList1(ENUMTYPOIDNAME,
+ ObjectIdGetDatum(enumTypeOid));
+ nelems = list->n_members;
+
+ /*
+ * Locate the element to rename and check if the new label is already in
+ * use. (The unique index on pg_enum would catch that anyway, but we
+ * prefer a friendlier error message.)
+ */
+ old_tup = NULL;
+ for (i = 0; i < nelems; i++)
+ {
+ enum_tup = &(list->members[i]->tuple);
+ en = (Form_pg_enum) GETSTRUCT(enum_tup);
+ if (strcmp(NameStr(en->enumlabel), oldVal) == 0)
+ {
+ old_tup = enum_tup;
+ }
+ }
+
+ ReleaseCatCacheList(list);
+
+ if (!old_tup)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("\"%s\" is not an existing enum label",
+ oldVal)));
+
+ CatalogTupleDelete(pg_enum, &old_tup->t_self);
+ table_close(pg_enum, RowExclusiveLock);
+}
+
/*
* Test if the given enum value is in the table of uncommitted enums.
Index: src/backend/commands/typecmds.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
--- a/src/backend/commands/typecmds.c (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/backend/commands/typecmds.c (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -1280,8 +1280,16 @@
if (stmt->oldVal)
{
- /* Rename an existing label */
- RenameEnumLabel(enum_type_oid, stmt->oldVal, stmt->newVal);
+ if(stmt->newVal)
+ {
+ /* Rename an existing label */
+ RenameEnumLabel(enum_type_oid, stmt->oldVal, stmt->newVal);
+ }
+ else
+ {
+ /* Delete an existing label */
+ DropEnumLabel(enum_type_oid, stmt->oldVal);
+ }
}
else
{
Index: src/backend/parser/gram.y
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
--- a/src/backend/parser/gram.y (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/backend/parser/gram.y (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -6359,7 +6359,7 @@
ALTER TYPE_P any_name ADD_P VALUE_P opt_if_not_exists Sconst
{
AlterEnumStmt *n = makeNode(AlterEnumStmt);
-
+
n->typeName = $3;
n->oldVal = NULL;
n->newVal = $7;
@@ -6404,6 +6404,18 @@
n->skipIfNewValExists = false;
$$ = (Node *) n;
}
+ | ALTER TYPE_P any_name DROP VALUE_P Sconst
+ {
+ AlterEnumStmt *n = makeNode(AlterEnumStmt);
+
+ n->typeName = $3;
+ n->oldVal = $6;
+ n->newVal = NULL;
+ n->newValNeighbor = NULL;
+ n->newValIsAfter = false;
+ n->skipIfNewValExists = false;
+ $$ = (Node *) n;
+ }
;
opt_if_not_exists: IF_P NOT EXISTS { $$ = true; }
Index: src/include/catalog/pg_enum.h
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/include/catalog/pg_enum.h b/src/include/catalog/pg_enum.h
--- a/src/include/catalog/pg_enum.h (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/include/catalog/pg_enum.h (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -57,6 +57,7 @@
bool skipIfExists);
extern void RenameEnumLabel(Oid enumTypeOid,
const char *oldVal, const char *newVal);
+extern void DropEnumLabel(Oid enumTypeOid, const char *oldVal);
extern bool EnumUncommitted(Oid enum_id);
extern Size EstimateUncommittedEnumsSpace(void);
extern void SerializeUncommittedEnums(void *space, Size size);
Index: src/test/regress/expected/enum.out
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.out
--- a/src/test/regress/expected/enum.out (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/test/regress/expected/enum.out (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -605,6 +605,28 @@
-- check that renaming to an existent value fails
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
ERROR: enum label "green" already exists
+-- check dropping a value
+ALTER TYPE rainbow DROP VALUE 'purple';
+ALTER TYPE rainbow DROP VALUE 'blue';
+ALTER TYPE rainbow DROP VALUE 'green';
+ALTER TYPE rainbow DROP VALUE 'yellow';
+ALTER TYPE rainbow DROP VALUE 'orange';
+ALTER TYPE rainbow DROP VALUE 'crimson';
+SELECT enum_range(NULL::rainbow);
+ enum_range
+------------
+ {}
+(1 row)
+
+ALTER TYPE rainbow DROP VALUE 'purple';
+ERROR: "purple" is not an existing enum label
+ALTER TYPE rainbow ADD VALUE 'purple';
+SELECT enum_range(NULL::rainbow);
+ enum_range
+------------
+ {purple}
+(1 row)
+
--
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
--
Index: src/test/regress/sql/enum.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sql
--- a/src/test/regress/sql/enum.sql (revision 1f9e3a9be539f912babd3ad58d01a4ce6aa0b85b)
+++ b/src/test/regress/sql/enum.sql (revision 2984db4be6d2a4afc37ba15049c6365b880b6609)
@@ -276,6 +276,20 @@
-- check that renaming to an existent value fails
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
+-- check dropping a value
+ALTER TYPE rainbow DROP VALUE 'purple';
+ALTER TYPE rainbow DROP VALUE 'blue';
+ALTER TYPE rainbow DROP VALUE 'green';
+ALTER TYPE rainbow DROP VALUE 'yellow';
+ALTER TYPE rainbow DROP VALUE 'orange';
+ALTER TYPE rainbow DROP VALUE 'crimson';
+SELECT enum_range(NULL::rainbow);
+
+ALTER TYPE rainbow DROP VALUE 'purple';
+
+ALTER TYPE rainbow ADD VALUE 'purple';
+SELECT enum_range(NULL::rainbow);
+
--
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
--
