pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
IMMUTABLE function to cast enum to/from text?
4+ messages / 3 participants
[nested] [flat]

* IMMUTABLE function to cast enum to/from text?
@ 2022-11-10 19:52  Philip Semanchuk <philip@americanefficient.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Philip Semanchuk @ 2022-11-10 19:52 UTC (permalink / raw)
  To: PostgreSQL General <pgsql-general@lists.postgresql.org>

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: IMMUTABLE function to cast enum to/from text?
@ 2022-11-10 20:18  Joe Conway <mail@joeconway.com>
  parent: Philip Semanchuk <philip@americanefficient.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Joe Conway @ 2022-11-10 20:18 UTC (permalink / raw)
  To: Philip Semanchuk <philip@americanefficient.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: IMMUTABLE function to cast enum to/from text?
@ 2022-11-10 20:39  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2022-11-10 20:39 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Philip Semanchuk <philip@americanefficient.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: IMMUTABLE function to cast enum to/from text?
@ 2022-11-10 23:33  Philip Semanchuk <philip@americanefficient.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Philip Semanchuk @ 2022-11-10 23:33 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joe Conway <mail@joeconway.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>



> 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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2022-11-10 23:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-10 19:52 IMMUTABLE function to cast enum to/from text? Philip Semanchuk <philip@americanefficient.com>
2022-11-10 20:18 ` Joe Conway <mail@joeconway.com>
2022-11-10 20:39   ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-10 23:33     ` Philip Semanchuk <philip@americanefficient.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox