【PostgreSQL】PL/pgSQLで全テーブルに一括で更新日時設定のトリガーをセットする

やること

テーブル別で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設定を上手く利用すればトリガー設定は省略できるかもしれません。

qiita.com