LoginSignup
0

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

Last updated at Posted at 2023-09-05

背景

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

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

前提

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

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

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

やり方

以下のplsqlafterMigrate__${識別用の名前}.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から始めているのは、毎回のマイグレーション成功後に実行するためです。
これにはFlywayCallback機能を利用しています。

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

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

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0