Add multiple PostgreSQL enum values with one 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 bigger script gives an error:

ALTER TYPE ... ADD cannot be executed from a function or multi-command string

This makes it a real hassle to script changes to be applied in production, because support staff have to remember that, while most scripts can be run "normally", there are a few "special" scripts that need to be opened in pgAdmin and run manually, piece by piece. I've Googled this and I understand the limitation - enum values cannot be added inside a transaction or part of a "multi-command string". That's OK. I don't need to do that. I just want to add multiple enum values and execute other, unrelated, SQL statements without having to feed Postgres one statement at a time.

In other words: I want to be able to run a single script, both from pgAdmin and from psql, that simply does the same thing that pgAdmin does when I highlight one line at a time and press F5 (run). Is there a way to do this, maybe with plpgsql?

Answer 1

Looks like this will finally be fixed in PostgreSQL 12.

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

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