track revisions in postgresql

Question

I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter.

CREATE TABLE A (
id SERIAL,
foo TEXT,
PRIMARY KEY (id));

CREATE TABLE B (
revision INTEGER NOT NULL) INHERITS (A);

Then I created a trigger that would update table B everytime A is inserted/updated. What I can't figure out is how to make B.revision keep an individual "sequence" for each id.

ie: table A has 2 rows, (i & j) i has been updated 3 times and should have 3 revisions (1, 2, 3). j has been updated 2 times and should have two revisions (1, 2).

Here is what I have so far, maybe I'm going down the wrong path and someone can help me!

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
    DECLARE
        last_revision INTEGER;
    BEGIN
        SELECT INTO last_revision MAX(revision) FROM B WHERE id = NEW.id;

        IF NOT FOUND THEN
            last_revision := 0;
        END IF;

        INSERT INTO B SELECT NEW.*;

        RETURN NEW;
    END;
$table_update$ LANGUAGE plpgsql;

CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
    FOR EACH ROW EXECUTE PROCEDURE table_update();

Answer 1

Here's my suggestion:

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
DECLARE
    last_revision INTEGER;
BEGIN
    SELECT INTO last_revision coalesce(MAX(revision), 0) FROM B WHERE id = NEW.id;

    INSERT INTO B SELECT NEW.*, last_revision + 1;

    RETURN NEW;
END;
$table_update$ LANGUAGE plpgsql;

I changed the "if not found" into a coalesce, that will pick the "0" if there is no existing revision. Then, I insert in B the row, with the incremented revision.

Be careful with your inheritance: you will need to use the "only" keyword to limit yourself to the A table when selecting and updating, as such:

select * from only A
update only A set foo = ... where id = ...

Answer 2

If you need the version numbers just for ordering, and don't specifically need them to be an integer that increase by one for each identifier, the easiest way to do it is to use a sequence for the revision and just let it do the tracking for you:

CREATE TABLE A (
    id SERIAL,
    foo TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE B ( revision SERIAL NOT NULL) INHERITS (A);

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
    BEGIN
        INSERT INTO B SELECT NEW.*;
        RETURN NEW;
    END;
$table_update$ LANGUAGE plpgsql;

CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
    FOR EACH ROW EXECUTE PROCEDURE table_update();

Then do the inserts as usual:

    try=# insert into a (foo) values ('bar');
    INSERT 0 1
    try=# insert into a (foo) values ('bar');
    INSERT 0 1
    try=# update a set foo = 'you' where id = 1;
    UPDATE 2
    try=# select * from b;
     id | foo | revision 
    ----+-----+----------
      2 | bar |        2
      1 | you |        1
      1 | you |        3
    (3 rows)

So you can get all revisions for a given row like so:

    try=# select * from b where id = 1 order by revision;
     id | foo | revision 
    ----+-----+----------
      1 | you |        1
      1 | you |        3
    (2 rows)