Re: Implementing a change log - Mailing list pgsql-general

Berend Tober wrote:

> ...See "User Comments" at
>
> "http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html"
>
>
> for something that should set you afire.

And, commenting on my own post, try this cool function:

/*
The following is based on suggestion by Mike Rylander posted on
Postgresql-General
Sun, 18 Sep 2005 23:29:51 +0000

Rylander's original suggestion employed a trigger and tracked
only row updates. My implementation makes use of rules and
handles both updates and deletions.
*/

\o output.txt
\set ON_ERROR_STOP OFF

DROP SCHEMA auditor CASCADE;
DROP SCHEMA test CASCADE;

\set ON_ERROR_STOP ON

-- Create a schema to contain all of our audit tables and the creator
function
CREATE SCHEMA auditor;

CREATE OR REPLACE FUNCTION auditor.create_auditor(name, name)
  RETURNS bool AS
'
BEGIN
    -- This is the function that does the heavy lifting of creating
audit tables
    -- and the triggers that will populate them.

    -- Create the audit table: auditor.{schema}_{table}
    EXECUTE \'
        CREATE TABLE auditor.\' || $1 || \'_\' || $2 || \' (
            update_action VARCHAR(6) NOT NULL,
            update_date TIMESTAMP NOT NULL DEFAULT NOW(),
            update_user NAME NOT NULL DEFAULT CURRENT_USER
        ) INHERITS (\' || $1 || \'.\' || $2 || \') WITHOUT OIDS;
    \';

    EXECUTE \'
        CREATE RULE \'|| $2 ||\'_ru AS ON UPDATE TO \'|| $1 ||\'.\'|| $2
||\'
        DO INSERT INTO auditor.\'|| $1 ||\'_\'|| $2 ||\'
        SELECT OLD.*, \'\'UPDATE\'\';
    \';

    EXECUTE \'
        CREATE RULE \'|| $2 ||\'_rd AS ON DELETE TO \'|| $1 ||\'.\'|| $2
||\'
        DO INSERT INTO auditor.\'|| $1 ||\'_\'|| $2 ||\'
        SELECT OLD.*, \'\'DELETE\'\';
    \';

    RETURN TRUE;
END;
'
  LANGUAGE 'plpgsql' VOLATILE;



/* BEGIN EXAMPLE */

CREATE SCHEMA test AUTHORIZATION postgres;

-- This option makes it unnecessary to use the "ONLY" keyword in your
SELECT and UPDATE statements.
\set SQL_INHERITANCE TO OFF;

\set search_path = test, pg_catalog;
\set default_with_oids = false;

CREATE TABLE test.person (
    first_name character varying(24),
    last_name character varying(24),
    gender character(1),
    marital_status character(1)
) WITHOUT OIDS;

INSERT INTO test.person VALUES ('Charlie', 'Bucket', 'M', 'S');
INSERT INTO test.person VALUES ('Grandpa', 'Joe', 'M', NULL);
INSERT INTO test.person VALUES ('Veruca', 'Salt', NULL, 'S');
INSERT INTO test.person VALUES ('Augustus', 'Gloop', 'M', 'S');
INSERT INTO test.person VALUES ('Micheal', 'Teevee', 'M', 'S');
INSERT INTO test.person VALUES ('Violet', 'Beaureguard', 'M', 'S');

SELECT auditor.create_auditor('test', 'person');

UPDATE test.person set marital_status = 'M' WHERE last_name = 'Joe';
SELECT * FROM auditor.test_person;
/*
 first_name | last_name | gender | marital_status | update_action
|        update_date         | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
 Grandpa    | Joe       | M      |                | UPDATE        |
2005-09-20 03:26:23.063965 | postgres
(1 row)
*/

UPDATE test.person set first_name = 'Joe', last_name = 'Bucket' WHERE
last_name = 'Joe';
SELECT * FROM auditor.test_person;
/*
 first_name | last_name | gender | marital_status | update_action
|        update_date         | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
 Grandpa    | Joe       | M      |                | UPDATE        |
2005-09-20 03:26:23.063965 | postgres
 Grandpa    | Joe       | M      | M              | UPDATE        |
2005-09-20 03:26:23.13654  | postgres
(2 rows)
*/

UPDATE test.person set gender = 'F' WHERE last_name = 'Salt';
SELECT * FROM auditor.test_person;
/*
 first_name | last_name | gender | marital_status | update_action
|        update_date         | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
 Grandpa    | Joe       | M      |                | UPDATE        |
2005-09-20 03:26:23.063965 | postgres
 Grandpa    | Joe       | M      | M              | UPDATE        |
2005-09-20 03:26:23.13654  | postgres
 Veruca     | Salt      |        | S              | UPDATE        |
2005-09-20 03:26:23.175714 | postgres
(3 rows)
*/

DELETE FROM test.person WHERE last_name = 'Salt';
SELECT * FROM auditor.test_person;
/*
 first_name | last_name | gender | marital_status | update_action
|        update_date         | update_user
------------+-----------+--------+----------------+---------------+----------------------------+-------------
 Grandpa    | Joe       | M      |                | UPDATE        |
2005-09-20 03:26:23.063965 | postgres
 Grandpa    | Joe       | M      | M              | UPDATE        |
2005-09-20 03:26:23.13654  | postgres
 Veruca     | Salt      |        | S              | UPDATE        |
2005-09-20 03:26:23.175714 | postgres
 Veruca     | Salt      | F      | S              | DELETE        |
2005-09-20 03:26:23.201887 | postgres
(4 rows)
*/

/* END EXAMPLE */