Re: Implementing a change log

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 */