🥛

【PostgreSQL】関数内でSQLの結果を複数行変数に格納する

2024/02/06に公開

概要

PostgreSQLの関数の機能には、SQL Serverのような複数行を保持できるtable型がありません。対応案としてはSELECT multiple rows and columns into a record variableの記事にある通り、一時テーブルを作成するかカーソルに格納するかが挙げられると思います。
とはいえ、配列を上手く使えばやりようがあるのではと思い、今回他の対応をメモ書きします。

対応方法

How to declare an array of rowtype in a PostgreSQL function?の記事にある通り、"テーブル名"[]のような形で該当テーブルのrecord型の配列が作成できます。ここにarray_aggを使ってselectの結果を入れれば実現できそうです。

実装サンプル

以下のようなテスト用のテーブルを用意して、これを一度変数に格納してから結果を返す関数を実装してみました。

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_record_func() returns TABLE(
  return_id int8, return_name varchar
)
LANGUAGE plpgsql
AS $BODY$
declare
  row_array "sample_table"[];
begin
  SELECT array_agg(tbl) INTO row_array FROM sample_table tbl;  
  return query select * from unnest(row_array);
end;
$BODY$

概要

対応方法

実装サンプル

Discussion