Revision Database

revdb.sql

DROP TYPE IF EXISTS tmp_events_op CASCADE;
CREATE TYPE tmp_events_op AS ENUM ('create', 'update', 'delete');

DROP TABLE IF EXISTS tmp_events;
CREATE TEMPORARY TABLE tmp_events
(
    id    SERIAL PRIMARY KEY,
    op    tmp_events_op NOT NULL,
    name  text          NOT NULL,
    value text          NOT NULL
);

INSERT INTO tmp_events
    (op, name, value)
VALUES ('create', 'name', 'josh')
     , ('update', 'name', 'josh2')
     , ('update', 'name', 'josh3')
     , ('create', 'sex', 'male')
     , ('update', 'sex', 'female')
     , ('create', 'scar', 'on face')
     , ('create', 'age', '30')
     , ('update', 'age', '40')
     , ('delete', 'age', '')
;

SELECT json_object_agg(
               rows.name,
               rows.last_value
           )
FROM (
         SELECT n.name,
                (SELECT value
                 FROM tmp_events
                 WHERE op IN ('update', 'create')
                   AND name = n.name
                 ORDER BY id DESC
                 LIMIT 1
                )         AS last_value,
                (SELECT CASE WHEN op = 'delete' THEN true else false END
                 FROM tmp_events
                 WHERE name = n.name
                 ORDER BY id DESC
                 LIMIT 1) as is_deleted
         FROM (
                  SELECT name
                  FROM tmp_events
                  GROUP BY name
              ) n
     ) rows
WHERE rows.is_deleted = false


/*Outputs:
{
  "name": "josh3",
  "sex": "female",
  "scar": "on face"
}
*/