USE hstore store table’s trace record
背景
在数据库应用中，某些数据表的记录更改可能会需要跟踪，例如删除，新增，更新。
跟踪的信息包括：老的记录值，新的记录值，记录变更时间，哪个用户操作的等等。
在数据库中，跟踪可以通过触发器来做。
因为每个表的结构都不一样，要设计一个比较通用的存储跟踪记录的表的话，需要使用通用的存储类型，例如 text 类型，但是text类型存储的值未来要展现的话又比较费劲，还得根据表结构来抽取原来存储的变更信息，本文试图使用 hstore 类型来存储变更前后的row信息。并且使用hstore提供的each函数，可以很方便的取出原来存储的值。
测试 :
创建需要被跟踪的测试表
CREATE TABLE test (id int primary key, info text, crt_time timestamp(0));
创建hstore extension;
CREATE EXTENSION hstore;
创建通用的存储跟踪记录的记录表
CREATE TABLE table_change_rec (
id serial8 primary key,
relid oid,
table_schema text,
table_name text,
when_tg text,
level text,
op text,
old_rec hstore,
new_rec hstore,
crt_time timestamp without time zone DEFAULT now(),
username text,
client_addr inet,
client_port int
);
创建通用的触发器函数
CREATE OR REPLACE FUNCTION dml_trace()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
v_new_rec hstore;
v_old_rec hstore;
v_username text := session_user;
v_client_addr inet := inet_client_addr();
v_client_port int := inet_client_port();
BEGIN
case TG_OP
when 'DELETE' then
v_old_rec := hstore(OLD.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_username, v_client_addr, v_client_port);
when 'INSERT' then
v_new_rec := hstore(NEW.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, new_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_new_rec, v_username, v_client_addr, v_client_port);
when 'UPDATE' then
v_old_rec := hstore(OLD.*);
v_new_rec := hstore(NEW.*);
insert into table_change_rec (relid, table_schema, table_name, when_tg, level, op, old_rec, new_rec, username, client_addr, client_port)
values (tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_old_rec, v_new_rec, v_username, v_client_addr, v_client_port);
else
return null;
end case;
RETURN null;
END;
$BODY$ strict;
在测试表上分别创建插入, 更新, 删除的三个触发器.
CREATE TRIGGER tg AFTER DELETE or INSERT or UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE dml_trace();
测试插入, 删除, 更新操作是否被跟踪.
(已更新dml_trace, 以下例子未包含client_addr和client_port)
postgres=# insert into test values (1, 'digoal', now());
INSERT 0 1
postgres=# select * from test;
id | info | crt_time
----+--------+---------------------
1 | digoal | 2012-06-25 10:54:43
(1 row)
postgres=# select * from table_change_rec;
id | relid | table_schema | table_name | when_tg | level | op | old_rec | new_rec
| crt_time | username
----+-------+--------------+------------+---------+-------+--------+---------+------------------------------------------------------
----------+----------------------------+----------
4 | 23731 | public | test | AFTER | ROW | INSERT | | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25
10:54:43" | 2012-06-25 10:54:42.839553 | postgres
(1 row)
postgres=# update test set info='DIGOAL' where id=1;
UPDATE 1
postgres=# select * from test;
id | info | crt_time
----+--------+---------------------
1 | DIGOAL | 2012-06-25 10:54:43
(1 row)
postgres=# select * from table_change_rec;
id | relid | table_schema | table_name | when_tg | level | op | old_rec
| new_rec | crt_time | username
----+-------+--------------+------------+---------+-------+--------+----------------------------------------------------------------
+----------------------------------------------------------------+----------------------------+----------
4 | 23731 | public | test | AFTER | ROW | INSERT |
| "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:54:42.839553 | postgres
5 | 23731 | public | test | AFTER | ROW | UPDATE | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43"
| "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:55:41.006069 | postgres
(2 rows)
postgres=# delete from test where id=1;
DELETE 1
postgres=# select * from test;
id | info | crt_time
----+------+----------
(0 rows)
postgres=# select * from table_change_rec;
id | relid | table_schema | table_name | when_tg | level | op | old_rec
| new_rec | crt_time | username
----+-------+--------------+------------+---------+-------+--------+----------------------------------------------------------------
+----------------------------------------------------------------+----------------------------+----------
4 | 23731 | public | test | AFTER | ROW | INSERT |
| "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:54:42.839553 | postgres
5 | 23731 | public | test | AFTER | ROW | UPDATE | "id"=>"1", "info"=>"digoal", "crt_time"=>"2012-06-25 10:54:43"
| "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43" | 2012-06-25 10:55:41.006069 | postgres
6 | 23731 | public | test | AFTER | ROW | DELETE | "id"=>"1", "info"=>"DIGOAL", "crt_time"=>"2012-06-25 10:54:43"
| | 2012-06-25 10:56:00.862319 | postgres
(3 rows)
使用each函数分解显示hstore存储的信息.
postgres=# select id,(each(old_rec)).* from table_change_rec;
id | key | value
----+----------+---------------------
5 | id | 1
5 | info | digoal
5 | crt_time | 2012-06-25 10:54:43
6 | id | 1
6 | info | DIGOAL
6 | crt_time | 2012-06-25 10:54:43
(6 rows)
postgres=# select id,(each(new_rec)).* from table_change_rec;
id | key | value
----+----------+---------------------
4 | id | 1
4 | info | digoal
4 | crt_time | 2012-06-25 10:54:43
5 | id | 1
5 | info | DIGOAL
5 | crt_time | 2012-06-25 10:54:43
(6 rows)
触发器变量
When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:
NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.
OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.
TG_NAME
Data type name; variable that contains the name of the trigger actually fired.
TG_WHEN
Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.
TG_LEVEL
Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.
TG_OP
Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.
TG_RELID
Data type oid; the object ID of the table that caused the trigger invocation.
TG_RELNAME
Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.
TG_TABLE_NAME
Data type name; the name of the table that caused the trigger invocation.
TG_TABLE_SCHEMA
Data type name; the name of the schema of the table that caused the trigger invocation.
TG_NARGS
Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.
TG_ARGV[]
Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.
A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for.
参考
http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
http://www.postgresql.org/docs/9.2/static/hstore.html