postgresql：枚举和字符变化，更新 [英] postgresql: enum and character varying, updating

问题描述

我在postgresql中有ENUM类型

I have the ENUM type in postgresql

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

我在表格中有一个字段 state code>用户，类型为 user_state 。

I have a field state in table users with the type user_state.

我尝试执行下一个查询：

And I try to execute the next query:

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

但查询失败。错误是：列state的类型为user_state，但是表达式的类型字符变化。提示：您需要重写或转换表达式。

But the query fails. The error is: Column "state" is of type user_state but expression is of type character varying. Hint: You will need to rewrite or cast the expression.

好吧，有一点阅读的人，写一点的代码，并且我有隐式类型转换为变化到user_state的字符：

Okay, there were a little bit of reading mans, a writing a little bit of code, and I have implicit type cast for character varying to 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会说我堆栈溢出（这就是为什么我现在在这个网站，哈哈）。错误是：

But this does not work. It works only in case if id does not exist (so, query does nothing, so, syntax and type checking are correct now I suppose). But if id exists, postgresql says me that stack is overflowed (that is why I'm on this site now, ha-ha). The error is:

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

而且不知道如何解决它。任何想法？

And I have no idea how to fix it. Any ideas?

Postgresql 9.1和升级程序记录（版本2.5）。

Postgresql 9.1 and lift-squeryl-record (of version 2.5).

（* 1 ）查询更新用户SET状态='活动'::字符变化WHEREid= 1 不是一个真正的。我使用squeryl（scala-language orm），而squeryl生成查询，所以我不能仅仅删除 ::字符变化来使其工作。真正的查询看起来像这样（在错误答案）：更新用户设置状态=？ id=？ jdbcParams：[active，10] 并且对我完全按照我上面提到的问题（关于重写或转换表达式）进行回答。所以，查询被赋予 as as ，我不能改变它：我不能删除::字符变化或添加:: user_state或CAST（.. as .. ）。

(*1) The query UPDATE "users" SET "state" = 'active'::character varying WHERE "id" = 1 is not a real one. I use squeryl (scala-language orm), and the squeryl generates the query, so, I can't just remove ::character varying to have it work. Real query looks like this (in error answer): update "users" set "state" = ? where "id" = ? jdbcParams:[active,10] and answers to me exactly as I wrote in question above (about rewrite or cast the expression). So, the query is given as is and I cant change it: I can't just remove ::character varying or add ::user_state or CAST(.. as ..).

UPD。还有人可以在较新版本的postgresql（9.2,9.3,9.4）上尝试运行代码？

UPD. Could also somebody try run code on a newer version of postgresql (9.2, 9.3, 9.4)? If it works, it is the answer too.

推荐答案

一个简单的尝试的问题是你有一个演员调用演员，调用演员，调用演员...

The problem with a simple attempt is you have a cast calling the cast, calling the cast, calling the cast...

您需要以某种方式摆脱您的演员中的varchar->枚举。最简单（最易理解）的方式是手动转换。注意在case语句中被转换的字符串文字不是它们被引用的文本 - 未知类型，它避开无限递归。

You need somehow to get away from varchar->enum in your cast. Simplest (most understandable) way is to just manually convert. Note the string literals being cast in the case statement aren't text they are quoted-unknown-type which sidesteps the infinite recursion.

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;

这篇关于postgresql：枚举和字符变化，更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！