【PostgreSQL】更新日時記録用のトリガーをマイグレーション毎に自動設定する【Flyway】 #PostgreSQL

背景

PostgreSQLでは、更新日時を記録したい場合トリガーを設定する必要があります。
これをテーブル追加毎に設定するのは手間で、漏れる可能性も有ります。

そこで、このトリガーをマイグレーション毎に自動設定します。

前提

マイグレーションに関してはFlywayで管理します。
また、更新日時のカラム定義はUPDATED_AT TIMESTAMP WITH TIME ZONE DEFAULT current_timestampで統一されているものとします。

詳細なバージョンは以下の通りです。

flyway-gradle-plugin(org.flywaydb.flyway): 8.5.10
postgres: 13

やり方

以下のplsqlをafterMigrate__${識別用の名前}.sqlというようなファイル名で配置することで、毎回のマイグレーション成功後にトリガー設定を実行することができます。

afterMigrate.sql

-- UPDATED_ATの更新用トリガー
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
  RETURNS TRIGGER AS $$ BEGIN NEW.UPDATED_AT = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

DO
$$
DECLARE
  -- UPDATED_ATカラムを持つ更新トリガー未設定テーブルを抽出
  has_updated_at_tables CURSOR FOR
    SELECT c.table_name
    FROM information_schema.columns c
      LEFT JOIN information_schema.triggers t ON t.event_object_table = c.table_name
        AND t.event_object_schema = c.table_schema -- 同名別スキーマ対策でスキーマも指定
        AND t.trigger_name = 'set_timestamp' -- JOIN対象はset_timestampトリガーのみ
    WHERE c.table_schema = 'public'
      AND c.column_name ILIKE 'UPDATED_AT' -- 取得対象はUPDATED_ATのあるテーブル（定義は大文字だが、POSTGRES上は小文字扱いなため、ILIKEで検索している）
      AND t.trigger_name IS NULL; -- JOINできなかった = set_timestamp未設定のみ対象
  table_name VARCHAR;
BEGIN
  OPEN has_updated_at_tables;
  LOOP
    -- テーブル名を取得、取得できなくなればループ終了
    FETCH has_updated_at_tables INTO table_name;
    EXIT WHEN NOT FOUND;
    EXECUTE format(
      'CREATE TRIGGER set_timestamp
       BEFORE UPDATE ON %s
       FOR EACH ROW
       EXECUTE PROCEDURE trigger_set_timestamp()',
      'public.' || table_name
    );
  END LOOP;
END
$$ LANGUAGE PLPGSQL;

以下、やっていることを解説します。

ファイル名について

ファイル名をafterMigrateから始めているのは、毎回のマイグレーション成功後に実行するためです。
これにはFlywayのCallback機能を利用しています。

トリガーの自動設定について

自動設定の肝であるDO $$...以降の部分は、以下のような構成になっています。

UPDATED_ATカラムを持ち、trigger_set_timestampが定義されていないテーブル一覧を抽出
1で取得されたテーブルに対してCREATE TRIGGERをそれぞれ実行

1の抽出クエリに関しては別記事に解説をまとめています。