user icon

PostgreSQLのTRIGGER備忘録

PostgreSQLのTRIGGERは滅多に使わない>忘れてしまっていたので思い出すのを兼ねて備忘録とします。
今回はテーブルに対しての追加更新削除時のデータを、ログテーブルに丸ごと保存する例です。

まず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が増加してるのは、何度か試したので。
※時間が一緒なのは一括実行の結果なので、一行ずつ実行すれば異なる。
Facebooktwitterlinkedintumblrmail
名前
E-mail
URL
コメント

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)