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

PostgreSQL: Is it possible cast enum to integer?

提问by Alberto

Is it possible cast enum to integer? Starting from 1 the first element

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

回答by Craig Ringer

While you can't cast enum to integer as Catcall explained, you canuse the PostgreSQL-specific and possibly not-version-to-version compatible pg_enumsystem catalog table to get an ordinal representation.

虽然您不能像 Catcall 解释的那样将 enum 转换为整数，但您可以使用特定于 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)

This looks easy, but it isn't. Observe:

这看起来很容易，但事实并非如此。观察：

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)

From this you can see that enumsortorderprovides ordering, but no fixed 'distance'. If support for removing values from enums is ever added, it'll likely create 'holes' in the sequence, too.

由此您可以看到，enumsortorder提供了排序，但没有固定的“距离”。如果添加了对从枚举中删除值的支持，它也可能会在序列中创建“漏洞”。

To get the enum position you'll need to use the row_number()window function to get the ordering, and the pg_typeofto get the oid(regtype) of the enum type. You need this to make sure that you return the right ordinal when there are multiple enums with the same label.

要获得枚举位置，您需要使用row_number()window 函数来获取排序，并使用pg_typeof来获取枚举类型的oid( regtype)。您需要这样做以确保在有多个具有相同标签的枚举时返回正确的序数。

This function does the job:

此功能完成以下工作：

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()
    ) enum_ordering
    WHERE enumlabel = (::text);
$$ LANGUAGE 'SQL' STABLE STRICT;

Note:

笔记：

• It's STABLEnot IMMUTABLE, because adding (or if support in Pg is later added, removing) values from enums would change the ordering and break indexes relying on the ordering; so
• You cannot use this in an index expression; and
• It's STRICTbecause it should return null for a null input

• 这STABLE不IMMUTABLE，因为添加来自枚举（或者如果PG支持以后添加，删除）值将改变排序和休息指数依托排序; 所以
• 您不能在索引表达式中使用它；和
• 这是STRICT因为它应该为空输入返回空

You can now use this function to CREATE CASTfor specific enums to integer. You cannot create a generic cast for all enums to integer, because the anyenumpseudo-type cannot be used for casts. For example, if I want to allow the demo happinessto be cast to integer, I would write:

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

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

after which I could successfully execute:

之后我可以成功执行：

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

Note that this is probably an insanething to do, is unsupported, and is quite likely a terrible idea. Your code must be aware that the ordinal values will changewhen you add or (if later supported) remove a value from the enum.

请注意，这可能是一件疯狂的事情，不受支持，而且很可能是一个糟糕的主意。您的代码必须知道，当您从枚举中添加或（如果稍后支持）删除值时，序号值会发生变化。

Indexes created based on this cast (only possible if the function is defined immutable) will begin producing crazy and wrong results if you change the definition of the enum (except by appending new values to the end of it) because PostgreSQL believes you when you say a function is immutable. Don't do that.

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

回答by mpontes

You can do this through a clever abuse of the function enum_range().

您可以通过巧妙地滥用函数enum_range()来做到这一点。

If you pass your enum value as the second argument of the enum_range() function, with NULL being the first, you'll get an array with all the values that enum can take up to that point. Then you just need to count them with array_length and you get an integer that represents the enum.

如果您将 enum 值作为 enum_range() 函数的第二个参数传递，NULL 是第一个参数，您将获得一个数组，其中包含 enum 到该点可以采用的所有值。然后你只需要用 array_length 计算它们，你就会得到一个代表枚举的整数。

Here's an example. This is my enum:

这是一个例子。这是我的枚举：

content=# select enum_range(null::content_state);
                        enum_range                          
--------------------------------------------------------------
 {created,deleted,preview,draft,submitted,approved,published}

And this is me figuring out the int for the "draft" value:

这是我找出“草稿”值的整数：

content=# select array_length(enum_range(NULL, 'draft'::content_state), 1);
 array_length 
--------------
            4

Caveat: removing values from the enum will make your ints point to other values, so don't use this on enums that you might want to change at some point.

警告：从枚举中删除值将使您的整数指向其他值，因此不要在您可能希望在某些时候更改的枚举上使用它。

回答by Mike Sherrill 'Cat Recall'

You can't cast an enum to integer.

您不能将枚举转换为整数。

You mightbe able to write a custom operatorto extract the number associated with a value, but I find it hard to believe that's worth the trouble.

您也许可以编写一个自定义运算符来提取与值关联的数字，但我发现很难相信这样做值得。

If I needed that kind of information, I'd have built a table and set a foreign key reference to it instead of using an enum.

如果我需要那种信息，我会建立一个表并为其设置外键引用，而不是使用枚举。