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
Could you please explain how the hs_old - hs_new works to get the original value?
ReplyDeleteThe 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.
ReplyDeletesimilar solution: http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html
ReplyDeleteakretschmer
Hi! I am using this code for catching column changes:
ReplyDeleteIF 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;