most simply implementation of history table with hstore extension
Postgres has this nice extension (hstore) lot of years. It can be used for simulation some features of doc databases - or can be used for implementation of generic triggers for history table:
I have a table test and table history:
CREATE TABLE test(a int, b int, c int);
CREATE TABLE history(
event_time timestamp(2),
executed_by text,
origin_value hstore,
new_value hstore
);
CREATE OR REPLACE FUNCTION history_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO history(event_time, executed_by, new_value)
VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(NEW));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION history_delete()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO history(event_time, executed_by, origin_value)
VALUES(CURRENT_TIMESTAMP, SESSION_USER, hstore(OLD));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION history_update()
RETURNS TRIGGER AS $$
DECLARE
hs_new hstore := hstore(NEW);
hs_old hstore := hstore(OLD);
BEGIN
INSERT INTO history(event_time, executed_by, origin_value, new_value)
VALUES(CURRENT_TIMESTAMP, SESSION_USER, hs_old - hs_new, hs_new - hs_old);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER test_history_insert AFTER INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE history_insert();
CREATE TRIGGER test_history_delete AFTER DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE history_delete();
CREATE TRIGGER test_history_update AFTER UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE history_update();
Result:INSERT INTO test VALUES(1000, 1001, 1002);
UPDATE test SET a = 10, c = 20;
DELETE FROM test;
postgres=# SELECT * FROM history ;
event_time | executed_by | origin_value | new_value
------------------------+-------------+-----------------------------------+---------------------------------------
2015-01-15 20:59:05.52 | pavel | | "a"=>"1000", "b"=>"1001", "c"=>"1002"
2015-01-15 20:59:05.6 | pavel | "a"=>"1000", "c"=>"1002" | "a"=>"10", "c"=>"20"
2015-01-15 20:59:06.51 | pavel | "a"=>"10", "b"=>"1001", "c"=>"20" |
(3 rows)
Tested on PostgreSQL 9.2


4 Comments:
Could you please explain how the hs_old - hs_new works to get the original value?
The hs_old - hs_new returns key/value pairs from hs_old that are not in hs_new (in this case the ones that have different value), it's standard hstore "-" operator.
similar solution: http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html
akretschmer
Hi! I am using this code for catching column changes:
IF TG_OP = 'UPDATE' THEN
_new_version := hstore(NEW);
SELECT delete((hstore(OLD),_new_version))
INTO _old_version;
FOR _changed_vals IN SELECT k.key
FROM skeys(_old_version) AS k(key)
WHERE key NOT IN (... list of ignored columns as id etc .. )
LOOP
INSERT INTO history
(
history_name,
history_old,
history_new
) VALUES (
_changed_vals.key,
_old_version->_changed_vals.key,
_new_version->_changed_vals.key,
);
END LOOP;
END IF;
Post a Comment
Subscribe to Post Comments [Atom]
<< Home