【サンプル満載】postgresql ストアド プロシージャ自由自在(stored procedure)

SQL入門
この記事は約13分で読めます。

仕事で PostgreSQL のストアドプロシージャを使うことになったので、色々とググったところ、今一分かりやすい記事が見つかりませんでした。

そこで、これから PostgreSQL でストアドプロシージャ(stored procedure)やストアドファンクション(stored function)を始めたい人向けに作り方を解説したいと思います。

サンプルも豊富に掲載していますので、是非お役立てください。

ストアドプロシージャとは

ストアドプロシージャとは、データベースに登録して使うプログラムの事です。

通常は、アプリケーションからデータベースへ命令する場合、命令の回数文だけSQLを実行しなければなりません。

例えば、4つのSQLを実行する場合、データベースに対して4回の通信が発生します。

実行したいSQLをデータベースに登録(stored proceder)しておき、これを呼び出すことが出来れば、アプリケーションとデータベースの通信は1回で済みます。

ストアドプロシージャのメリット

  • 大量の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,・・・)

ストアドの削除方法

ストアドの削除方法については drop を使います。

ただ、PostgreSQLのストアドは異なる引数であれば同じ名前で登録できますので、引数の型まで指定する必要があります。

ストアドファンクションの場合

drop function ファンクション名(引数1 型1,引数2,型2,・・・)

ストアドプロシージャの場合

drop procedure プロシージャ名名(引数1 型1,引数2,型2,・・・)

テストテーブルについて

この記事で紹介するストアドは、以下のテストテーブルに対して動作確認を行いました。

記載内容を再現したい場合は、あらかじめ下記のSQLを実行して下さい。

ストアドファンクションの書き方とサンプル

ここで説明する内容はストアドファンクションについての事ですが、戻り値を返す部分以外はストアドプロシージャと共通です。

結果を1つだけ返すストアドファンクション

結果を1つだけ返す場合、次のような記述になります。

このサンプルでは WHERE 地域 like ‘%’ || p_area || ‘%’ としていますが、これはSQLの中で文字列を結合する方法を示したかったので、あえて like を使いました。

select * from summary(‘大阪’) を実行すると以下の結果が返ります。

summary
250

複数の結果を返すストアドファンクション

多くのプログラミング言語では、1つのファンクションから複数の値を返すことができますが、PostgreSQLのストアドの場合は、少し工夫が必要です。

例えば、

CREATE OR REPLACE Function summary(p_area varchar) RETURNS integer ,varchar

という具合に、RETURNS の後に複数の変数を記述したいところですが、残念ながらこれだとエラーになります。

ただ、複数の結果を返せない訳ではなく、次に説明する SETOF を使うか、後述する TABLE を使う必要があります。

複数行を返すストアドファンクション(SETOF)

SETOFと RETURNQUERY VALUES を使うことで、複数の値を行として取り出す事が出来ます。

select * from summary(‘大阪’) を実行すると次の結果が得られます。

summary
大阪
250
大阪

結果をテーブル形式で返す ストアドファンクション(TABLE)

この記述をすることで、あたかもテーブルからデータを抽出するような結果が得られます。

先ほど、複数の値を返す方法のところで少し触れましたが、このTABLEを使って1行だけ結果を返すようにすれば、先ほどのことは実現可能です。

しかし本当にメリットがあるのは、複雑な入れ子状態でレスポンスが遅くなったVIEWに対して、この方法でストアド化することです。

VIEWに対して検索条件を指定した場合、一旦VIEWに記述されているSQLが実行されてしまうため、VIEWが複雑だったり大量データだった場合、レスポンスが非常に遅くなります。

しかし、ストアドの中で適切に条件を絞り込んであげることで、劇的にレスポンスが改善できます。

ポイントは次の通りです。

  • RETURNS TABLE の後に、返したいカラム名を列挙する。
  • DECLARE に 実行したいSQLを記述する。
  • BEGIN に FOR 文によるループを記述し、その中に RETURN NEXT を記述しておく。
  • RETURNS TABLE TABLE に記述した変数にrec の値を代入する。

このストアドファンクションに対して select * from summary(‘大阪’) を実行すると、次の結果が返ります。 

v_datev_areav_hinmeiv_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 を使う時よりもデータの取得部分が簡素化できるというメリットがあります。

