PostgreSQL:是否可以将枚举转换为整数？

Question

是否可以将枚举转换为整数？从1开始第一个元素

Answer 1

虽然您不能像 Catcall 解释的那样将枚举转换为整数，但您可以使用特定于 PostgreSQL 且可能版本不兼容的 pg_enum系统目录表以获得序号表示。

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 输入返回 null

您现在可以使用此函数为 integer 的特定枚举CREATE CAST。您不能为所有枚举创建通用类型转换为 integer，因为 anyenum 伪类型不能用于类型转换。例如，如果我想让演示 happiness 转换为整数，我会这样写:

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

之后我可以成功执行:

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

请注意，这可能是一件疯狂的事情，不受支持，而且很可能是一个糟糕的想法。您的代码必须知道，当您添加或(如果以后支持)从枚举中删除一个值时，序数值将会改变。

如果您更改枚举的定义(除非在其末尾附加新值)，基于此转换创建的索引(仅当函数定义为不可变时才有可能)将开始产生疯狂和错误的结果，因为 PostgreSQL 相信您当你说一个函数是不可变的。不要那样做。