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