PostgreSQL trigger magic (CouchDB-like _changes)

test.sql

-- tables as they are
create sequence post_id_seq;
create table post (id bigint not null, content text not null, constraint post_pkey primary key(id));
create sequence post_comment_id_seq;
create table post_comment (id bigint not null, post_id bigint not null, content text not null, constraint post_comment_pkey primary key(id), constraint fk_post_comment_post_id foreign key(post_id) references post(id));
-- basics for triggers
create sequence changes_seq;
create or replace function changes_seq_fkt() returns trigger as $$ begin NEW.seq=nextval('changes_seq'); NEW.ts=now(); return NEW; end; $$ language plpgsql;
-- add sequence / ts info to our tables
alter table post add ts timestamp, add seq bigint;
create trigger post_changes_insert before insert on post for each row execute procedure changes_seq_fkt();
create trigger post_changes_update before update on post for each row when (OLD.* is distinct from NEW.*) execute procedure changes_seq_fkt();
alter table post_comment add ts timestamp, add seq bigint;
create trigger post_comment_changes_insert before insert on post_comment for each row execute procedure changes_seq_fkt();
create trigger post_comment_changes_update before update on post_comment for each row when (OLD.* is distinct from NEW.*) execute procedure changes_seq_fkt();
-- do first tests
insert into post (id, content) values (nextval('post_id_seq'), 'My first post on cool Triggers applied to hot summer nights');
update post set content='My first post on cool Triggers applied to inspired summer nights' where id=currval('post_id_seq');
commit;
insert into post(id, content) values (nextval('post_id_seq'), 'Yet another post on the Trigger thing');
commit;
insert into post_comment (id, post_id, content) values (nextval('post_comment_id_seq'), currval('post_id_seq'), 'This post is crap!');
commit;
-- create some index
create unique index post_seq_idx on post(seq);
create unique index post_comment_seq_idx on post_comment(seq);
-- do some unions (assume where yields a relatively small subset, i.e. we pull often)
select seq, ts, id, tab from (
select seq, ts, id, 'post' as tab from post
union
select seq, ts, id, 'post_comment' as tab from post_comment
) as alltab where seq>=3 order by seq;
-- call it a day - hey!