やること
テーブル別でUPDATED_AT
カラムへのトリガー設定を行っている状況が有ったとします。
-- 関数作成 CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.UPDATED_AT = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -- テーブル毎にトリガー設定 CREATE TABLE IF NOT EXISTS FOO(...); CREATE TRIGGER set_timestamp BEFORE UPDATE ON FOO FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp(); CREATE TABLE IF NOT EXISTS BAR(...); CREATE TRIGGER set_timestamp BEFORE UPDATE ON BAR FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
この書き方は可読性が悪く、設定漏れを起こしかねません。
そこで、全テーブルへの設定処理一括で行う形に修正を行います。
やったこと
PL/pgSQL
を使って以下のような処理を作成しました。
管理用テーブル等を除いてUPDATED_AT
カラムを持つテーブルを抽出し、それらに対してそれぞれトリガー設定を呼び出しています。
DO $$ DECLARE -- postgresの管理用テーブルやflyway関連以外で、UPDATED_ATカラムを持つテーブルを抽出(その他除外したいテーブルはここに書く) has_updated_at_tables CURSOR FOR SELECT t.table_name FROM information_schema.tables t INNER JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = t.table_schema WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' AND t.table_name != 'flyway_schema_history' AND c.column_name ILIKE 'UPDATED_AT'; -- ファイル上の定義は大文字だが、POSTGRES上は小文字扱いなため、ILIKEで検索している 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()', table_name ); END LOOP; END $$ LANGUAGE PLPGSQL;
これを用いると、先程のDDL
は以下のようになります。
-- 関数作成 CREATE TABLE IF NOT EXISTS FOO(...); CREATE TABLE IF NOT EXISTS BAR(...); -- 関数作成 CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.UPDATED_AT = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -- UPDATED_ATのトリガーを一括設定 DO $$ DECLARE -- postgresの管理用テーブルやflyway関連以外で、UPDATED_ATカラムを持つテーブルを抽出(その他除外したいテーブルはここに書く) has_updated_at_tables CURSOR FOR SELECT t.table_name FROM information_schema.tables t INNER JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = t.table_schema WHERE t.table_schema = 'public' AND t.table_type = 'BASE TABLE' AND t.table_name != 'flyway_schema_history' AND c.column_name ILIKE 'UPDATED_AT'; -- ファイル上の定義は大文字だが、POSTGRES上は小文字扱いなため、ILIKEで検索している 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()', table_name ); END LOOP; END $$ LANGUAGE PLPGSQL;
補足
自分は試していませんが、DEFAULT
設定を上手く利用すればトリガー設定は省略できるかもしれません。