Skip to content

Instantly share code, notes, and snippets.

@jdmaturen jdmaturen/posts.sql Secret

forked from DRMacIver/posts.sql
Created Dec 7, 2009
Embed
What would you like to do?
Insert-only complete history database model via Postgresql Rules
-- Cf. http://www.drmaciver.com/2009/11/filtering-deleted-documents-with-postgresql-rules/
-- and related comments
BEGIN;
create sequence post_ids;
create table unfiltered_posts(id int primary key default nextval('post_ids'),
parent_id int references unfiltered_posts (id),
created timestamp not null default current_timestamp,
body text);
create or replace rule unfiltered_posts_update as on update to unfiltered_posts do instead nothing;
create or replace rule unfiltered_posts_delete as on delete to unfiltered_posts do instead nothing;
create view posts as select coalesce(parent_id, id) as id, created as updated, body
from unfiltered_posts where body is not null and id in
(select max(id) from unfiltered_posts group by coalesce(parent_id, id));
create or replace rule insert_into_posts as
on insert to posts do instead
insert into unfiltered_posts
values(coalesce(NEW.id, nextval('post_ids')),
null,
coalesce(NEW.updated, current_timestamp),
NEW.body
);
create or replace rule update_to_posts as
on update to posts do instead
insert into unfiltered_posts
values(nextval('post_ids'),
OLD.id,
coalesce(NEW.updated, current_timestamp),
NEW.body
);
create or replace rule delete_posts as
on delete to posts do instead
insert into unfiltered_posts
select nextval('post_ids'),
OLD.id,
current_timestamp,
null
where exists (select id from posts where id = OLD.id);
insert into posts (body) values ('I like kittens');
insert into posts (body) values ('I am the very model of a modern major general');
select * from posts;
-- id | updated | body
-- ----+---------------------------+-----------------------------------------------
-- 1 | 2009-12-06 21:43:09.53681 | I like kittens
-- 2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- (2 rows)
select * from unfiltered_posts order by id desc;
-- id | parent_id | created | body
-- ----+-----------+---------------------------+-----------------------------------------------
-- 2 | | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- 1 | | 2009-12-06 21:43:09.53681 | I like kittens
-- (2 rows)
update posts set body = 'I like puppies' where id = 1;
select * from posts;
-- id | updated | body
-- ----+---------------------------+-----------------------------------------------
-- 2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- 1 | 2009-12-06 21:43:09.53681 | I like puppies
-- (2 rows)
select * from unfiltered_posts order by id desc;
-- id | parent_id | created | body
-- ----+-----------+---------------------------+-----------------------------------------------
-- 3 | 1 | 2009-12-06 21:43:09.53681 | I like puppies
-- 2 | | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- 1 | | 2009-12-06 21:43:09.53681 | I like kittens
-- (3 rows)
delete from posts where id = 1;
select * from posts;
-- id | updated | body
-- ----+---------------------------+-----------------------------------------------
-- 2 | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- (1 row)
select * from unfiltered_posts order by id desc;
-- id | parent_id | created | body
-- ----+-----------+---------------------------+-----------------------------------------------
-- 4 | 1 | 2009-12-06 21:43:09.53681 |
-- 3 | 1 | 2009-12-06 21:43:09.53681 | I like puppies
-- 2 | | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- 1 | | 2009-12-06 21:43:09.53681 | I like kittens
-- (4 rows)
delete from posts;
select * from posts;
-- id | updated | body
-- ----+---------+------
-- (0 rows)
select * from unfiltered_posts order by id desc;
-- id | parent_id | created | body
-- ----+-----------+---------------------------+-----------------------------------------------
-- 5 | 2 | 2009-12-06 21:43:09.53681 |
-- 4 | 1 | 2009-12-06 21:43:09.53681 |
-- 3 | 1 | 2009-12-06 21:43:09.53681 | I like puppies
-- 2 | | 2009-12-06 21:43:09.53681 | I am the very model of a modern major general
-- 1 | | 2009-12-06 21:43:09.53681 | I like kittens
-- (5 rows)
update unfiltered_posts set body = 'I do not like kittens' where id = 1;
select body from unfiltered_posts where id = 1;
-- body
-- ----------------
-- I like kittens
-- (1 row)
-- Postgres doesn't lie!
delete from unfiltered_posts;
select count(*)=5 from unfiltered_posts;
-- ?column?
-- ----------
-- t
-- (1 row)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.