[pgsql-jp: 28261] Re: トリガファンクションのカラム名を変数で使いたい
Chie.M
gontakun ＠ check.ne.jp
2002年 12月 11日 (水) 16:14:51 JST
Chieです。Mashikiさん何度もありがとうございます。
> >「トリガーファンクションとトリガーをCreateするようなファンクションを定義」
> >するサンプルを作ってみました。
> >トリガも発行できると言う事、ご報告いたします。
> おおなるほど、無責任発言の検証をしていただきありがとうございます。
とんでもないです。色々助言いただきまして、ありがとうございます。
> >下記に作成した簡単なサンプルを貼っておきます。
> >トリガファンクションとトリガを発行するものです。
>
> トリガーを作成する際は、システムカタログをみて必要があればDrop Trigger
> も発行するんでしょうか。
えーっとそれについては何も考えてませんでした(^_^;)
TRIGGERが存在したらDROPする物を追加した物を作成しました。
ありがとうございます。
> ところで、Create Functionを発行する関数は、参照しているシステム
> カタログのトリガーになるように定義すると、全自動でいけそうな気も
> しますね。（できるのかわからず、さらに無責任モードで書いています。
> だまされないでください）
ものすごく良い考えですね！早速テストしてみます！！
ということで、今まで教えていただいた事のご報告を兼ねて
一つのテーブルに複数のテーブルの更新履歴をとる汎用の関数を
作ってみましたので貼ります。
汎用なので、下記の「準備1」のものさえ用意してあれば
普通に使えるんじゃないかと思いますが自信はないです...。
上記の全自動の件については、末尾に貼っておきました。
（長文になります。申し訳ありません）
・準備1
システムカタログからテーブル名とカラム名を参照するビューと
更新履歴を格納するテーブルを作成します。
-----------------------------------------------
--テーブルとカラムの一覧のビュー
----抽出条件に権限指定を含めているのでビューに表示させたい
----テーブルは必ず権限を指定する必要有
CREATE VIEW Vs_AllTableObject AS
SELECT
pc.oid AS tbl_id, --テーブルID
pc.relname AS tbl_name, -- テーブル名
pa.attname AS tbl_cal, --列名
pt.typname AS tbl_type, -- データ型
pc.relnatts AS tbl_cal_num -- 列数
FROM
pg_attribute as pa
JOIN pg_class as pc
ON (pa.attrelid = pc.oid)
JOIN pg_type as pt
ON (pa.atttypid = pt.oid)
WHERE
pc.relkind = 'r' AND
pc.relacl IS NOT NULL AND
pa.attnum > 0 AND
pc.oid > 200000
ORDER BY pc.oid;
COMMENT ON VIEW Vs_AllTableObject Is 'DB格納テーブル一覧';
COMMENT ON COLUMN Vs_AllTableObject.tbl_id IS 'テーブルID';
COMMENT ON COLUMN Vs_AllTableObject.tbl_name IS 'テーブル名';
COMMENT ON COLUMN Vs_AllTableObject.tbl_cal IS '列名';
COMMENT ON COLUMN Vs_AllTableObject.tbl_type IS 'データ型';
COMMENT ON COLUMN Vs_AllTableObject.tbl_cal_num IS '列数';
GRANT SELECT ON Vs_AllTableObject to GROUP mygroup;
-----
--変更履歴用テーブル
CREATE TABLE update_log(
method_name VARCHAR(10),
tbl_name VARCHAR(100),
tbl_cal VARCHAR(100),
old_data TEXT,
new_data TEXT,
update_time DATETIME,
update_name NAME
);
COMMENT ON TABLE update_log Is '更新履歴';
COMMENT ON COLUMN update_log.method_name IS 'テーブルID';
COMMENT ON COLUMN update_log.tbl_name IS 'テーブル名';
COMMENT ON COLUMN update_log.tbl_cal IS '列名';
COMMENT ON COLUMN update_log.old_data IS '旧データ';
COMMENT ON COLUMN update_log.new_data IS '新データ';
COMMENT ON COLUMN update_log.update_time IS '更新日時';
COMMENT ON COLUMN update_log.update_name IS '更新者';
GRANT SELECT ON update_log to GROUP mygroup;
-----------------------------------------------
・準備2
更新履歴をテストする為のテーブルを作成。
-----------------------------------------------
--DROP TABLE test_data;
--データ更新テーブル
CREATE TABLE test_data(
t_id int4,
t_data1 CHAR(10),
t_data2 int4,
t_data3 CHAR(1)
);
--データを入れておく
INSERT INTO test_data VALUES(10,'ABCDE',123,'A');
INSERT INTO test_data VALUES(20,'TOKYO',456,'B');
INSERT INTO test_data VALUES(30,'SAITAMA',789,'C');
INSERT INTO test_data VALUES(40,'TEST',101,'D');
INSERT INTO test_data VALUES(50,'KANAGAWA',999,'E');
GRANT SELECT ON test_data to GROUP mygroup;
-----------------------------------------------
・本題
関数とトリガの作成
-----------------------------------------------
-- 「変更履歴挿入関数」をセットする関数
CREATE OR REPLACE FUNCTION fnc_Set_updatelog(text) RETURNS INT4 AS '
DECLARE
cu refcursor;
rec record;
tbl_rel ALIAS FOR $1;
colname varchar(100);
insert_sql text;
create_function text;
create_trigger text;
BEGIN
insert_sql := '''';
-- テーブル名カラム一覧を開く
OPEN cu FOR EXECUTE
''SELECT vsa.tbl_name, vsa.tbl_cal
FROM Vs_AllTableObject AS vsa
WHERE tbl_name = '' || quote_literal(tbl_rel) || '';'';
LOOP
-- 一行ずつ取り出す
FETCH cu INTO rec;
IF NOT FOUND THEN
EXIT;
END IF;
-- 列名を代入
colname := TRIM(rec.tbl_cal);
-- データ挿入用SQL作成
insert_sql := insert_sql ||
''INSERT INTO update_log ( method_name,
tbl_name, tbl_cal, old_data, new_data,
update_time, update_name)
VALUES(TG_OP, \'\'\'\''' || tbl_rel || ''\'\'\'\', \'\'\'\''' || colname || ''\'\'\'\',
OLD.'' || colname || ''::text, NEW.'' || colname || ''::text,
now_timestamp, now_user);
'';
END LOOP;
CLOSE cu;
--変更履歴挿入関数を作成する文
create_function := ''CREATE OR REPLACE FUNCTION fnc_updatelog_'' || tbl_rel || ''()
RETURNS OPAQUE AS ''''
DECLARE
now_timestamp datetime;
now_user varchar(10);
BEGIN
now_timestamp := ''''''''now'''''''';
now_user := current_user;
IF TG_OP = ''''''''UPDATE'''''''' THEN
''
|| insert_sql
|| ''DELETE FROM update_log WHERE old_data = new_data;
RETURN new;
END IF;
END;
'''' LANGUAGE ''''plpgsql'''';'';
--トリガー作成の文
create_trigger := ''CREATE TRIGGER trg_updatelog
BEFORE UPDATE
ON '' || tbl_rel || ''
FOR EACH ROW
EXECUTE PROCEDURE fnc_updatelog_'' || tbl_rel || ''();'';
--トリガが存在したら削除
SELECT INTO rec *
FROM pg_trigger as t, pg_class as c
WHERE t.tgrelid = c.oid
AND t.tgname = ''trg_updatelog''
AND c.relname = quote_literal(tbl_rel);
IF NOT FOUND THEN
ELSE
DROP TRIGGER trg_updatelog ON tbl_rel;
END IF;
--関数とトリガを作成
EXECUTE create_function;
EXECUTE create_trigger;
RETURN 1;
END; '
LANGUAGE 'plpgsql';
-----------------------------------------------
・テスト
テスト用テーブルにセットして、テストする
-----------------------------------------------
--テスト用のtest_dataテーブルに対して関数とトリガをセット
SELECT fnc_Set_updatelog('test_data');
--データ変更テスト
UPDATE test_data
SET t_id = 32, t_data1 = 'AAAZZB',
t_data2 = 902
WHERE t_id = 30;
--確認
SELECT * FROM update_log;
-----------------------------------------------
さてここで、Mashikiさんのおっしゃっていた
・システムカタログのトリガーになるように定義すると全自動
という事が是非やりたかったので、いくつかテストしてみました。
関数を実行する部分をトリガにしてみました。
----
CREATE TRIGGER trg_set_updatelog
BEFORE INSERT
ON pg_class
FOR EACH ROW
EXECUTE PROCEDURE fnc_Set_updatelog('test_Data');
----
これを実行すると
----
ERROR: CreateTrigger: can't create trigger for relation pg_class
----
と返ってきました。
システムカタログにトリガはつくれないみたいです。残念・・・。
次にルールで挑戦してみました。
----
--DROP RURE ru_create_updatelog;
CREATE RURE ru_create_updatelog AS
ON INSERT
TO pg_class
DO INSTEAD
SELECT fnc_Set_updatelog(NEW.relname);
----
ルールは作成できます。
しかし、新しいテーブルを作成しても無反応。
エラーも出ないし実行もされないです。
# それ以前にルールの書き方があっているか全く
# 自信がないです・・・(^_^;)
システムカタログの動作がよくわからないのですが、
テーブル作成時にINSERT句で挿入しているわけじゃ
ないんじゃないか、と思いました。
ソースを読み取るほどの知識がないのでスミマセン・・・。
INSERTが実行されないので動かない、と言う事では
ないでしょうか？？
# 以上、何か変だったら突っ込んでください<(_ _)>
結果：
全自動で実行させる事は無理そうですが、カナリ汎用に近い
更新履歴の関数を作成する事ができました！
どうもありがとうございました！
------------------------
From：Chie.M
gontakun ＠ check.ne.jp
------------------------
pgsql-jp メーリングリストの案内