cq3Kmrh4

1copy rows before updating them to preserve archive in Postgres
2CREATE TABLE authors (
3    author_id INTEGER NOT NULL,
4    version INTEGER NOT NULL CHECK (version > 0),
5    author_name TEXT,
6    is_active BOOLEAN DEFAULT '1',
7    modified_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
8    PRIMARY KEY (author_id, version)
9)
10
11INSERT INTO authors (author_id, version, author_name) 
12VALUES  (1, 1, 'John'),
13        (2, 1, 'Jack'),
14        (3, 1, 'Ernest');
15	
16UPDATE authors SET author_name = 'Jack K' WHERE author_id = 1;
17	
182, 1, Jack, t, 2012-03-29 21:35:00
192, 2, Jack K, t, 2012-03-29 21:37:40
20	
21SELECT author_name, modified_on 
22FROM authors
23WHERE 
24author_id = 2 AND 
25modified_on < '2012-03-29 21:37:00' 
26ORDER BY version DESC 
27LIMIT 1;
28	
292, 1, Jack, t, 2012-03-29 21:35:00
30	
31CREATE OR REPLACE FUNCTION archive_authors() RETURNS TRIGGER AS $archive_author$
32    BEGIN
33        IF (TG_OP = 'UPDATE') THEN
34
35            -- The following fails because author_id,version PK already exists
36            INSERT INTO authors (author_id, version, author_name)
37            VALUES (OLD.author_id, OLD.version, OLD.author_name);
38
39            UPDATE authors 
40            SET version = OLD.version + 1
41            WHERE 
42                author_id = OLD.author_id AND 
43                version = OLD.version;
44            RETURN NEW;
45        END IF;
46        RETURN NULL; -- result is ignored since this is an AFTER trigger
47    END;
48$archive_author$ LANGUAGE plpgsql;
49
50CREATE TRIGGER archive_author
51AFTER UPDATE OR DELETE ON authors
52    FOR EACH ROW EXECUTE PROCEDURE archive_authors();
53	
54insert into authors (author_id, version, author_name)
55select 
56    1, 
57    (
58        select max(version) + 1
59        from authors
60        where author_id = 1
61    ),
62    'Jack K'