Versioned items with rules

versions.sql

drop schema if exists foo cascade;
create schema foo;

CREATE TABLE foo.object (
    id uuid primary key,
    version int4 not null
);


CREATE TABLE foo.items_base (
    name varchar(255) not null,
    PRIMARY KEY (id)
) INHERITS (foo.object);


CREATE TABLE foo.items_archived (
    PRIMARY KEY (version, id)
) INHERITS (foo.items_base);

CREATE TABLE foo.items (
    PRIMARY KEY (id)
) INHERITS (foo.items_base);

CREATE OR REPLACE RULE item_update_rule AS ON UPDATE
    TO foo.items
    DO ALSO
        INSERT INTO foo.items_archived 
            SELECT OLD.*
;

CREATE OR REPLACE RULE item_delete_rule AS ON DELETE
    TO foo.items
    DO ALSO
        INSERT INTO foo.items_archived 
            SELECT OLD.*
;


insert into foo.items (id, version, name) values ('d8330f40-56c0-11e1-b86c-0800200c9a66', 3, 'foo');
update only foo.items set version = 4;

-- See that its deleted
select id from foo.items_archived except select id from foo.items;