PL/pgSQL : PostgreSQL でプロシージャ・トリガーを実装する
Oracle DB を触っていた時に PL/SQL というプログラミング言語を触っていた。同様の手続き型言語が PostgreSQL にも用意されていて、PL/pgSQL という。
今回は対象テーブルにインサートが発生した時にその内容を標準出力するというプロシージャを作成し、PostgreSQL に適用してみる。Docker コンテナとかで動いている PostgreSQL を想定して、標準出力をロギングに利用しているので、そこに必要なログを追加で流すためにプロシージャを使うというようなシナリオだ。
目次
プロシージャを作成する
まずはプロシージャを作成する。プロシージャは「関数」とも言い換えられ、このあと定義する「トリガー」によって実行される処理を実装する。
CREATE OR REPLACE FUNCTION logging_my_table()
RETURNS TRIGGER AS $$
DECLARE
-- 単一行コメントはハイフン2つ。C や Java と同じブロックコメントも書ける
-- DECLARE は変数宣言のブロック。以下は「my_table」というテーブルの型を指定した「new_record」変数を定義している
new_record my_table%ROWTYPE;
BEGIN
-- インサートされた行は、組み込み変数「NEW」に設定されている
new_record := NEW;
-- 標準出力にログを出力する。「%」を使用して my_table の id カラムの値を表示させている
RAISE LOG 'Inserted : %', new_record.id;
-- インサート時のプロシージャでは戻り値を使わないので以下のように NULL を返す。RETURN を書かないと警告が出る
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
こんな感じ。ほとんど PL/SQL と同様の構文。公式のリファレンスを参考に見様見真似で実装してみた。
RAISE がログ出力するための処理。RAISE LOG で LOG レベルのログを出力できる。ログレベルは以下のモノがあり、それぞれで標準出力されるかどうかなどの挙動が違った。
# 以下の3つのレベルは標準出力にログが出てこなかった
RAISE DEBUG 'Debug Level Log!';
RAISE INFO 'Info Level Log!';
RAISE NOTICE 'Notice Level Log!';
# 以下の2つのレベルは標準出力にログが出た
RAISE LOG 'Log Level Log!';
RAISE WARNING 'Warning Level Log!';
# 以下の2つはログ出力した行で処理が中止され、後続行が動作しない
# RAISE のログレベル未指定は EXCEPTION と同義になる
RAISE EXCEPTION 'Exception Level Log!';
RAISE 'Raise Log!';
Info ログなつもりで考えているので、今回は RAISE LOG を使っている。
トリガーを作成する
次に、「テーブルにインサートが発生した時に上述のプロシージャを実行する」という、トリガー定義を実装する。
PostgreSQL の場合、CREATE OR REPLACE TRIGGER という構文はないので、先に DROP TRIGGER IF EXISTS を使って、CREATE OR REPLACE 相当の処理を実現する。
DROP TRIGGER IF EXISTS trigger_my_table ON my_table;
CREATE TRIGGER trigger_my_table
AFTER INSERT ON my_table
FOR EACH ROW
EXECUTE PROCEDURE logging_my_table();
ON 【テーブル名】 で対象のテーブルを指定し、AFTER INSERT で実行タイミングを指定している。
どんなログが出力されるのか
プロシージャとトリガーを登録したら、実際にどんなログが出力されるのか見てみる。
# 以下の要領で標準出力を確認する想定
$ docker logs -f my-postgres
別のターミナルで my_table にインサートをかけてみる。
SQL> INSERT INTO my_tables (id, name) VALUES (100, 'Example');
標準出力には次のようなログが出力された。
2020-08-01 00:10:25.500 UTC [7690] LOG: Inserted : 100
2020-08-01 00:10:25.500 UTC [7690] CONTEXT: PL/pgSQL function logging_my_table() line 4 at RAISE
2020-08-01 00:10:25.500 UTC [7690] STATEMENT: INSERT INTO my_table VALUES (100, 'Example');
CONTEXT と STATEMENT の行は勝手に出る。自分が意図して出したのは LOG: の行のみ。整形は後でやるからとりあえずコレでいいや〜。