I am experimenting with creating a table that keeps a version of every row. The idea is to be able to query for how the rows were at any point in time even if the query has JOINs. Consider a system where the primary resource is books, that is, books are queried for, and author info comes along for the ride
CREATE TABLE authors (
author_id INTEGER NOT NULL,
version INTEGER NOT NULL CHECK (version > 0),
author_name TEXT,
is_active BOOLEAN DEFAULT '1',
modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (author_id, version)
)
INSERT INTO authors (author_id, version, author_name)
VALUES (1, 1, 'John'),
(2, 1, 'Jack'),
(3, 1, 'Ernest');
I would like to be able to update the above like so
UPDATE authors SET author_name = 'Jack K' WHERE author_id = 1;
and end up with
2, 1, Jack, t, 2012-03-29 21:35:00
2, 2, Jack K, t, 2012-03-29 21:37:40
which I can then query with
SELECT author_name, modified_on
FROM authors
WHERE
author_id = 2 AND
modified_on < '2012-03-29 21:37:00'
ORDER BY version DESC
LIMIT 1;
to get
2, 1, Jack, t, 2012-03-29 21:35:00
Something like the following doesn't really work
CREATE OR REPLACE FUNCTION archive_authors() RETURNS TRIGGER AS $archive_author$
BEGIN
IF (TG_OP = 'UPDATE') THEN
-- The following fails because author_id,version PK already exists
INSERT INTO authors (author_id, version, author_name)
VALUES (OLD.author_id, OLD.version, OLD.author_name);
UPDATE authors
SET version = OLD.version + 1
WHERE
author_id = OLD.author_id AND
version = OLD.version;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$archive_author$ LANGUAGE plpgsql;
CREATE TRIGGER archive_author
AFTER UPDATE OR DELETE ON authors
FOR EACH ROW EXECUTE PROCEDURE archive_authors();
How can I achieve the above? Or, is there a better way to accomplish this? Ideally, I would prefer to not create a shadow table to store the archived rows.
