通数组文本到PostgreSQL功能 [英] Pass array literal to PostgreSQL function
问题描述
我有一个Postgres的功能,其中包含一个select语句。我需要使用一个变量传递包含字符串值数组添加一个条件。
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--
问题
表:
id ,title, code
1, "title1", "qcode1"
2, "title2", "qcode2"
3, "title3", "qcode3"
4, "title4", "qcode4"
应如何 V $ C $ç
文字在PHP格式化,应该是什么状态的语法?
How should the vcode
literal be formatted in PHP and what should be the syntax of the condition?
使用PostgreSQL 9.1.1,5.3.6 PHP, pg_query_params
。
Using PostgreSQL 9.1.1, PHP 5.3.6, pg_query_params
.
推荐答案
SQL NOT IN
适用于的设置的。因为你是传递一个的阵列的,使用<> ALL
。
SQL NOT IN
works with sets. Since you are passing in an array, use <> ALL
.
您必须要格外小心,不要涉及与这样一个前pression任何 NULL
值,因为 NULL&LT;&GT;什么
从不计算 TRUE
,因此在 WHERE
子句从未资格。
You have to be extra 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) AS
$func$
SELECT q.id, q.title, q.code
FROM questions q
WHERE q.code <> ALL ($1);
$func$ LANGUAGE sql;
电话:
SELECT * FROM get_questions('{qcode2, qcode2}');
或(与<一个替代语法href=\"http://www.postgresql.org/docs/current/interactive/sql-ex$p$pssions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS\"相对=nofollow>数组构造):
SELECT * FROM get_questions(ARRAY['qcode2', 'qcode2']);
或者你可以使用可变参数
参数:
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的。
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 TABLE
for the return type.
谈到与位置参数参数 $ 1
,因为按名称指只与9.2版本推出了SQL函数(而不是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).
表,限定列名否则会与冲突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;
此变体(相对于上述)忽略输入阵列中的空值。
This variant (as opposed to the above) ignores NULL values in the input array.
这篇关于通数组文本到PostgreSQL功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!