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.