PL/pgSQL 配列

PostgreSQLのPL/pgSQLの配列について。

配列の定義方法

PL/pgSQLで配列を定義するには、要素のデータ型の後ろに角括弧を付ける。

declare
	array1 int[];
begin
	〜
end

create or replace procedure example(array2 int[])
as $$
	〜
$$;

配列数を指定する必要は無い。（無制限）
（標準SQLでは配列数を指定しないといけないらしいが、PostgreSQLでは不要。指定しても無制限になるらしい）

テーブルの1レコードを表す変数の場合、データ型は「テーブル名%rowtype」と書く。[2020-11-12]
しかしその配列を定義しようとして「テーブル名%rowtype[]」と書くとエラーになる。
「テーブル名[]」で大丈夫なようだ。

declare
  h  hoge%rowtype;
  hh hoge[];
  i  int;
begin
  i := 0;
  for h in select * from hoge loop
    i := i + 1;
    hh[i] = h;
  end loop;

  raise info 'hh = %', hh;

  foreach h in array hh loop
    raise info 'h = %', h;
  end loop;
end

PostgreSQLドキュメントの行型のところを見ると、「%rowtype」は省略しても問題ないらしい。[2020-11-13]
（他RDBMSとの移植性のためには有った方が良いらしい）
したがって、1レコードを表す変数のデータ型もテーブル名だけで書ける。

declare
  h  hoge;
  hh hoge[];
begin
  〜
end

あと、record型は配列に出来ない模様。[2020-11-13]

declare
  h  record;
  hh record[];
begin
  〜
end

↓

ERROR: variable "recs" has pseudo-type record[]

配列の初期化方法

配列を初期化する場合はarray生成子を使う。（配列コンストラクターというらしい）

	array1 := array[123, 456, 789];

空の配列やNULLを入れたい場合は、明示的にキャストする必要がある。

	array1 := array[]::int[];	--空配列
	array1 := null::int[];   	--NULL

同じ値で初期化したい場合はarray_fillを使う。

	array1 := array_fill(123, array[3]);		→array[123, 123, 123]

第1引数で要素の値、第2引数で配列の長さ（要素数）を指定する。
（第2引数がarrayなのは、二次元以上の配列も指定できるようになっている為）

配列の添字

配列の変数に角括弧で添字を付けて、各要素にアクセスできる。

配列の添字は基本的に1から始まるが、0や負の数を指定することも出来る。
間を飛ばした添字を指定して代入することも可能。

要素を取得する際の添字には、範囲外の添字（下限より下や上限より上）や値が入っていない要素の添字も指定可能で、その場合はNULLが返る。

	array1[1] := 111;
	array1[3] := 333;

	raise info 'a[1] = %', array1[1];
	raise info 'a[2] = %', array1[2];
	raise info 'a[3] = %', array1[3];
	raise info 'a[4] = %', array1[4];

↓実行結果

INFO:  a[1] = 111
INFO:  a[2] = <NULL>
INFO:  a[3] = 333
INFO:  a[4] = <NULL>

配列の長さや添字の上限・下限の取得

配列の長さ（要素数）や添字の上限下限は以下のようにして取得できる。

	raise info 'length = %', array_length(array1, 1);	--長さ
	raise info 'lower  = %', array_lower(array1, 1); 	--下限
	raise info 'upper  = %', array_upper(array1, 1); 	--上限

第2引数は次元の番号で、一次元配列の場合は1。

下限が1の場合は、長さと上限は一致する。
（長さ = 上限 - 下限 + 1）

なお、配列が空配列やNULLの場合は、これらの関数はNULLを返すので注意。
（空配列に対するarray_lengthは0を返して欲しいところだけどなぁ）

配列の結合

配列同士の結合には連結演算子「||」もしくはarray_catを使う。

	array1 := array[1, 2] || array[3, 4];		→array[1, 2, 3, 4]
	array1 := array[1, 2] || null;       		→array[1, 2]

	array1 := array_cat(array[1, 2], array[3, 4]);

要素が追加された新しい配列を作るには、連結演算子「||」もしくはarray_prepend・array_appendを使う。

	array1 := 1 || array[2, 3];
	array1 := array[1, 2] || 3;

	array1 := array_prepend(1, array[2, 3]);
	array1 := array_append(array[1, 2], 3);

要素の検索

配列の中から特定の値を検索するにはarray_positionを使用する。（JavaのindexOf相当）
見つかった値の添字が返る。複数ある場合は先頭の添字。
値が見つからなかった場合はNULLが返る。

declare
	n int;
begin
	n = array_position(array[11, 22, 33], 22);

	raise info 'n = %', n;
end

↓

INFO:  n = 2

一致する値が複数あって、その全ての添字が欲しい場合は、array_positionsを使用する。

declare
	n int[];
begin
	n = array_positions(array[11, 22, 33, 22, 44], 22);

	raise info 'n = %', n;
end

↓

INFO:  n = {2,4}

一致する値が1個も無かった場合は、空配列が返る。

配列の巡回

配列の全ての要素を処理するにはfor文が利用できる。

