PostgreSQLのDBのメンテナンス作業で最近、配列に設定しておいた値をループして処理したいことが度々あり、その都度方法を検索するのが面倒になったのでメモとして記事にまとめる。
文字列配列をforeachするサンプル
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$$;
コマンドラインからの実行
psql -f foreach_stringarray.sql
実行結果
データ1
データ2
データ3
文字列配列をindexでforするサンプル
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$$;
コマンドラインからの実行
psql -f for_intarray_index.sql
実行結果
1, データ1
2, データ2
3, データ3
複合データの配列をforeachするサンプル
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;
コマンドラインからの実行
psql -f foreach_complex_stringarray.sql
実行結果
10 : DATA1
20 : DATA2
30 : DATA3
複数の配列を結合してforeachするサンプル
複数の配列を作って結合するには||を用いる。
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;
コマンドラインからの実行
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]としてループする。
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;
コマンドラインからの実行
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で定義できる。末尾にセミコロンは不要である。
\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