動的SQL文で複数行の結果を取得したい

Question

前提・実現したいこと

下記のSQLのtest1テーブルとtest2テーブルを入力引数として複数行の取得結果を返すことを考えています。
入力引数をSQL文に埋め込む場合、動的SQLに変更することで対応できることが分かったのですが、書き方がわかりません。
よろしくお願いいたします。

該当のソースコード

CREATE OR REPLACE FUNCTION public.sabun(
)
RETURNS TABLE(id text, key1 text, key2 text)
LANGUAGE 'plpgsql'

COST 100
VOLATILE 
ROWS 1000

AS $BODY$
BEGIN
RETURN QUERY
SELECT * FROM
(
(SELECT b.id,
MAX(CASE b.key WHEN 'key1' THEN b.value ELSE '' END) AS "KEY1",
MAX(CASE b.key WHEN 'key2' THEN b.value ELSE '' END) AS "KEY2"
FROM
(SELECT test1.id, a.key, a.value FROM test1 JOIN json_each_text(test1.data::json) a ON true) b
GROUP BY b.id
ORDER BY b.id)
EXCEPT ALL
(SELECT c.id,c.key1,c.key2 FROM test2 c ORDER BY id)
) d
ORDER BY d.id;
END
$BODY$;

補足情報（FW/ツールのバージョンなど）

・plpgsql
・postgres 10

Comments

[Orlofsky]

コードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

Answer 1

入力引数をSQL文に埋め込む場合、動的SQLに変更することで対応できることが分かったのですが、書き方がわかりません。

動的SQLである必要はありません。SQL内からパラメータ名で参照できます。

42.3.1. 関数引数の宣言
以下引用

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

追記

質問のSQL自体は、以下の様に簡潔に記述できます。

select id, data->>'key1' as key1, data->>'key2' as key2 from test1
except all
select id, key1, key2 from test2
order by id

Comments

[g1081828]

ご回答ありがとうございます。
難しく考えてました。
また簡潔なSQL文も記載して頂きありがとうございます。勉強になりました。