[PostgreSQL]SQL中で配列変数をforeachするサンプル

PostgreSQLのDBのメンテナンス作業で最近、配列に設定しておいた値をループして処理したいことが度々あり、その都度方法を検索するのが面倒になったのでメモとして記事にまとめる。

# 文字列配列をforeachするサンプル
```sql:foreach_stringarray.sql
\encoding 'UTF8'

do $$
declare
t	text;
arr	text[]	:= array[
	'データ1',
	'データ2',
	'データ3'
];
begin
    foreach t in array arr loop
    	raise info '%', t;
    end loop;
end$$;
```

```batch:コマンドラインからの実行
psql -f foreach_stringarray.sql
```

```:実行結果
データ1
データ2
データ3

```

# 文字列配列をindexでforするサンプル

```sql:for_intarray_index.sql
\encoding 'UTF8'

do $$
declare
i	int;
arr	text[]	:= array[
	'データ1',
	'データ2',
	'データ3'
];
begin
    for i in array_lower(arr,1)..array_upper(arr,1) loop
    	raise info '%, %', i, arr[i];
    end loop;
end$$;
```

```batch:コマンドラインからの実行
psql -f for_intarray_index.sql
```

```:実行結果
1, データ1
2, データ2
3, データ3
```

# 複合データの配列をforeachするサンプル

```sql:foreach_complex_stringarray.sql
\encoding 'UTF8'

-- 複合型を定義
create type mytype as (num int, str text);	-- いちいちcreate type してdropするのが面倒。もっとスマートな方法があったら知りたい。

do $$
declare
	num	int;
	str	text;
	arr	mytype[]	:= array[
		(10, 'DATA1'),
		(20, 'DATA2'),
		(30, 'DATA3')
	];
begin
    foreach num, str in array arr loop
    	raise info '% : %', num, str;
    end loop;
end$$;

drop type mytype;
```

```batch:コマンドラインからの実行
psql -f foreach_complex_stringarray.sql
```

```:実行結果
10 : DATA1
20 : DATA2
30 : DATA3
```

# 複数の配列を結合してforeachするサンプル

複数の配列を作って結合するには```||```を用いる。

```sql:foreach_concat_array.sql
\encoding 'UTF8'

-- 複合型を定義
create type mytype as (num int, str text);	-- いちいちcreate type してdropするのが面倒。もっとスマートな方法があったら知りたい。

do $$
declare
	num	int;
	str	text;
	arr1	mytype[]	:= array[
		(10, 'DATA1'),
		(20, 'DATA2'),
	];
	arr2	mytype[]	:= array[
		(30, 'DATA3'),
		(40, 'DATA4'),
	];
begin
    foreach num, str in array arr1||arr2 loop	-- arr1とarr2を結合してループ
    	raise info '% : %', num, str;
    end loop;
end$$;

drop type mytype;
```

```batch:コマンドラインからの実行
psql -f foreach_concat_array.sql
```

```:実行結果
10 : DATA1
20 : DATA2
30 : DATA3
40 : DATA4
```

# ネストした配列のフラット化

面白いことに、foreachはarrayの中のarrayをフラットにする。つまり、```[[1,2],[3,4]]```を```[1,2,3,4]```としてループする。

```sql:foreach_nested_stringarray.sql
\encoding 'UTF8'

-- 複合型を定義
create type mytype as (num int, str text);	-- いちいちcreate type してdropするのが面倒。もっとスマートな方法があったら知りたい。

do $$
declare
	num	int;
	str	text;
	arr1	mytype[]	:= array[
		(10, 'DATA1'),
		(20, 'DATA2'),
	];
	arr2	mytype[]	:= array[
		(30, 'DATA3'),
		(40, 'DATA4'),
	];
	arr	mytype[]	:= array[ arr1, arr2 ];
begin
    foreach num, str in array arr loop
    	raise info '% : %', num, str;	-- 10,20,30,40と順に処理される
    end loop;
end$$;

drop type mytype;
```

```batch:コマンドラインからの実行
psql -f foreach_nested_stringarray.sql
```

```:実行結果
10 : DATA1
20 : DATA2
30 : DATA3
40 : DATA4
```

尚、```\encoding```は、実行環境で定義されているなら省いて良いが、たまにclient_encodingがSJISになっていると面倒なので付けている。

# 引数付きのスクリプトの実行方法
スクリプトファイルを実行する時は以下のコマンドを使う。

```
psql -U <userid> -h <host> -f <scriptfilename>
```

スクリプトに引数を付けて実行することもできる。

下記のように、```:id```として参照する変数を```\set```で定義できる。末尾にセミコロンは不要である。

```sql
\set id 123
update mytabel1 set field1 = 'xxx' where id = :id;
```

上記のように```\set```を使わず、コマンドラインから値を与えることもできる。これはスクリプトの引数のように使える。

```
psql -U <userid> -h <host> -v id=123 -f <scriptfilename>
```

文字列値の場合はシングルコーテーションで囲う。

```
psql -U <userid> -h <host> -v id='123' -f <scriptfilename>
```

setの使い方についてはこちらの記事が詳しい。
https://qiita.com/noborus/items/f2954c4767ebcb5e52a4