postgresql 创建一个声明预定义文本数组的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24938311/
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
Create a function declaring a predefined text array
提问by Edson Horacio Junior
I need to create a function in Postgres and one of the variables I declare is a predefined text array, but I don't know the syntax to set its values. This is what I have so far:
我需要在 Postgres 中创建一个函数，我声明的变量之一是预定义的文本数组，但我不知道设置其值的语法。这是我到目前为止：
CREATE OR REPLACE FUNCTION testFunction() RETURNS text
AS $$
DECLARE
TESTARRAY TEXT['value 1', 'value 2', 'value 3'];
BEGIN
return 'any text';
END;
$$ LANGUAGE 'plpgsql';
I get this error when I execute the code:
执行代码时出现此错误：
ERROR: syntax error at or near "'value 1'" LINE 5: TESTARRAY TEXT['value 1', 'value 2', 'value 3'];
ERROR: syntax error at or near "'value 1'" LINE 5: TESTARRAY TEXT['value 1', 'value 2', 'value 3'];
回答by pozs
The right syntax for default value (in a variable declaration) is { DEFAULT | := } expression.
默认值（在变量声明中）的正确语法是.{ DEFAULT | := } expression
For expression, you can use any of the array inputs.
对于expression，您可以使用任何数组输入。
F.ex. these can work:
例如 这些可以工作：
DECLARE
test1 TEXT ARRAY DEFAULT ARRAY['value 1', 'value 2', 'value 3'];
test2 TEXT[] := '{"value 1", "value 2", "value 3"}';
test3 TEXT[] DEFAULT ARRAY[]::TEXT[]; -- empty array-constructors need a cast
回答by Erwin Brandstetter
@pozsalready gave a proper answer.
@pozs已经给出了正确的答案。
In addition, when in doubt about proper syntax, you can just ask Postgres for the string literal:
此外，当对正确的语法有疑问时，您可以向 Postgres 询问字符串文字：
test=# SELECT ARRAY['value 1', 'value 2', 'value 3'];
array
---------------------------------
{"value 1","value 2","value 3"}
test=# SELECT ARRAY['foo', 'bar', 'b A "Z'];
array
---------------------
{foo,bar,"b A \"Z"}
There is a string representation for every possible value of every type.
每种类型的每个可能值都有一个字符串表示。
To get the readily quotedversion, that deals with all possible corner cases, wrap it in quote_nullable():
要获得易于引用的版本，该版本处理所有可能的极端情况，请将其包装在quote_nullable()：
test=# SELECT quote_nullable(ARRAY['value 1', 'value 2', 'value 3']);
quote_nullable
-----------------------------------
'{"value 1","value 2","value 3"}'
test=# SELECT quote_nullable(ARRAY['foo', 'bar', 'b ''A'' "Z"']);
quote_nullable
--------------------------------
E'{foo,bar,"b ''A'' \"Z\""}'
Your example:
你的例子：
CREATE OR REPLACE FUNCTION test_function()
RETURNS text AS
$func$
DECLARE
testarray text[] := '{"value 1","value 2","value 3"}';
BEGIN
RETURN 'any text';
END
$func$ LANGUAGE plpgsql;
Other points
其他要点
- Don't quote the language name:
LANGUAGE plpgsql. Use lower case identifiers in Postgres. Per documentation:
All key words are case-insensitive. Identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.
- 不要引用语言名称：
LANGUAGE plpgsql. 在 Postgres 中使用小写标识符。根据文档：
所有关键字都不区分大小写。除非双引号，否则标识符会隐式转换为小写，就像在普通 SQL 命令中一样。
