Skip to content

Instantly share code, notes, and snippets.

@dotjosh
Last active Nov 14, 2020
Embed
What would you like to do?
Revision Database
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"
}
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment