我在postgresql中有ENUM类型
CREATE TYPE user_state AS ENUM ('unconfirmed', 'locked', 'active');
我有一个字段state
表users
与类型user_state
.
我尝试执行下一个查询:
UPDATE "users" SET "state" = 'active'::character varying WHERE "id" = 1 (*1)
但是查询失败了.错误是: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:
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会告诉我堆栈溢出(这就是为什么我现在在这个网站上,ha-ha).错误是:
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" = ? where "id" = ? jdbcParams:[active,10]
和我完全按照我上面写的那样回答(关于重写或转换表达式).所以,给出了查询as is
,我无法改变它:我不能只删除:: character vary或add :: user_state或CAST(.. as ..).
UPD.还有人可以尝试在较新版本的postgresql(9.2,9.3,9.4)上运行代码吗?如果它有效,那也是答案.
一个简单的尝试的问题是你有一个演员调用演员,调用演员,调用演员......
你需要以某种方式远离你的演员阵容中的varchar-> enum.最简单(最易理解)的方法是手动转换.请注意,在case语句中强制转换的字符串文字不是它们引用的文本 - unknown-type,它会回避无限递归.
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;