【PostgreSQL】配列を任意の引数とした関数でselectの結果を返す

概要

以下の条件でsleectの結果を返す、PostgreSQLの関数を試しに作成してみたのでメモ書きを残しておきます。

• 配列が引数で設定された場合は、その値で絞り込みを行う。
• 配列が引数で設定されていない場合は、絞り込みを行わない。

対応方針

• Empty array as PostgreSQL array column default valueのstackoverflowの回答を参考に、引数の配列に空配列をデフォルトで設定します。
• 引数の配列が設定されているかの判定は、PostgreSQL15のドキュメント9.19. 配列関数と演算子を参考にarray_lengthを使用します。空配列の場合は結果がnullになります。
• 配列が引数で設定された場合、【PostgreSQL】配列の値で抽出する（Where、any）の記事で紹介されている通り、anyを使用して絞り込みを行います。

今回用意したテーブル

CREATE TABLE public.sample_table (
	id int8 NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT samle_table_pk PRIMARY KEY (id)
);

データは以下の通り入ってるものとします。

実装サンプル

create or replace
function sample_function(names varchar[] default '{}'::varchar[]) returns 
table(id int8, name varchar) as $$
begin
return QUERY
  select
	sample.id,
	sample.name
from
	public.sample_table sample
where
	case
		when array_length(names, 1) > 0 
    then sample.name = any(names)
		else true
	end;
end;
$$ language plpgsql;

select * from sample_function()で実行した場合は全件取得できて、select * from sample_function(array['テスト1', 'テスト2']::varchar[])で実行した場合は2件取得できます。