«
»
aikawa

PostgreSQLのTRIGGER備忘録

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が増加してるのは、何度か試したので。

※時間が一緒なのは一括実行の結果なので、一行ずつ実行すれば異なる。

カテゴリー: PostgreSQL, 未分類

«
»