[解決済み] PostgreSQL の関数に配列リテラルを渡す

質問

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)
  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 パラメータを使用します。

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

... そして リスト という値があります。

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

詳細

• plpgsql関数で入力配列の要素にマッチする行を返します。

主なポイント

質問にはPL/pgSQLの手続き的な要素を必要とするものがないので、単純なSQL関数を使用しています。

入力パラメータは、テキストの配列です。 text[]

クエリから複数行を返すには、次のようにします。 RETURNS TABLE を返り値の型に指定します。

inパラメータを位置決めパラメータで参照する $1 名前による参照が導入されたのはバージョン9.2のSQL関数だけだからです（plpgsql関数ではいくつかのバージョンから導入されていますが）。

と衝突するようなカラム名をテーブル修飾する。 OUT で定義された同名のパラメータを使用します。 RETURNS 節を使用します。

LEFT JOIN unnest($1) / IS NULL

長い配列(> ~ 80 elements, 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 値を無視します。