IMMUTABLE function to cast enum to/from text?

On 11/10/22 14:52, Philip Semanchuk wrote:

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?

When asking for help here, it always helps us to help you if you provide
a self-contained set of SQL that illustrates what you are looking for.

That said, I think you are looking for something like this:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (name text, current_mood mood);
INSERT INTO person VALUES ('Moe', 'happy');
CREATE OR REPLACE FUNCTION mood2text(mood)
RETURNS text AS
$$
select $1
$$ STRICT IMMUTABLE LANGUAGE sql;

SELECT name, mood2text(current_mood) FROM person;
name | mood2text
------+-----------
Moe | happy
(1 row)

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Joe Conway <mail@joeconway.com> writes:

On 11/10/22 14:52, Philip Semanchuk wrote:

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?

CREATE OR REPLACE FUNCTION mood2text(mood)
RETURNS text AS
$$
select $1
$$ STRICT IMMUTABLE LANGUAGE sql;

Of course, what this is doing is using a SQL-function wrapper to
lie about the mutability of the expression. Whether you consider
that elegant is up to you ;-) ... but it should work, as long as
you don't break things by renaming the enum's values.

regards, tom lane

On Nov 10, 2022, at 3:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joe Conway <mail@joeconway.com> writes:

CREATE OR REPLACE FUNCTION mood2text(mood)
RETURNS text AS
$$
select $1
$$ STRICT IMMUTABLE LANGUAGE sql;

Of course, what this is doing is using a SQL-function wrapper to
lie about the mutability of the expression. Whether you consider
that elegant is up to you ;-) ... but it should work, as long as
you don't break things by renaming the enum's values.

Thanks Joe and Tom,
I’m comfortable lying to Postgres occasionally — never for evil, only for good of course. :-)

Cheers
Philip