IMMUTABLE function to cast enum to/from text?

From: Philip Semanchuk <philip(at)americanefficient(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: IMMUTABLE function to cast enum to/from text?
Date: 2022-11-10 19:52:24
Message-ID: EBFE2A74-B421-4494-8FCC-9F072F128188@americanefficient.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,
I know that Postgres' enum_in()/enum_out() functions have a volatility class of STABLE, and STABLE is required because enum elements can be renamed. We have an enum in our database used in a number of custom functions, all of which require casting the enum to/from text. Since enum_in() and enum_out() are STABLE, that means our functions that rely on those casts must also be STABLE, and as a result we can't use them in generated columns.

I have seen conversations that suggest creating a custom IMMUTABLE function to perform the cast, but I can't figure out how to do that except with a CASE statement that enumerates every possible value. Is there a more elegant approach?

Thanks
Philip

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2022-11-10 20:18:19 Re: IMMUTABLE function to cast enum to/from text?
Previous Message Karsten Hilbert 2022-11-10 17:36:04 Aw: Re: Q: documentation improvement re collation version mismatch