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