仕事で PostgreSQL のストアドプロシージャを使うことになったので、色々とググったところ、今一分かりやすい記事が見つかりませんでした。
そこで、これから PostgreSQL でストアドプロシージャ(stored procedure)やストアドファンクション(stored function)を始めたい人向けに作り方を解説したいと思います。
サンプルも豊富に掲載していますので、是非お役立てください。
Contents
ストアドプロシージャとは
ストアドプロシージャとは、データベースに登録して使うプログラムの事です。
通常は、アプリケーションからデータベースへ命令する場合、命令の回数文だけSQLを実行しなければなりません。
例えば、４つのSQLを実行する場合、データベースに対して４回の通信が発生します。
実行したいSQLをデータベースに登録（stored proceder）しておき、これを呼び出すことが出来れば、アプリケーションとデータベースの通信は１回で済みます。
ストアドプロシージャのメリット
- 大量のSQLを実行するケースにおいて、データベースとの通信量、データベースエンジンのSQL解析時間が大幅に削減されるため、処理時間が短縮できる。
- 副問い合わせを何段にも重ねるVIEWを作る場合、プログラムの中に検索条件を記述することで対象データを絞り込めるため、処理時間が短縮できる。
ストアドプロシージャのデメリット
- データベース毎に用意された専用のプログラミング言語を覚える必要がある
- プログラムで出力項目や検索項目が固定化されるため、VIEWのように臨機応変に出力項目や検索項目を追加することが出来ない。
- プログラムのデバッグがしづらい
ストアドプロシージャの種類
ストアドプロシージャには、「結果を返す」ものと、「結果を返さない」ものが存在します。
「結果を返すもの」はストアドプロファンクション（stored function）と呼んでおり、「結果を返さない」ものはストアドプロシージャ（stored procedure）と呼んで区別します。
しかし、実際の現場では両者をまとめてストアドプロシージャと呼ぶ事も多く、ストアドとか、ストプロという省略後を用いる場合もあります。
この記事では、ストアドと記述する部分がありますが、その場合はストアドプロシージャ、ストアドファンクションの両方について言及していると思ってください。
PostgreSQLで知っておくべき基礎知識
PostgreSQLでストアドを作成する場合、PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language)という専用の言語を使います。
詳細は公式サイトに記述されていますが、非常に分かり難いので、この記事では「これだけ知っていれば困らない」内容について解説したいと思います。
また、書き方については色々とパターンがあり、他のサイトを見比べてみると微妙に記述の仕方が異なっています。
これは、特定のステートメントを省略したり、短縮することがPL/pgSQLの言語仕様として許されているためです。
ユーザーの記述に幅を持たせることでプログラミングを楽にしたり、他のデータベースのストアドへ移植しやすくするためだと思いますが、逆に複数の記述が許されることで初心者は逆に混乱するかもしれません。
このサイトで紹介する記述方法が全てではありませんが、少なくともこのサイトで紹介する内容を覚えておけば、おおよその事は実現できるようになっています。
ストアドのテンプレート
ストアドプロシージャで「結果を返すもの」をストアドファンクションと呼ぶ事は述べましたが、その書き方は次のようなルールになっています。
一方、「値を返さない」ストアドプロシージャについては、次のように記述します。
ストアドプロシージャとストアドファンクションの違いは、戻り値を返す部分が有るか無いかの違いだけです。
基本的な文法
以下は、プログラミングする上で覚えておきたいポイントです。
- 行末はC＃やJavaと同じくセミコロンを使う。（例 SELECT * from HOGE;）
- 代入は コロンとイコールを続けて書く（例 data := 12345）
- DECLARE～BEGINの間に、プログラム中で使う変数の名前と型を定義する
- BEGIN～ENDまでの間にプログラムを記述する。
- 引数で指定した変数もプログラム内で自由に使うことが出来る。
ストアド内で使える変数は、引数と自分で定義した変数以外に、後述する「カーソル」内で使う変数があり、テーブルのカラム名も含めてプログラム内で利用することになります。
PostgreSQLにストアドを登録する際、例えばカラム名と同じ名前を引数で定義していたり、DECLAREで宣言したりすると、PostgreSQLはどの変数の事を指しているのか判断できずエラーになってしまいます。
これを防ぐために、変数の先頭には、どこで定義されたかを示す接頭語（引数なら”p_”、自分で定義した場合は ”v_”など）を付ける方法をお勧めします。
ストアドの呼び出し方
呼び出し方はファンクションとプロシージャで変わってきます。
ストアドファンクションの場合、テーブルの代わりに使うことが出来ます。
select * from ストアドファンクション名（引数1,引数2,・・・）
一方、ストアドプロシージャの場合は call を使います。
call ストアドプロシージャ名 （引数1,引数2,・・・）
|
1 2 |
select * from func_hoge('2021/10/10','大阪') call pro_hoge('2021/10/10','大阪') |
ストアドの削除方法
ストアドの削除方法については drop を使います。
ただ、PostgreSQLのストアドは異なる引数であれば同じ名前で登録できますので、引数の型まで指定する必要があります。
ストアドファンクションの場合
drop function ファンクション名(引数1 型1,引数2,型2,・・・）
ストアドプロシージャの場合
drop procedure プロシージャ名名(引数1 型1,引数2,型2,・・・）
|
1 2 |
drop function func_hoge(yymmde date,area varchar) drop procedure pro_hoge(yymmde date,area varchar) |
テストテーブルについて
この記事で紹介するストアドは、以下のテストテーブルに対して動作確認を行いました。
記載内容を再現したい場合は、あらかじめ下記のSQLを実行して下さい。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
create table "public"."売上明細" ( "販売日" date not null , "地域" character varying(50) not null , "品名" character varying(50) not null , "数量" integer , primary key ("販売日", "地域", "品名") ); create table "public"."集計表" ( "地域" character varying(50) not null , "数量" integer , primary key ("地域") ); insert into 売上明細 values('2021/10/10','愛知','ういろう',230); insert into 売上明細 values('2021/10/10','沖縄','紅芋タルト',80); insert into 売上明細 values('2021/10/10','岩手','かもめの玉子',200); insert into 売上明細 values('2021/10/10','宮城','笹かまぼこ',300); insert into 売上明細 values('2021/10/10','京都','八つ橋',300); insert into 売上明細 values('2021/10/10','秋田','だまこ餅',50); insert into 売上明細 values('2021/10/10','青森','いか墨カスター',80); insert into 売上明細 values('2021/10/10','大阪','たこやき',250); insert into 売上明細 values('2021/10/10','長崎','長崎カステラ',100); insert into 売上明細 values('2021/10/10','東京','東京ばなな',200); insert into 売上明細 values('2021/10/10','奈良','柿の葉寿司',100); insert into 売上明細 values('2021/10/10','兵庫','ゴーフル',150); insert into 売上明細 values('2021/10/10','北海道','白い恋人',50); insert into 売上明細 values('2021/10/11','愛知','ういろう',50); insert into 売上明細 values('2021/10/11','沖縄','紅芋タルト',250); insert into 売上明細 values('2021/10/11','岩手','かもめの玉子',230); insert into 売上明細 values('2021/10/11','宮城','笹かまぼこ',200); insert into 売上明細 values('2021/10/11','京都','八つ橋',150); insert into 売上明細 values('2021/10/11','秋田','だまこ餅',300); insert into 売上明細 values('2021/10/11','青森','いか墨カスター',250); insert into 売上明細 values('2021/10/11','大阪','たこやき',300); insert into 売上明細 values('2021/10/11','長崎','長崎カステラ',150); insert into 売上明細 values('2021/10/11','東京','東京ばなな',250); insert into 売上明細 values('2021/10/11','奈良','柿の葉寿司',300); insert into 売上明細 values('2021/10/11','兵庫','ゴーフル',230); insert into 売上明細 values('2021/10/11','北海道','白い恋人',80); insert into 売上明細 values('2021/10/12','東京','東京ばなな',150); insert into 売上明細 values('2021/10/13','大阪','たこやき',230); insert into 売上明細 values('2021/10/14','京都','八つ橋',50); insert into 売上明細 values('2021/10/15','兵庫','ゴーフル',80); insert into 売上明細 values('2021/10/16','愛知','ういろう',50); insert into 売上明細 values('2021/10/17','北海道','白い恋人',100); insert into 売上明細 values('2021/10/18','青森','いか墨カスター',200); insert into 売上明細 values('2021/10/19','秋田','だまこ餅',50); insert into 売上明細 values('2021/10/20','長崎','長崎カステラ',250); insert into 売上明細 values('2021/10/21','岩手','かもめの玉子',300); insert into 売上明細 values('2021/10/22','宮城','笹かまぼこ',150); insert into 売上明細 values('2021/10/23','沖縄','紅芋タルト',50); insert into 売上明細 values('2021/10/24','奈良','柿の葉寿司',80); |
ストアドファンクションの書き方とサンプル
ここで説明する内容はストアドファンクションについての事ですが、戻り値を返す部分以外はストアドプロシージャと共通です。
結果を１つだけ返すストアドファンクション
結果を１つだけ返す場合、次のような記述になります。
このサンプルでは WHERE 地域 like ‘%’ || p_area || ‘%’ としていますが、これはSQLの中で文字列を結合する方法を示したかったので、あえて like を使いました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE Function summary(p_area varchar) RETURNS integer AS $$ DECLARE v_suryo integer; BEGIN SELECT 数量 INTO v_suryo FROM 売上明細 WHERE 地域 like '%' || p_area || '%'; RETURN v_suryo ; END; $$ LANGUAGE plpgsql; |
select * from summary(‘大阪’) を実行すると以下の結果が返ります。
| summary |
| 250 |
複数の結果を返すストアドファンクション
多くのプログラミング言語では、１つのファンクションから複数の値を返すことができますが、PostgreSQLのストアドの場合は、少し工夫が必要です。
例えば、
CREATE OR REPLACE Function summary(p_area varchar) RETURNS integer ,varchar
という具合に、RETURNS の後に複数の変数を記述したいところですが、残念ながらこれだとエラーになります。
ただ、複数の結果を返せない訳ではなく、次に説明する SETOF を使うか、後述する TABLE を使う必要があります。
複数行を返すストアドファンクション（SETOF）
SETOFと RETURNQUERY VALUES を使うことで、複数の値を行として取り出す事が出来ます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR REPLACE Function summary(p_area varchar) RETURNS SETOF varchar AS $$ DECLARE v_hinmei varchar; v_suryo varchar; v_area varchar; BEGIN SELECT 品名,数量::varchar,地域 INTO v_hinmei,v_suryo,v_area FROM 売上明細 WHERE 地域 like '%' || p_area || '%'; RETURN QUERY VALUES (v_area),(v_suryo),(v_area); END; $$ LANGUAGE plpgsql; |
select * from summary(‘大阪’) を実行すると次の結果が得られます。
| summary |
| 大阪 |
| 250 |
| 大阪 |
結果をテーブル形式で返す ストアドファンクション（TABLE）
この記述をすることで、あたかもテーブルからデータを抽出するような結果が得られます。
先ほど、複数の値を返す方法のところで少し触れましたが、このTABLEを使って１行だけ結果を返すようにすれば、先ほどのことは実現可能です。
しかし本当にメリットがあるのは、複雑な入れ子状態でレスポンスが遅くなったVIEWに対して、この方法でストアド化することです。
VIEWに対して検索条件を指定した場合、一旦VIEWに記述されているSQLが実行されてしまうため、VIEWが複雑だったり大量データだった場合、レスポンスが非常に遅くなります。
しかし、ストアドの中で適切に条件を絞り込んであげることで、劇的にレスポンスが改善できます。
ポイントは次の通りです。
- RETURNS TABLE の後に、返したいカラム名を列挙する。
- DECLARE に 実行したいSQLを記述する。
- BEGIN に FOR 文によるループを記述し、その中に RETURN NEXT を記述しておく。
- RETURNS TABLE TABLE に記述した変数にrec の値を代入する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE Function summary(p_area varchar) RETURNS TABLE( v_date date, v_area varchar, v_hinmei varchar, v_suryo integer ) AS $$ DECLARE cur cursor(c_area varchar) for SELECT t.販売日,t.地域,t.品名,t.数量 FROM 売上明細 t WHERE t.地域 like '%' || c_area || '%'; BEGIN FOR rec IN cur(p_area) LOOP v_date := rec.販売日; v_area := rec.地域; v_hinmei := rec.品名; v_suryo := rec.数量; RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; |
このストアドファンクションに対して select * from summary(‘大阪’) を実行すると、次の結果が返ります。
| v_date | v_area | v_hinmei | v_suryo |
| 2021/10/10 | 大阪 | たこやき | 250 |
| 2021/10/11 | 大阪 | たこやき | 300 |
| 2021/10/13 | 大阪 | たこやき | 230 |
ループを使ったデータの取得方法として、FOR の他に FETCH があります。
FETCH は無条件ループ（LOOP～END LOOP）の中で使い、データが無くなったことで発生するイベント（EXTI WHENNOT FOUNT）を受けてループを抜けるような記述になります。
上記のソースコードのBEGIN～ENDの部分を、下記に置き換えることで、FETCH を使ったデータ取得が可能になります。
どちらを使っても良いのですが、
FETCH cur INTO 変数1,変数2,変数3,・・・
という具合に FOR を使う時よりもデータの取得部分が簡素化できるというメリットがあります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE OR REPLACE Function summary(p_area varchar) RETURNS TABLE( v_date date, v_area varchar, v_hinmei varchar, v_suryo integer ) AS $$ DECLARE cur cursor(c_area varchar) for SELECT t.販売日,t.地域,t.品名,t.数量 FROM 売上明細 t WHERE t.地域 like '%' || c_area || '%'; BEGIN OPEN cur(p_area); LOOP FETCH cur INTO v_date,v_area,v_hinmei,v_suryo; EXIT WHEN NOT FOUND; RETURN NEXT; END LOOP; CLOSE cur; RETURN; END; $$ LANGUAGE plpgsql; |
引数に配列を渡す場合のストアドファンクション
引数に配列を渡す場合、引数の型の後ろに[] を付けます
ストアド名（変数名 変数の型[],・・・・）
下記のサンプルでは、p_area に varchar[] を指定しています。
受取った配列をどのように処理するかについては、いくつか方法があるので、それぞれについて解説したいと思います。
FOR IN を使う方法
配列の個数は array_length関数で取得できるので、次のように記述出来ます。
FOR n IN 1..array_length(配列の変数名, 配列の次元数 ) LOOP
今回は１次元配列を使っていますので array_length(p_area,1) になっています。
ちなみに、配列の添え字は１から始まります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE OR REPLACE Function summary(p_area varchar[]) RETURNS TABLE( v_date date, v_area varchar, v_hinmei varchar, v_suryo integer ) AS $$ DECLARE cur cursor(c_area varchar) for SELECT t.販売日,t.地域,t.品名,t.数量 FROM 売上明細 t WHERE t.地域 like '%' || c_area || '%'; BEGIN FOR n IN 1..array_length(p_area,1) LOOP FOR rec IN cur(p_area[n]) LOOP v_date := rec.販売日; v_area := rec.地域; v_hinmei := rec.品名; v_suryo := rec.数量; RETURN NEXT; END LOOP; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; |
select * from summary(array[‘大阪’,’東京’]) を実行すると、次の結果が得られます。
| v_date | v_area | v_hinmei | v_suryo |
| 2021/10/10 | 大阪 | たこやき | 250 |
| 2021/10/11 | 大阪 | たこやき | 300 |
| 2021/10/13 | 大阪 | たこやき | 230 |
| 2021/10/10 | 東京 | 東京ばなな | 200 |
| 2021/10/11 | 東京 | 東京ばなな | 250 |
| 2021/10/12 | 東京 | 東京ばなな | 150 |
FOREACHを使う方法
配列のループについては、FOREACHを使って次のように記述することも可能です。
FOREACH 値を受取る変数名 IN array 配列の変数名 LOOP
上記の方がスマートかもしれませんが、値を受取る変数名をDECLAREに定義する必要があるので、それが面倒な場合は、前述の FOR を使う方が良いかもしれません。
以下はFOREACHで書き直したものです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE OR REPLACE Function summary(p_area varchar[]) RETURNS TABLE( v_date date, v_area varchar, v_hinmei varchar, v_suryo integer ) AS $$ DECLARE cur cursor(c_area varchar) for SELECT t.販売日,t.地域,t.品名,t.数量 FROM 売上明細 t WHERE t.地域 like '%' || c_area || '%'; v_area varchar; BEGIN FOREACH v_area IN array p_area LOOP FOR rec IN cur(v_area) LOOP v_date := rec.販売日; v_area := rec.地域; v_hinmei := rec.品名; v_suryo := rec.数量; RETURN NEXT; END LOOP; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; |
anyを使う方法
配列を使ってループを回すのではなく、配列を in で使いたい場合があります。
例えば
select * from hoge where name in 配列名
という具合に記述したい場合、in の直後に配列名を記述してもうまくいきません。
こんな場合は、any を使って次の様に記述します。
select * from hoge where name = any(配列名)
これを使って書き直すと次のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE OR REPLACE Function summary(p_area varchar[]) RETURNS TABLE( v_date date, v_area varchar, v_hinmei varchar, v_suryo integer ) AS $$ DECLARE cur cursor(c_area varchar[]) for SELECT t.販売日,t.地域,t.品名,t.数量 FROM 売上明細 t WHERE t.地域 = any(c_area); BEGIN FOR rec IN cur(p_area) LOOP v_date := rec.販売日; v_area := rec.地域; v_hinmei := rec.品名; v_suryo := rec.数量; RETURN NEXT; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; |
ストアドプロシージャの書き方とサンプル
戻り値の無いストアドファンクション＝ストアドプロシージャになりますので、細かい記述方法はストアドプロシージャを見ていただくとして、ここではストアドプロシージャに特化した内容について解説します。
Aテーブルの内容を使ってBテーブルを更新する
ストアドファンクションは戻り値が無いため、INSERT、UPDATE、DELETE を使ったデータの更新処理が中心になります。
例えば、売上明細テーブルからデータを抽出し、集計表テーブルを更新する場合は、次の様に記述します。
下記のサンプルでは、売上明細テーブルからFETCHを使っていますが、もちろんFORを使っても構いません。
ここでは INSERT を１行分だけ実行していますが、同時にUPやDELETEを実行することも可能です。
ちなみに、サンプルではトランザクション処理について考慮していませんので、適宜必要に応じてトランザクション処理を行ってください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE OR REPLACE PROCEDURE area_summary(p_area varchar) AS $$ DECLARE v_area varchar; v_sum integer; cur cursor(c_area varchar) for SELECT t.地域,sum(t.数量) FROM 売上明細 t WHERE t.地域 = c_area GROUP BY t.地域; BEGIN OPEN cur(p_area); LOOP FETCH cur INTO v_area,v_sum; EXIT WHEN NOT FOUND; INSERT INTO 集計表(地域,数量) values(v_area,v_sum); END LOOP; CLOSE cur; END; $$ LANGUAGE plpgsql; |
call area_summary(‘大阪’) でストアドプロシージャを実行すると、集計表には次の値が挿入されます。
| 地域 | 数量 |
| 大阪 | 780 |
上記ストアドプロシージャを２回実行すると、重複キーエラーになりますのでご注意下さい。
動的SQLを使ったストアドのサンプル
動的SQLとは
ストアドにSQLを記述する場合、そのSQLは外部から変更できませんから固定になります。
しかし、例えばAテーブルの内容を見てSQLを変えたい場合、IF文を多用する必要があり、処理が煩雑になります。
あるいは、Aテーブルの特定カラムに登録されている文字列をSQLとして実行したい場合は、今までのストアドの書き方だと実現出来ません。
この様な場合、SQLの文法で書いた文字列を、SQLとして解釈して実行してくれたら便利ですよね。
これが動的SQLです。
文字列をSQLとして解釈して実行するには EXECUTE を使います
Aテーブルの内容を使って動的SQLでBテーブルを更新する
では、先ほどのストアドプロシージャのINSERT部分を、動的SQLに書き換えると次の様になります。
EXECUTEに続けてINSERT文を文字列として渡していますので、売上明細テーブルから取得した値はSQLの文字列連結方法に従っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE OR REPLACE PROCEDURE area_summary(p_area varchar) AS $$ DECLARE v_area varchar; v_sum integer; v_sql varchar; cur cursor(c_area varchar) for SELECT t.地域,sum(t.数量) FROM 売上明細 t WHERE t.地域 = c_area GROUP BY t.地域; BEGIN OPEN cur(p_area); LOOP FETCH cur INTO v_area,v_sum; EXIT WHEN NOT FOUND; EXECUTE 'INSERT INTO 集計表(地域,数量) values(''' || v_area || ''',' || v_sum || ')'; END LOOP; CLOSE cur; END; $$ LANGUAGE plpgsql; |
call area_summary(‘京都’) を実行すると、集計表テーブルは次の結果になります。
| 地域 | 数量 |
| 京都 | 500 |
動的SQLでAテーブルの内容を取得し、動的SQLでBテーブルを更新する
今度は、取得元と更新先の両方について動的SQLを使ったサンプルです。
BEGINの中にSQLを書くと煩雑になるので、DECLARE に記述しています。
また、今回は format 関数を使って、SQLの文字列に引数を埋め込むようにしました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE OR REPLACE PROCEDURE area_summary(p_area varchar) AS $$ declare v_area varchar; v_sum integer; v_sql1 varchar := 'select 地域,sum(数量) from 売上明細 where 地域 like ''%s'' group by 地域'; v_sql2 varchar := 'INSERT INTO 集計表(地域,数量) values(''%s'',%s)'; cur refcursor; BEGIN OPEN cur for EXECUTE format(v_sql1,p_area); LOOP FETCH cur INTO v_area,v_sum; EXIT WHEN NOT FOUND; EXECUTE format(v_sql2,v_area,v_sum); END LOOP; CLOSE cur; END; $$ LANGUAGE plpgsql; |
まとめ
今回はPostgreSQL のストアドファンクション、ストアドプロシージャについて、
- 前提となる基礎知識
- ストアドファンクションの書き方とサンプル
- ストアドプロシージャの書き方とサンプル
- 動的SQLの使い方
について解説致しました。
色々な書き方がありますが、まずはこの記事の内容をテンプレートとして使っていただければ、一通りの事は出来ると思います。
今までいろいろなサイトを見て理解しようとして、今一理解できなかった方に対して、この記事が手助けになれば幸いです。