php - 将数组文字传递给 PostgreSQL 函数

Question

我有一个包含 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--

问题表:

id ,title, code
1, "title1", "qcode1"
2, "title2", "qcode2"
3, "title3", "qcode3"
4, "title4", "qcode4"

vcode 文字应该如何在 PHP 中格式化，条件的语法应该是什么？

使用 PostgreSQL 9.1.1、PHP 5.3.6、pg_query_params。

Answer 1

SQL NOT IN使用集合。因为你传递的是一个数组，所以使用<> ALL .

你必须小心不要涉及任何NULL具有这种表达式的值，因为 NULL <> anything从不评估为 TRUE因此永远不符合 WHERE 的条件条款。

您的函数可能如下所示:

CREATE OR REPLACE FUNCTION get_questions(vcode text[])
  RETURNS TABLE(id int, title text, code text)
  LANGUAGE sql AS
$func$
SELECT q.id, q.title, q.code
FROM   questions q
WHERE  q.code <> ALL ($1);
$func$;

调用array literal :

SELECT * FROM get_questions('{qcode2, qcode2}');

或使用 array constructor ):

SELECT * FROM get_questions(ARRAY['qcode2', 'qcode2']);

或者您可以使用 VARIADIC参数:

CREATE OR REPLACE FUNCTION get_questions(VARIADIC vcode text[]) ...

... 并传递值的列表:

SELECT * FROM get_questions('qcode2', 'qcode2');

详细信息:

• Return rows matching elements of input array in plpgsql function

要点:

使用简单的 SQL 函数，因为您的问题中没有任何内容需要 PL/pgSQL 的过程元素。

输入参数是一个文本数组:text[]

要从查询中返回多行，请使用 RETURNS TABLE 返回类型。

用位置参数引用in参数$1因为按名称引用仅在 9.2 版中针对 SQL 函数引入(与现在某些版本中存在的 plpgsql 函数相反)。

否则会与 OUT 冲突的表限定列名称RETURNS 中定义的同名参数条款。

LEFT JOIN unnest($1)/IS NULL

长数组更快(> ~ 80 个元素，视情况而定):

SELECT q.id, q.title, q.code
FROM   questions q
LEFT   JOIN unnest($1) c(code) USING (code)
WHERE  c.code IS NULL;

此变体(与上述变体相反)忽略输入数组中的 NULL 值。