-- 下限が1の場合
declare
	a1 int[];
begin
	a1 := array[11, 22, 33];

	for i in 1..array_length(a1, 1) loop
		raise info 'element = %', a1[i];
	end loop;
end

-- 下限が1でない場合
declare
	a1 int[];
	n int;
begin
	a1[-1] := 11;
	a1[3]  := 33;

	for i in array_lower(a1,1)..array_upper(a1, 1) loop
		n := a1[i];
		if n is not null then
			raise info 'element = %', n;
		end if;
	end loop;
end

また、foreach文を使う方法もある。

declare
	a1 int[];
	n int;
begin
	a1[-1] := 11;
	a1[3]  := 33;

	foreach n in array a1 loop
		if n is not null then
			raise info 'element = %', n;
		end if;
	end loop;
end

配列⇔文字列変換

配列を文字列に変換するにはarray_to_stringを使用する。[2020-11-11]

declare
	a1 int[];
	s text;
begin
	a1 := array[1, 2, 3];
	s  := array_to_string(a1, ', ');	--'1, 2, 3'
end

区切り文字で区切られた文字列を配列に変換するにはstring_to_arrayを使用する。

declare
	a1 text[];
	a2 int[];
	s text;
begin
	s  := '1, 2, 3';
	a1 := string_to_array(s, ',');	--array['1', ' 2', ' 3']
	a2 := string_to_array(s, ',');	--array[1, 2, 3]
end

string_to_arrayによって返される配列の型はtext[]だが、それぞれの値がintに変換できるのであれば、int[]に代入できる。
値が変換できない場合はエラーが発生する。

空文字列を渡した場合には空配列が返る。
（string_to_arrayはPostgreSQL9.1からそういう挙動になったらしい）

NULLを渡した場合はNULLが返る。

他に、正規表現で区切り文字を指定できるregexp_split_to_arrayという関数もある。

declare
	a1 text[];
	a2 int[];
	s text;
begin
	s  := '1 2  3';
	a1 := regexp_split_to_array(s, '\s+');	--array['1', '2', '3']
	a2 := regexp_split_to_array(s, '\s+');	--array[1, 2, 3]
end

空文字列を渡すと、空文字列が1つ入った配列が返る。
NULLを渡した場合はNULLが返る。

SELECT結果を配列に入れる方法

SELECTした結果を配列に入れるには、普通に考えるとSELECT結果をfor文でループして配列に追加していくと思う。

declare
	a1 int[];
	id int;
	i  int;
begin
	i := 0;
	for id in select h_id from hoge order by h_id loop
		i := i + 1;
		a1[i] := id;
	end loop;
end

declare
	a1  hoge[];
	rec hoge;
	i   int;
begin
	i := 0;
	for rec in select * from hoge order by h_id loop
		i := i + 1;
		a1[i] := rec;
	end loop;
end

array_agg集約関数を使う方法

SELECTするのが1カラムだけの場合はarray_agg集約関数が使える。

declare
	a1 int[];
begin
	select array_agg(h_id) into a1 from hoge order by h_id;

	raise info 'id list = %', a1;
end

あるいは

	a1 := array_agg(h_id) from hoge order by h_id;

無理矢理複数カラムにすることも出来るみたいだが、変数のデータ型がtextの配列になってしまう…。

declare
	a1 text[];
begin
	a1 := array_agg((h_id, h_name)) from hoge;

	raise info 'list = %', a1;
end

複数カラムを丸括弧で囲む（タプルみたいに見える）とrecord型になるようなのだが、PL/pgSQLはrecordの配列は定義できない模様。

受ける変数を複合型の配列にしておけば、自動的に変換はされるようだ。[2020-11-19]

declare
	a1 hoge[];
begin
	a1 := array_agg((h_id, h_name)) from hoge;
	a1 := array_agg(hoge.*) from hoge;	-- array_agg(*)だとエラーになるので注意

	raise info 'list = %', a1;
end

arrayを使う方法

（array_aggを使わずに）arrayで書けた。[/2020-11-19]

declare
	a1 int[];
begin
	a1 := array(select h_id from hoge order by h_id);

	raise info 'id list = %', a1;
end

declare
	a1 hoge[];
begin
	a1 := array(select row(hoge.*) from hoge order by h_id);	-- row(*)だとエラーになるので注意

	raise info 'list = %', a1;
end

unnest

なお、逆に配列を（テーブルのような）複数の行にするには、unnestを使用する。

select * from unnest(array[1, 2, 3]);

複合型の配列

複合型（行型）の配列は色々ハマりどころがあるので注意。[2020-11-17]

複合型とは、「create type 型名 as(カラム1 型1, カラム2 型2, …);」によって作られるユーザー定義のデータ型のこと。
行型とは、「テーブル名%rowtype」で表されるデータ型のこと。
PostgreSQLでは、create tableによってテーブルを作成すると、テーブル名と同名の複合型が暗黙に定義されるらしい。

複合型の配列の定義方法

配列はデータ型の後ろに角括弧を付けて定義するが、行型「テーブル名%rowtype」は何故かその方法が使えない。（エラーになる）
複合型の配列はOK。

