PostgreSQLでストアドプロシージャを使用する
PostgreSQLインサイド

PostgreSQL 11では、ストアドプロシージャが機能追加され、ストアドプロシージャがサポートされているOracleなど他社データベースからの移行性が向上しました。ここでは、PostgreSQL 11のストアドプロシージャについて解説します。

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

ストアドプロシージャは、あらかじめデータベースを操作する一連の処理をデータベースサーバーに格納したものです。さまざまなクライアントやアプリケーションからストアドプロシージャを呼び出すだけで一連の処理が実行できます。異なるアプリケーションからデータベースに対して、汎用的な処理を実行するような場合（1日の売り上げを集計するなど）に、データベースサーバー上のストアドプロシージャを呼び出すだけで処理することができます。
ストアドプロシージャは、データベースシステムごとに記述できる言語が異なります。PostgreSQLでは、下記の言語で作成可能です。

• 手続き言語（PL/pgSQL, PL/Python, PL/Tcl, PL/Perl）
• SQL
• C言語

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

ストアドプロシージャを利用するメリットを説明します。

処理の高速化

ストアドプロシージャでは、1回の要求で複数のSQL文が実行できます。
クライアントからアプリケーションを実行する場合、通常は、クライアントからサーバーに1つのSQLを送信し結果を受信、また次のSQLを送信というように、クライアントとサーバー間でSQLの送信と受信が繰り返されるため、処理に時間がかかります。このような場合に、ストアドプロシージャを利用すると、クライアントから1回呼び出すだけで複数のSQL処理が可能となり、クライアントには、最終的な実行結果だけが送付されるため、ネットワーク負荷がかからず処理時間も短縮できます。

• ストアドプロシージャ未使用

  

• ストアドプロシージャ使用

  

ただし、複数のクライアントから一斉にストアドプロシージャが呼び出される場合や比較的大規模なストアドプロシージャを利用する場合など、データベースサーバーの負荷が大きくなってしまうときがあります。性能面を考慮し、どの処理をストアドプロシージャにすると効果的なのか見極めが必要です。

汎用的な処理の共通化

各アプリケーションでは、データベースに対し同様の処理を実行している場合があります。このような同様の処理を分離し、データベースにストアドプロシージャとして格納することで、処理を共通化することができます。処理が共通化されると、アプリケーション開発で新たな作りこみをする必要がないので、開発の効率化が期待できます。また、処理に変更が発生した場合、共通化しているストアドプロシージャの修正のみで良いため、保守性も向上します。
「現時点での在庫状況を集計する」や「1日の売り上げを集計する」などは、汎用的に実行される処理なのでストアドプロシージャが向いています。

• ストアドプロシージャ未使用

  

• ストアドプロシージャ使用

  

1.2 ストアドプロシージャの特徴

下表に、ストアドプロシージャの使い方に関する主な特徴を示します。

2. ストアドプロシージャの定義例と実行例

ストアドプロシージャの定義例と実行例を説明します。以降の定義例では、手続き言語として、PL/pgSQLを使用しています。

定義例

ストアドプロシージャは、CREATE PROCEDURE文で定義します。下記の例では、パラメーターをINOUTに指定することで、値を返しています。

CREATE PROCEDURE proc1(INOUT p1 TEXT)  -- INOUTパラメーターを指定します
AS $$
BEGIN
    p1 := '!! ' ||  p1 ||' !!';
    RAISE NOTICE 'Procedure Parameter: %', p1 ;
END;
$$
LANGUAGE plpgsql ;

実行例

ストアドプロシージャは、CALL文で実行します。

mydb=# CALL proc1 ('Stored Procedure supported in PostgreSQL 11');
NOTICE:  Procedure Parameter: !! Stored Procedure supported in PostgreSQL 11 !!
                       p1
--------------------------------------------------------
!! Stored Procedure supported in PostgreSQL 11 !!
(1 行)

2.1 トランザクション制御を実装したストアドプロシージャの例

ストアドプロシージャでは、COMMIT / ROLLBACKによるトランザクション制御を実装できます。
COMMIT / ROLLBACKを記述した以下のようなストアドプロシージャを作成し、COMMIT / ROLLBACKの動作を確認してみます。以下の例では、整数FORループ内で、iは1から100の値を取り、10の倍数の場合には処理をCOMMIT、10の倍数でない場合には処理をROLLBACKします。

