Pages

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:

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

    ReplyDelete
  2. 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.

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

    akretschmer

    ReplyDelete
  4. 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;

    ReplyDelete