PostgreSQLのTRIGGER備忘録 « LANCARD.LAB｜ランカードコムのスタッフブログ

PostgreSQLのTRIGGER備忘録

2020/10/20
aikawa

PostgreSQLのTRIGGERは滅多に使わない＞忘れてしまっていたので思い出すのを兼ねて備忘録とします。

今回はテーブルに対しての追加更新削除時のデータを、ログテーブルに丸ごと保存する例です。

参考情報は以下URL。
https://www.postgresql.jp/document/9.6/html/plpgsql-trigger.html

まずpostgres等のスーパーユーザでCREATE LANGUAGE。

CREATE LANGUAGE plpgsql;
SELECT * FROM pg_language WHERE lanname = 'plpgsql'; --確認

以降はスーパーユーザーの必要は無いので、データベースオーナーが実施。テスト用テーブルの作成。

CREATE TABLE public.tb(
   id serial NOT NULL,
   note text,
   create_time timestamp without time zone NOT NULL DEFAULT now(),
   CONSTRAINT tb_pkey PRIMARY KEY (id)
);

ログ保存用スキーマlogsを作成して、そこにログ用テーブルを追加。

CREATE SCHEMA logs;

CREATE TABLE logs.log_tb AS
 SELECT 0::bigint AS log_id,''::text AS oper,null::timestamp without time zone AS copy_time,*
 FROM public.tb WHERE id = 0;
CREATE SEQUENCE logs.log_tb_log_id_seq;
ALTER TABLE logs.log_tb ADD PRIMARY KEY (log_id);

トリガ関数を定義。

CREATE OR REPLACE FUNCTION public.tb_copy() RETURNS trigger AS
$BODY$
 BEGIN
   IF (TG_OP = 'DELETE') THEN
     INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = OLD.id;
     RETURN OLD;
   ELSE
     INSERT INTO logs.log_tb SELECT nextval('logs.log_tb_log_id_seq'::regclass),TG_OP,now(),* FROM public.tb WHERE id = NEW.id;
     RETURN NEW;
   END IF;
 END;
$BODY$
LANGUAGE plpgsql VOLATILE;

トリガをtbに仕掛ける。

CREATE TRIGGER tb_change AFTER INSERT OR UPDATE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();
CREATE TRIGGER tb_delete BEFORE DELETE ON public.tb FOR EACH ROW EXECUTE PROCEDURE public.tb_copy();

最後に、データを操作してログが取れるかを確認。

INSERT INTO public.tb(note) VALUES('TEST');
INSERT INTO public.tb(note) VALUES('TEST2');
UPDATE public.tb SET note = 'UPDATE TEST';
DELETE FROM public.tb;
SELECT * FROM logs.log_tb;

これでINSERT, UPDATE, DELETEのログがそれぞれ2件ずつ登録されている。
※log_idやidが増加してるのは、何度か試したので。
※時間が一緒なのは一括実行の結果なので、一行ずつ実行すれば異なる。