CREATE PROCEDURE proc2()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO samp(number) VALUES(i);
        IF i % 10 = 0 THEN  -- i/10の余りがゼロか否かの判定
            COMMIT;         -- 10の倍数の場合：COMMITする
        ELSE
            ROLLBACK;       -- 10の倍数でない場合：ROLLBACKする
        END IF;
    END LOOP;
END
$$;

実際にストアドプロシージャを実行し、その実行結果を見てみましょう。10の倍数だけがsampテーブルに挿入されており、COMMIT / ROLLBACKが機能していることがわかります。

mydb=# CREATE TABLE samp(number integer);
CREATE TABLE


mydb=# CALL proc2();
CALL
mydb=# SELECT * FROM samp;
number
--------
     10
     20
     30
     40
     50
     60
     70
     80
     90
    100
(10 行)

また、ストアドプロシージャ内から別のストアドプロシージャの呼び出し（入れ子）も可能です。

CREATE PROCEDURE proc3() LANGUAGE plpgsql AS $$
BEGIN
 INSERT INTO samp VALUES(1);   -- （a）
 CALL proc4();
 INSERT INTO samp VALUES(4);   -- （d）
 COMMIT;
END;
$$;

CREATE PROCEDURE proc4() LANGUAGE plpgsql AS $$
BEGIN
 INSERT INTO samp VALUES(2);   -- （b）
 ROLLBACK;
 INSERT INTO samp VALUES(3);   -- （c）
END;
$$;

ストアドプロシージャを実行し、実行結果を見てみます。上記の例では、proc3からproc4を呼び出していますが、proc4はサブトランザクションとならず、呼び出し元の処理（a）がproc4でROLLBACKされ、無効になっていることがわかります。

mydb=# DELETE FROM samp;
DELETE 10
mydb=# CALL proc3();
CALL
mydb=# SELECT * FROM samp;    -- proc4のROLLBACKにより、（a）および（b）の行が挿入されていません。
number
-------
    3
    4
(2 行)

以下は、上記を実行したときのsampテーブルの状態遷移です。

3. OracleからPostgreSQLへのストアドプロシージャの移行

OracleのストアドプロシージャをPostgreSQLに移行する場合、PostgreSQL 10まではファンクションに移行する必要がありましたが、PostgreSQL 11ではストアドプロシージャが利用可能となり、移行がしやすくなりました。
OracleのストアドプロシージャをPostgreSQLに移行する例を示します。下記の例では、赤字部分の修正のみで移行できます。

• （備考）
  上記の例では、samp2テーブルのデータ件数を事前に判定し、すでに10件以上登録済みの場合は、メッセージを出力します。10件に満たない場合は、パラメーターで渡されたデータをsamp2テーブルに挿入します。

参考

Fujitsu Enterprise Postgresには、他社データベースからの移行支援サービスがあります。移行支援サービスでは、ストアドプロシージャをはじめとするアプリケーションの移行アセスメントから資産移行、導入までのトータルなサポートを提供しています。

• 製品情報はこちら

3.1 トランザクション制御に関する制約

PostgreSQLとOracleでは、トランザクション制御の仕様が異なります。PostgreSQLでは、トランザクション制御に関して以下の制約がありますので、Oracleからの移行時に注意が必要です。

ファンクション内や明示的なトランザクション内からストアドプロシージャを呼び出した場合、そのストアドプロシージャ内でCOMMIT / ROLLBACKが実行されるとエラーになります。

BEGINとEXCEPTIONの間に、COMMIT / ROLLBACKは記述できません。この場合、EXCEPTIONが必要な処理をBEGINからENDでサブブロック化し、COMMIT / ROLLBACKはサブブロックの外に記述します。

ここでは、PostgreSQLのストアドプロシージャを解説しました。アプリケーション開発の効率化と処理の高速化を実現するため、ストアドプロシージャの使用を検討してください。

2019年5月31日公開

こちらもおすすめ

• パーティショニングの概要
• パーティショニングにおける性能向上のしくみ
• 技術者に聞く：パーティショニングにおける性能改善への取り組み

本コンテンツに関するお問い合わせ