PL/pgSQL プロシージャとファンクションについて

2018年 10月にリリースされたPostgreSQL11からストアド・プロシージャが実装されています。今回は、従来のストアド・ファンクションとストアド・プロシージャの違いを見ていこうかと思います。

ファンクションとプロシージャの違い

ストアド・ファンクションの主な特徴は、下記の通りです。

	ファンクション	プロシージャ
戻り値	必要(ないケースでも)	不要
実行方法	SQLから実行可能	SQLではなくCALLから実行
トランザクション	利用できない	利用可能(コミット、ロールバックができる)

ファンクションの例

Hello worldを標準出力に表示するファンクションの例です。戻り値もVOIDという形で明示的に定義しています

CREATE OR REPLACE FUNCTION hello_func()
RETURNS VOID AS $$
     BEGIN
         RAISE NOTICE 'HELLO WORLD';
     END;
$$ LANGUAGE plpgsql;

実行例は、下記の通りです。

$ SELECT hello_func();

プロシージャの例

Hello worldを標準出力に表示するプロシージャの例です。戻り値の定義がないことがわかります。

CREATE OR REPLACE PROCEDURE hello_proc()
AS $$
BEGIN
    RAISE NOTICE 'HELLO WORLD';
END;
$$ LANGUAGE plpgsql;

実行例は、下記の通りです。

$ CALL hello_proc();

トランザクションの注意点

PostgreSQL11を利用している場合、CALLを利用すれば、Function内からも呼び出すことが可能になります。このようなケースを考えてみます。

CREATE OR REPLACE PROCEDURE hello_proc()
AS $$
    BEGIN
	RAISE NOTICE 'HELLO WOLRD';
	COMMIT;
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION hello_func2()
RETURNS VOID AS $$
    BEGIN
	CALL hello_proc();
    END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE hello_proc2()
AS $$
    BEGIN
	CALL hello_proc();
    END;
$$ LANGUAGE plpgsql;

下記コマンドを実行すると下記のようにトランザクションに関連するエラーがおきます。

test=# CALL hello_proc();
NOTICE:  HELLO WOLRD
CALL
test=# SELECT hello_func2();
NOTICE:  HELLO WOLRD
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function hello_proc() line 4 at COMMIT
SQL statement "CALL hello_proc()"
PL/pgSQL function hello_func2() line 3 at CALL

一方、プロシージャからネストしてコールしたケースでは、下記のように正常に終了します。

test=# CALL hello_proc2();
NOTICE:  HELLO WOLRD
CALL
test=#

このようにトランザクションのネスト自体が、CALL命令でのみ可能です。

今回は、PostgreSQL11で追加されたプロシージャについて記載しました。あまり利用されているイメージはないですが、古いデータベースから移植するなど、アプリでトランザクションを使って実装している部分もデータベース側へ移植もできるかと思います。機会があれば利用してみましょう。