【MEMO】Postgresでテーブルごとの変更履歴を取るトリガー

Postgresでテーブルごとの履歴を取るトリガー

めもめも。
こちらを参考にした。
https://qiita.com/nishimura/items/aba93df97f02b571c3ce

1. 「schema.__tablename」に突っ込む
2. old_datasは１個前のデータ。変更のないUPDATEは履歴を取らない

CREATE FUNCTION define.insert_log()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
  DECLARE
    history_table NAME := TG_TABLE_SCHEMA || '.__' || TG_TABLE_NAME;
  BEGIN
    EXECUTE 'CREATE TABLE IF NOT EXISTS ' || history_table || '('
        || 'operated_user text NOT NULL,'
        || 'operated_type character(1) NOT NULL,'
        || 'operated_at timestamp without time zone NOT NULL,'
        || 'old_datas jsonb NOT NULL'
        || ')';
    IF (TG_OP = 'DELETE') THEN
      EXECUTE 'INSERT INTO ' || history_table ||
        ' SELECT USER, $1, NOW(), to_json($2)'
      USING 'd', OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
      EXECUTE 'INSERT INTO ' || history_table ||
        ' SELECT USER, $1, NOW(), to_json($2) WHERE $2 != $3'
      USING 'u', OLD, NEW;
    ELSIF (TG_OP = 'INSERT') THEN
      EXECUTE 'INSERT INTO ' || history_table ||
        ' SELECT USER, $1, NOW(), ''{}'''
      USING 'c';
    END IF;
    RETURN NULL;
  END;
$BODY$;

あとはトリガーをセットするだけ。

CREATE TRIGGER add_test_table_log
    AFTER INSERT OR DELETE OR UPDATE 
    ON data.test_table
    FOR EACH ROW
    EXECUTE FUNCTION define.insert_log();

投稿する際に履歴テーブルを勝手に作ったら楽？
と思って足してみたけど、権限とか考えたら外すべきかも。

これで１個前に戻したい！いつ実行したっけ？？
という悩みが解決され、るといいなー。