php 将数组文字传递给 PostgreSQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17138792/
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
Pass array literal to PostgreSQL function
提问by DevR
I have a Postgres function which contains a select statement. I need to add a condition using a passed in variable containing an array of string values.
我有一个包含 select 语句的 Postgres 函数。我需要使用包含字符串值数组的传入变量添加条件。
CREATE OR REPLACE FUNCTION get_questions(vcode text)
RETURN return_value as $f$
DECLARE vresult return_value;
BEGIN
--snip--
SELECT id, title, code
FROM questions WHERE code NOT IN (vcode);
--snip--
questionstable:
questions桌子：
id ,title, code
1, "title1", "qcode1"
2, "title2", "qcode2"
3, "title3", "qcode3"
4, "title4", "qcode4"
How should the vcodeliteral be formatted in PHP and what should be the syntax of the condition?
vcode文字应该如何在 PHP 中格式化以及条件的语法应该是什么？
Using PostgreSQL 9.1.1, PHP 5.3.6, pg_query_params.
使用PostgreSQL 9.1.1，5.3.6 PHP， pg_query_params。
回答by Erwin Brandstetter
SQL NOT INworks with sets. Since you are passing in an array, use <> ALL.
SQLNOT IN与集合一起使用。由于您传入的是数组，因此请使用<> ALL.
You have to be extra careful not to involve any NULLvalues with such an expression, because NULL <> anythingnever evaluates to TRUEand therefore never qualifies in a WHEREclause.
您必须格外小心，不要在NULL这样的表达式中包含任何值，因为NULL <> anything从不评估为TRUE，因此从不限定在WHERE子句中。
Your function could look like this:
您的函数可能如下所示：
CREATE OR REPLACE FUNCTION get_questions(vcode text[])
RETURNS TABLE(id int, title text, code text) AS
$func$
SELECT q.id, q.title, q.code
FROM questions q
WHERE q.code <> ALL ();
$func$ LANGUAGE sql;
Call:
称呼：
SELECT * FROM get_questions('{qcode2, qcode2}');
Or (alternative syntax with an array constructor):
或（使用数组构造函数的替代语法）：
SELECT * FROM get_questions(ARRAY['qcode2', 'qcode2']);
Or you could use a VARIADICparameter:
或者你可以使用一个VARIADIC参数：
CREATE OR REPLACE FUNCTION get_questions(VARIADIC vcode text[]) ...
... and pass a listof values:
...并传递值列表：
SELECT * FROM get_questions('qcode2', 'qcode2');
Details:
细节：
Major points:
要点：
Using a simple SQL function since there is nothing in your question that would require the procedural elements of PL/pgSQL.
The input parameter is an array of text:
text[]To return multiple rows from your query use
RETURNS TABLEfor the return type.Referring to the in parameter with the positional parameter
$1since referring by name was only introduced with version 9.2 for SQL functions (as opposed to plpgsql functions where this has been around for some versions now).Table-qualify column names that would otherwise conflict with
OUTparameters of the same name defined in theRETURNSclause.
使用简单的 SQL 函数，因为您的问题中没有任何内容需要 PL/pgSQL 的过程元素。
输入参数是一个文本数组：
text[]要从查询中返回多行，请使用
RETURNS TABLE返回类型。使用位置参数
$1引用in 参数，因为按名称引用仅在 SQL 函数的 9.2 版中引入（与现在某些版本已经存在的 plpgsql 函数相反）。否则会与子句中
OUT定义的同名参数冲突的表限定列名RETURNS。
LEFT JOIN unnest($1)/ IS NULL
LEFT JOIN unnest($1)/ IS NULL
Faster for long arrays (> ~ 80 elements, it depends):
长数组更快（> ~ 80 个元素，视情况而定）：
SELECT q.id, q.title, q.code
FROM questions q
LEFT JOIN unnest() c(code) USING (code)
WHERE c.code IS NULL;
This variant (as opposed to the above) ignores NULL values in the input array.
此变体（与上述相反）忽略输入数组中的 NULL 值。