引数に配列を渡す場合のストアドファンクション

引数に配列を渡す場合、引数の型の後ろに[] を付けます

  ストアド名(変数名 変数の型[],・・・・)   

下記のサンプルでは、p_area に varchar[] を指定しています。

受取った配列をどのように処理するかについては、いくつか方法があるので、それぞれについて解説したいと思います。

FOR IN を使う方法

配列の個数は array_length関数で取得できるので、次のように記述出来ます。

FOR n IN 1..array_length(配列の変数名, 配列の次元数 ) LOOP

今回は1次元配列を使っていますので array_length(p_area,1) になっています。

ちなみに、配列の添え字は1から始まります。

select * from summary(array[‘大阪’,’東京’]) を実行すると、次の結果が得られます。

v_datev_areav_hinmeiv_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で書き直したものです。

anyを使う方法

配列を使ってループを回すのではなく、配列を in で使いたい場合があります。

例えば

select * from hoge where name in 配列名

という具合に記述したい場合、in の直後に配列名を記述してもうまくいきません。

こんな場合は、any を使って次の様に記述します。

select * from hoge where name = any(配列名)

これを使って書き直すと次のようになります。

ストアドプロシージャの書き方とサンプル

戻り値の無いストアドファンクション=ストアドプロシージャになりますので、細かい記述方法はストアドプロシージャを見ていただくとして、ここではストアドプロシージャに特化した内容について解説します。

Aテーブルの内容を使ってBテーブルを更新する

ストアドファンクションは戻り値が無いため、INSERT、UPDATE、DELETE を使ったデータの更新処理が中心になります。

例えば、売上明細テーブルからデータを抽出し、集計表テーブルを更新する場合は、次の様に記述します。

下記のサンプルでは、売上明細テーブルからFETCHを使っていますが、もちろんFORを使っても構いません。

ここでは INSERT を1行分だけ実行していますが、同時にUPやDELETEを実行することも可能です。

ちなみに、サンプルではトランザクション処理について考慮していませんので、適宜必要に応じてトランザクション処理を行ってください。

call area_summary(‘大阪’) でストアドプロシージャを実行すると、集計表には次の値が挿入されます。

地域数量
大阪780

上記ストアドプロシージャを2回実行すると、重複キーエラーになりますのでご注意下さい。

動的SQLを使ったストアドのサンプル

動的SQLとは

ストアドにSQLを記述する場合、そのSQLは外部から変更できませんから固定になります。

しかし、例えばAテーブルの内容を見てSQLを変えたい場合、IF文を多用する必要があり、処理が煩雑になります。

あるいは、Aテーブルの特定カラムに登録されている文字列をSQLとして実行したい場合は、今までのストアドの書き方だと実現出来ません。

この様な場合、SQLの文法で書いた文字列を、SQLとして解釈して実行してくれたら便利ですよね。

これが動的SQLです。

文字列をSQLとして解釈して実行するには EXECUTE を使います

Aテーブルの内容を使って動的SQLでBテーブルを更新する

では、先ほどのストアドプロシージャのINSERT部分を、動的SQLに書き換えると次の様になります。

EXECUTEに続けてINSERT文を文字列として渡していますので、売上明細テーブルから取得した値はSQLの文字列連結方法に従っています。

call area_summary(‘京都’) を実行すると、集計表テーブルは次の結果になります。

地域数量
京都500

動的SQLでAテーブルの内容を取得し、動的SQLでBテーブルを更新する

今度は、取得元と更新先の両方について動的SQLを使ったサンプルです。

BEGINの中にSQLを書くと煩雑になるので、DECLARE に記述しています。

また、今回は format 関数を使って、SQLの文字列に引数を埋め込むようにしました。

まとめ

今回はPostgreSQL のストアドファンクション、ストアドプロシージャについて、

  • 前提となる基礎知識
  • ストアドファンクションの書き方とサンプル
  • ストアドプロシージャの書き方とサンプル
  • 動的SQLの使い方

について解説致しました。

色々な書き方がありますが、まずはこの記事の内容をテンプレートとして使っていただければ、一通りの事は出来ると思います。

今までいろいろなサイトを見て理解しようとして、今一理解できなかった方に対して、この記事が手助けになれば幸いです。

タイトルとURLをコピーしました