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 */
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
