Add multiple PostgreSQL enumeration values ​​with a single command

Question

Since PostgreSQL 9.1 enum values ​​can be added using

ALTER TYPE my_type ADD VALUE new_value;

However, trying to run this as part of a larger script gives an error:

ALTER TYPE ... ADD cannot be executed from a function or string with multiple commands

This makes a real hassle for script changes to be applied in production, because service personnel must remember that although most scripts can be run “normally”, there are several “special” scripts that must be opened in pgAdmin and run manually, by parts. I have Googled this, and I understand that the limitation is that enumeration values ​​cannot be added inside a transaction or part of a "string with multiple commands." This is normal. I do not need to do this. I just want to add some enum values ​​and execute other, unrelated SQL statements without loading Postgres one statement at a time.

In other words: I want to be able to run one script, both from pgAdmin and psql, which just does the same thing as pgAdmin, when I select one line at a time and press F5 (run), is there a way to do this maybe with plpgsql?

Answer 1

It looks like this will be fixed in PostgreSQL 12.

https://www.postgresql.org/docs/12/sql-altertype.html says

If ALTER TYPE ... ADD VALUE (a form that adds a new value to an enumeration type) is executed inside a transaction block, the new value cannot be used until the transaction is committed.