Corredor

ウェブ、プログラミングの勉強メモ。

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 LOGLOG レベルのログを出力できる。ログレベルは以下のモノがあり、それぞれで標準出力されるかどうかなどの挙動が違った。

# 以下の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');

CONTEXTSTATEMENT の行は勝手に出る。自分が意図して出したのは LOG: の行のみ。整形は後でやるからとりあえずコレでいいや〜。

わたしとぼくのPL/pgSQL (技術の泉シリーズ(NextPublishing))

わたしとぼくのPL/pgSQL (技術の泉シリーズ(NextPublishing))

  • 作者:目黒 聖
  • 発売日: 2019/04/12
  • メディア: オンデマンド (ペーパーバック)