postgresql 如何将字符串值转换为枚举
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18631840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to cast string value to enum
提问by Bart Friederichs
I have a table with an enumtype in it, and I created a function to add data to that table. I want that function to be generous in what to accept, so I take a textas the enum type and want to cast it later.
我有一个包含enum类型的表，我创建了一个函数来向该表添加数据。我希望该函数在接受内容方面大方，所以我将 atext作为枚举类型，并希望稍后对其进行转换。
This is the enum:
这是枚举：
CREATE TYPE public.enum_log_priority AS ENUM (
'critical','error','warning','notice','debug'
);
And this is the function:
这是功能：
CREATE OR REPLACE FUNCTION public.log_write(
_message text,
_priority text
) RETURNS integer AS
$body$
BEGIN
_priority = lower(_priority);
INSERT INTO log (message, priority) VALUES (_message, _priority);
RETURN 0;
END
$body$
LANGUAGE 'plpgsql';
I know this doesn't work:
我知道这不起作用：
ERROR: column "priority" is of type enum_log_priority but expression is of type text
错误：列“优先级”的类型为 enum_log_priority 但表达式的类型为文本
but how can I do this?
但我该怎么做呢？
回答by Rakesh Soni
Use syntax like below during insertion
在插入过程中使用如下语法
'critical'::enum_log_priority
Please see some link as well
请参阅一些链接
http://www.postgresql.org/docs/9.1/static/datatype-enum.html
http://www.postgresql.org/docs/9.1/static/datatype-enum.html
Inserting into custom SQL types with prepared statements in java
回答by Roman Pekar
change your function like this:
像这样改变你的功能：
CREATE OR REPLACE FUNCTION public.log_write(
_message text,
_priority text
) RETURNS integer AS
$body$
BEGIN
_priority = lower(_priority);
INSERT INTO log (message, priority) VALUES (_message, _priority::enum_log_priority);
RETURN 0;
END
$body$
LANGUAGE 'plpgsql';
