将数组文字传递给 PostgreSQL 函数 [英] Pass array literal to PostgreSQL function

问题描述

我有一个包含 select 语句的 Postgres 函数.我需要使用包含字符串值数组的传入变量添加条件.

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.

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--

questions 表格:

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

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

How should the vcode literal be formatted in PHP and what should be the syntax of the condition?

使用 PostgreSQL 9.1.1，PHP 5.3.6，pg_query_params.

Using PostgreSQL 9.1.1, PHP 5.3.6, pg_query_params.

推荐答案

SQL NOT IN 适用于 sets.由于您要传递一个 数组，请使用 <>全部.

SQL NOT IN works with sets. Since you are passing an array, use <> ALL.

您必须小心不要在这样的表达式中包含任何 NULL 值，因为 NULL <>any 永远不会计算为 TRUE，因此永远不会在 WHERE 子句中限定.

You have to be careful not to involve any NULL values with such an expression, because NULL <> anything never evaluates to TRUE and therefore never qualifies in a WHERE clause.

您的函数可能如下所示:

Your function could look like this:

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$;

调用:

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

或(使用 数组的替代语法构造函数):

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

或者您可以使用 VARIADIC 参数:

Or you could use a VARIADIC parameter:

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

...并传递一个列表值:

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

详情:

• 返回匹配plpgsql函数中输入数组元素的行

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

Using a simple SQL function since there is nothing in your question that would require the procedural elements of PL/pgSQL.

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

The input parameter is an array of text: text[]

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

To return multiple rows from your query use RETURNS TABLE for the return type.

使用位置参数 $1 引用 in 参数，因为按名称引用仅在 SQL 函数的 9.2 版中引入(与 plpgsql 函数相反，现在某些版本已经存在这种方法).

Referring to the in parameter with the positional parameter $1 since 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).

表限定列名，否则会与 RETURNS 子句中定义的同名 OUT 参数冲突.

Table-qualify column names that would otherwise conflict with OUT parameters of the same name defined in the RETURNS clause.

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

Faster for long arrays (> ~ 80 elements, it depends):

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

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

This variant (as opposed to the above) ignores NULL values in the input array.

这篇关于将数组文字传递给 PostgreSQL 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！