PostgreSQL：可以将枚举转换为整数吗？

regress=# CREATE TYPE happiness AS ENUM ('happy','very happy','ecstatic');

regress=# select enumsortorder,enumlabel from pg_catalog.pg_enum 
regress-# WHERE enumtypid = 'happiness'::regtype ORDER BY enumsortorder;
 enumsortorder | enumlabel  
---------------+------------
             1 | happy
             2 | very happy
             3 | ecstatic
(3 rows)

这看起来很容易,但不是这样.注意：

regress=# ALTER TYPE happiness ADD VALUE 'sad' BEFORE 'happy';
regress=# ALTER TYPE happiness ADD VALUE 'miserable' BEFORE 'very happy';
regress=# SELECT * FROM pg_enum ;
 enumtypid | enumsortorder | enumlabel  
-----------+---------------+------------
    185300 |             1 | happy
    185300 |             2 | very happy
    185300 |             3 | ecstatic
    185300 |             0 | sad
    185300 |           1.5 | miserable
(5 rows)

从这里你可以看到,enumsortorder提供订购,但没有固定的“距离”.如果添加了从枚举中删除值的支持,它也可能在序列中创建“孔”.

要获取枚举位置,您需要使用row_number()窗口函数来获取排序,而pg_typeof可以获取枚举类型的oid(regtype).您需要这样才能确保在有多个具有相同标签的枚举时返回正确的顺序.

这个功能可以做到这一点：

CREATE OR REPLACE FUNCTION enum_to_position(anyenum) RETURNS integer AS $$
SELECT enumpos::integer FROM (
        SELECT row_number() OVER (order by enumsortorder) AS enumpos,enumsortorder,enumlabel
        FROM pg_catalog.pg_enum
        WHERE enumtypid = pg_typeof($1)
    ) enum_ordering
    WHERE enumlabel = ($1::text);
$$LANGUAGE 'SQL' STABLE STRICT;

注意：

> STABLE不是IMMUTABLE,因为添加(或者如果稍后添加Pg中的支持,从枚举中删除)值将更改排序并依赖排序中断索引;所以
>您不能在索引表达式中使用它;和
>它是STRICT,因为它应该为空输入返回null

现在可以使用此函数将特定枚举的CAST CREATE转换为整数.您不能为整数创建所有枚举的通用转换,因为anyenum伪类型不能用于转换.例如,如果我想让演示幸福被转换为整数,我会写：

CREATE CAST (happiness AS integer) WITH FUNCTION enum_to_position(anyenum);

之后我可以成功执行：

regress=# SELECT ('happy'::happiness)::integer;
 int4 
------
    2
(1 row)

请注意,这可能是一个疯狂的事情,不受支持,很可能是一个可怕的想法.您的代码必须注意,当您添加或(如果稍后支持))从枚举中删除值时,序数值将更改.

基于此转换创建的索引(只有在函数定义不可变的情况下才会生效)如果更改枚举的定义(除了通过将新值附加到其结尾),将会开始产生疯狂和错误的结果,因为PostgreSQL相信您在说一个功能是不可变的.不要这样做