背景
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の抽出クエリに関しては別記事に解説をまとめています。