Thursday, January 15, 2015

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:

At January 15, 2015 at 12:57 PM , Anonymous Anonymous said...

Could you please explain how the hs_old - hs_new works to get the original value?

 
At January 15, 2015 at 1:23 PM , Anonymous Anonymous said...

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.

 
At January 15, 2015 at 10:31 PM , Blogger Unknown said...

similar solution: http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html

akretschmer

 
At January 16, 2015 at 12:09 PM , Anonymous palcat said...

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