postgresql:枚举和字符变化，更新

Question

我在 postgresql 中有 ENUM 类型

CREATE TYPE user_state AS ENUM ('unconfirmed', 'locked', 'active');

我在 users 表中有一个 state 字段，类型为 user_state。

然后我尝试执行下一个查询:

UPDATE "users" SET "state" = 'active'::character varying WHERE "id" = 1 (*1)

但是查询失败。错误是:列“state”的类型为 user_state，但表达式的类型为 character varying。提示:您将需要重写或转换表达式。

好吧，有一点读书人，写一点代码，我有隐式类型转换为 user_state 的字符:

CREATE OR REPLACE FUNCTION
    character_varying_to_user_status(user_state)
    RETURNS user_state AS $$
    SELECT $1::user_state
    $$ LANGUAGE SQL;

CREATE CAST (character varying AS user_state)
    WITH FUNCTION character_varying_to_user_status (character varying)
    AS IMPLICIT

但这行不通。它仅在 id 不存在的情况下才有效(因此，查询什么都不做，所以我想语法和类型检查现在是正确的)。但是如果 id 存在，postgresql 会告诉我堆栈溢出(这就是我现在在这个站点上的原因，哈哈)。错误是:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL function "character_varying_to_user_status" during inlining
SQL function "character_varying_to_user_status" during startup
SQL function "character_varying_to_user_status" statement 1
SQL function "character_varying_to_user_status" statement 1
SQL function "character_varying_to_user_status" statement 1
...
...
SQL function "character_varying_to_user_status" statement 1

In operator:
UPDATE "users" SET "state" = 'unconfirmed'::character varying WHERE "id" = 8

而且我不知道如何修复它。有什么想法吗？

Postgresql 9.1 和 lift-squeryl-record(2.5 版)。

(*1) 查询 UPDATE "users"SET "state"= 'active'::character varying WHERE "id"= 1 不是真实查询。我使用 squeryl(scala 语言 orm)，并且 squeryl 生成查询，所以，我不能只删除 ::character varying 来让它工作。真实查询看起来像这样(错误答案): update "users"set "state"= ?其中“id”=？ jdbcParams:[active,10] 并完全按照我在上面问题中所写的方式回答我(关于重写或转换表达式)。因此，查询按原样给出，我无法更改它:我不能只删除::character varying 或添加::user_state 或 CAST(.. as ..)。

更新。还有人可以尝试在较新版本的 postgresql(9.2、9.3、9.4)上运行代码吗？如果有效，这也是答案。

Answer 1

一个简单尝试的问题是你有一个类型转换调用类型转换，调用类型转换，调用类型转换......

您需要以某种方式摆脱 varchar->enum 的强制转换。最简单(最容易理解)的方法就是手动转换。请注意，在 case 语句中转换的字符串文字不是文本，它们是用引号引起来的未知类型，从而避免了无限递归。

BEGIN;

CREATE TYPE t_tl AS ENUM ('red', 'amber', 'green');

CREATE FUNCTION dummy_cast(varchar) RETURNS t_tl AS $$
    SELECT CASE $1
        WHEN 'red' THEN 'red'::t_tl
        WHEN 'amber' THEN 'amber'::t_tl
        WHEN 'green' THEN 'green'::t_tl
    END;
$$ LANGUAGE SQL;

CREATE CAST (varchar AS t_tl) WITH FUNCTION dummy_cast(varchar) AS ASSIGNMENT;

CREATE TABLE t (x t_tl);

INSERT INTO t VALUES ('red');
INSERT INTO t VALUES ('green'::varchar);

SELECT * FROM t;

ROLLBACK;