Copying Pavel Stehule’s simple history table but with the jsonb type

On 15/01/2015 Pavel Stehule wrote about implementing a dead simple history table using the hstore type.

On Friday evening I wanted to copy this almost line for line switching the hstore type for jsonb , but I counldn’t really see how to replicate the update part so simply without creating a delete operator. Once that operator has been created it appears to work:

CREATE TABLE test(a int, b int, c int);

CREATE TABLE history(
  event_time timestamp(2),
  executed_by text, 
  origin_value jsonb,
  new_value jsonb
);

CREATE OR REPLACE FUNCTION history_insert()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO history(event_time, executed_by, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, row_to_json(NEW)::jsonb);
  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, row_to_json(OLD)::jsonb);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION history_update()
RETURNS TRIGGER AS $$
DECLARE
  js_new jsonb := row_to_json(NEW)::jsonb;
  js_old jsonb := row_to_json(OLD)::jsonb;
BEGIN
  INSERT INTO history(event_time, executed_by, origin_value, new_value)
     VALUES(CURRENT_TIMESTAMP, SESSION_USER, js_old - js_new, js_new - js_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=# table history;
       event_time       | executed_by |         origin_value          |             new_value             
------------------------+-------------+-------------------------------+-----------------------------------
 2015-01-19 13:12:54.26 | glyn        |                               | {"a": 1000, "b": 1001, "c": 1002}
 2015-01-19 13:12:54.31 | glyn        | {"a": 1000, "c": 1002}        | {"a": 10, "c": 20}
 2015-01-19 13:12:54.94 | glyn        | {"a": 10, "b": 1001, "c": 20} | 
(3 rows)

Tested on PostgreSQL 9.4

2 thoughts on “Copying Pavel Stehule’s simple history table but with the jsonb type

  1. Karri

    Superb post, thank you.
    One note, pretty sure your history_update function won’t work, there is no jsonb – jsonb operator?
    As a workaround you could do it like this
    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)::jsonb, (hs_new – hs_old)::jsonb);
    RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    Reply
  2. Glyn Astill Post author

    Thanks! You’re correct that there is no jsonb-jsonb operator, but if you read the post again from the start you’ll see I stated that to begin with. Perhaps I wasn’t quite clear enough, sorry about that, but it was the whole point of the post. See the link in the second paragraph of the post to add what we needed.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *