У меня есть функция Postgres, которая содержит оператор select. Мне нужно добавить условие, используя переданную переменную, содержащую массив строковых значений.
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 и каков должен быть синтаксис условия?
Использование PostgreSQL 9.1.1, PHP 5.3.6, pg_query_params .
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) 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}');
Или (альтернативный синтаксис с конструктором массива ):
SELECT * FROM get_questions(ARRAY['qcode2', 'qcode2']);
Или вы можете использовать параметр VARIADIC :
CREATE OR REPLACE FUNCTION get_questions(VARIADIC vcode text[]) ...
… и передать список значений:
SELECT * FROM get_questions('qcode2', 'qcode2');
Детали:
Использование простой функции 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 во входном массиве.