PostgreSQL『ERROR: argument of WHERE must not return a set』の原因と対処

ERROR:  argument of WHERE must not return a set

□ WHERE unnest(array_col)                      -- 配列を展開するSRFをboolean位置に置いた
□ WHERE jsonb_array_elements(js->'tags')        -- JSON配列展開を直接WHEREに置いた
□ WHERE regexp_matches(txt, '...')              -- setof text[] を返す関数を置いた
□ WHERE generate_series(1,10)                   -- 行集合を返す関数を置いた
□ WHERE my_func(...)   -- RETURNS SETOF ... な自作関数を直接置いた
□ ANY/IN/EXISTSの誤用（配列と集合・配列と配列の混同）

-- 1) 配列を持つテーブル
CREATE TEMP TABLE items(id int, tags text[]);
INSERT INTO items VALUES (1, ARRAY['pg','db']), (2, ARRAY['ai']), (3, NULL);

-- NG: WHERE が集合（unnest）を受けてしまう
SELECT * FROM items
WHERE unnest(tags) = 'pg';
-- ERROR: argument of WHERE must not return a set

・配列に要素 'x' が含まれるか → 'x' = ANY(tags) か tags @> ARRAY['x']
・JSON配列に 'x' が含まれるか → EXISTS (SELECT 1 FROM jsonb_array_elements_text(...) v WHERE v='x')
                                     または jsb @> '["x"]'
・正規表現マッチ判定          → txt ~ 'pattern'
・SRFの結果が1件でも存在？    → EXISTS (SELECT 1 FROM my_set_func(...))
・SRFで結合して絞り込み        → FROM ... JOIN LATERAL my_set_func(...) s ON 条件
・数値集合（series）と突き合わせ → JOIN generate_series(...) g ON ...

-- NG
SELECT * FROM items WHERE unnest(tags) = 'pg';

-- OK1: ANY（配列メンバーシップ）
SELECT * FROM items WHERE 'pg' = ANY(tags);

-- OK2: 配列包含演算子（tags が ['pg'] を含む）
SELECT * FROM items WHERE tags @> ARRAY['pg'];

-- OK3: NULL安全にEXISTS + unnest（NULL tagsも扱いたいとき）
SELECT i.*
FROM items i
WHERE EXISTS (
  SELECT 1 FROM unnest(i.tags) AS t(tag) WHERE t.tag = 'pg'
);

CREATE TEMP TABLE posts(id int, tags jsonb);
INSERT INTO posts VALUES (1, '["pg","sql"]'::jsonb), (2, '["ai"]'), (3, 'null');

-- NG
SELECT * FROM posts WHERE jsonb_array_elements(tags) = '"pg"';

-- OK1: 要素存在チェック（@> は“包含”）
SELECT * FROM posts WHERE tags @> '["pg"]';

-- OK2: EXIFSTで展開（null/非配列は要注意）
SELECT p.*
FROM posts p
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements_text(p.tags) AS e(elem)
  WHERE e.elem = 'pg'
);

CREATE TEMP TABLE logs(id int, msg text);
INSERT INTO logs VALUES (1,'hello pg'), (2,'no match');

-- NG: regexp_matches は setof text[]（集合）
SELECT * FROM logs WHERE regexp_matches(msg, 'pg');

-- OK1: 単純判定は ~ 演算子（boolean）
SELECT * FROM logs WHERE msg ~ 'pg';

-- OK2: 構造が欲しい場合は EXISTS でSRFを包む
SELECT * FROM logs
WHERE EXISTS (SELECT 1 FROM regexp_matches(msg, 'p(g)'));

-- NG
SELECT * FROM items WHERE generate_series(1,3);

-- OK: seriesで結合して絞り込む
SELECT i.*, g.n
FROM items i
JOIN generate_series(1,3) AS g(n) ON g.n = i.id;

-- 自作 SRF の存在判定
-- NG: WHERE my_func(i.id)
-- OK:
SELECT i.*
FROM items i
WHERE EXISTS (SELECT 1 FROM my_func(i.id));

-- IN は「副問い合わせ（集合）やリテラルリスト」に対する集合判定
SELECT * FROM t WHERE col IN (SELECT x FROM u);

-- ANY は「配列」に対する判定（= ANY(array_col)）
SELECT * FROM t WHERE col = ANY(array_col);

-- EXISTS は「行が1つでもあるか」の存在判定（最も汎用）
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM some_srf(...));

-- NG（DELETEでunnestを直接WHEREに）
DELETE FROM items WHERE unnest(tags) = 'pg';

-- OK1: ANY
DELETE FROM items WHERE 'pg' = ANY(tags);

-- OK2: EXISTS + LATERAL（複雑条件）
DELETE FROM items i
WHERE EXISTS (
  SELECT 1 FROM LATERAL unnest(i.tags) AS t(tag)
  WHERE t.tag = 'pg'
);

-- proretset = true なら集合（SETOF）を返す
SELECT proname, proretset, pg_get_function_result(p.oid) AS result_type
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
  AND proname IN ('unnest','jsonb_array_elements','regexp_matches');  -- 例

-- 例：各行のtagsを展開し、'pg' を含む行を抽出
SELECT i.*
FROM items i
JOIN LATERAL unnest(i.tags) AS t(tag) ON TRUE
WHERE t.tag = 'pg';

× WHERE unnest(arr) = 'a'
○ WHERE 'a' = ANY(arr)
○ WHERE EXISTS (SELECT 1 FROM unnest(arr) u WHERE u='a')

× WHERE jsonb_array_elements(js) = '"a"'
○ WHERE js @> '["a"]'
○ WHERE EXISTS (SELECT 1 FROM jsonb_array_elements_text(js) e WHERE e='a')

× WHERE regexp_matches(txt, 're')
○ WHERE txt ~ 're'
○ WHERE EXISTS (SELECT 1 FROM regexp_matches(txt, 're'))

× WHERE generate_series(1,10)
○ JOIN generate_series(1,10) g(n) ON 条件

-- 配列
SELECT * FROM items WHERE unnest(tags) = 'pg';              -- NG
SELECT * FROM items WHERE 'pg' = ANY(tags);                 -- OK

-- JSONB
SELECT * FROM posts WHERE jsonb_array_elements(tags) = '"pg"';  -- NG
SELECT * FROM posts WHERE tags @> '["pg"]';                      -- OK

-- 正規表現
SELECT * FROM logs WHERE regexp_matches(msg, 'pg');         -- NG
SELECT * FROM logs WHERE msg ~ 'pg';                        -- OK

□ WHERE句は必ずboolean式になっているか（=, <, ~, @@, IS, AND/OR/NOT, EXISTS…）
□ SRF（unnest/jsonb_array_elements/regexp_matches/自作SETOF）を直接置いていないか
□ 集合判定なら IN / EXISTS、配列判定なら ANY/ALL・@> を使っているか
□ SRFはFROM（LATERAL）で使う設計に寄せているか
□ NULLを含む配列や非配列JSONに対する境界条件を考慮したか

-- 存在判定
WHERE EXISTS (SELECT 1 FROM <集合を返す式・関数> AS alias(...) WHERE 条件)

-- 配列に 'x' を含む
WHERE 'x' = ANY(array_col)        -- か  array_col @> ARRAY['x']

-- JSONB配列に 'x' を含む
WHERE jsonb_col @> '["x"]'
-- または
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements_text(jsonb_col) AS e(v) WHERE v='x'
)