declare
	hh hoge%rowtype[];	--NG
	hh hoge[];        	--OK

複合型の配列の要素のフィールドの更新

複合型の配列の要素のフィールドを直接更新することは出来ない。

declare
	hh hoge[];
begin
	hh[1].h_id := 1;	                  	--NG
	raise info 'h_id = %', hh[1].h_id;	--OK
end

↓

ERROR:  syntax error at or near "."
行 7:   hh[1].h_id := 1;
             ^

文法エラーになる。（同じ構文で参照は出来るくせに…）

次善の策として、複合型の変数を用意し、そこにコピーして更新する。

declare
	hh hoge[];
	h  hoge;
begin
	h      := hh[1];
	h.h_id := 1;
	hh[1]  := h;

	raise info 'h_id = %', hh[1].h_id;
end

ただし、複合型の変数と配列間の代入は全ての値のコピーとなる（参照渡しではない）ので、実行効率は悪そう。

プロシージャーや関数のinoutの引数にも配列の要素は指定できない。[2020-11-19]

create or replace procedure example5r(inout arg hoge)
language plpgsql
as $$
begin
	arg.h_id := 9;
end
$$;

create or replace procedure example5()
language plpgsql
as $$
declare
	hh hoge[];
	h  hoge;
begin
	call example5r(h);     -- OK
	call example5r(hh[1]); -- NG

	raise info 'h=%', h;
	raise info 'hh=%', hh;
end
$$;

↓

ERROR: procedure parameter "arg" is an output parameter but corresponding argument is not writable

配列の要素の有無の確認方法

PL/pgSQLの配列では、要素を入れる際の添字を飛び飛びにすることが出来る。
要素が入っていない添字を指定して参照しようとすると、NULLが返る。
したがって、要素があるかどうかを確認して使用する必要があるのだが。

declare
	hh hoge[];
	h  hoge;
begin
〜
	foreach h in array hh loop
		if h is not null then	--NG
		if not h is null then	--OK
			〜
		end if;
	end loop;
end;

複合型に対して「is not null」で判定すると、複合型の全てのフィールドがnullでない場合だけtrueになるらしい。
つまり、1フィールドでもnullが入っているとfalseになるので、要素自体の存在判定には使用できない。

したがって、「not 変数 is null」「not (変数 is null)」で判定しなければならない。
（この場合でも、要素の全フィールドがnullだったら正しく判定できないが、それはあまり起きない（ようにする）と思われる）

参考： todananoさんのpostgresqlのレコード型変数のNULLチェック

2次元配列

2次元配列は、データ型に角括弧を2つ付けることで表す。[2020-11-24]

declare
	a2 int[][];
begin
	a2 := array[[1, 2, 3], [4, 5, 6]];	--2次元配列の初期値
	raise info 'dim=%x%', array_length(a2, 1), array_length(a2, 2);

	raise info 'a2=%', a2;
	raise info 'a2[1]=%', a2[1];
	raise info 'a2[1][1]=%', a2[1][1];

	foreach n in array a2 loop
		raise info'n=%', n;
	end loop;
end

↓

INFO:  dim=2x3
INFO:  a2={{1,2,3},{4,5,6}}
INFO:  a2[1]=<NULL>
INFO:  a2[1][1]=1
INFO:  n=1
INFO:  n=2
INFO:  n=3
INFO:  n=4
INFO:  n=5
INFO:  n=6

a2[1]がNULLになってる…。2次元配列の場合、要素を1次元配列として取得するようなことは出来ないみたいだ。
当然、「foreach a in array a2[1] loop」のような書き方も出来ない。

2次元配列（多次元配列）では、（1次元配列と異なり）要素数の拡張が出来ない。

declare
	a2 int[][];
begin
	a2[1][1] := 1;
	a2[1][2] := 2;	--ERROR: array subscript out of range
end

事前に要素数を確定しておく必要がある。

declare
	a2 int[][];
begin
	a2 := array_fill(null::int, array[2, 3]);	--要素数は2×3
	a2[1][1] := 11;
	a2[1][2] := 12;
	a2[1][3] := 13;
	a2[2][1] := 21;
	a2[2][2] := 22;
	a2[2][3] := 23;

	raise info 'a2=%', a2;
end

↓

INFO:  a2={{11,12,13},{21,22,23}}

2次元配列で要素数の拡張をしたいなら、「要素の1次元配列のtype」を用意しておき、それの1次元配列にすれば実現できる。

create type int_list as
(
	int_array int[]
);

declare
	a2 int_list[];
	a  int_list;
begin
--	a2[1].int_array[1] := 11;	--ERROR: syntax error at or near "."

	a.int_array[1] := 11;
	a.int_array[2] := 12;
	a.int_array[3] := 13;
	a2[1] := a;

	a.int_array[1] := 21;
	a.int_array[2] := 22;
	a.int_array[3] := 23;
	a2[2] := a;

	raise info 'a2=%', a2;
	raise info 'a2[1]=%', a2[1];
	raise info 'a2[1][1]=%', a2[1].int_array[1];
end