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