Rule (which multiple actions) problem with history table.....

Hi,

Using Postgres 6.5.3 on Linux I'm trying to Create a history table that
will keep a copy of all data that was in the main table as it's changed.

So to do this I've set up some rules to do it ... but there seems to be a
problem.

So first off I have 2 tables ... one is the one that will store the
"history" data. Both tables have the same colums and types. (The full
script is below).

I then have a view set up on the main table and a rule that catches all
updates and then copies the record to the history table before modifying
the main table. At least it should.

The problem is with the rule on update.

CREATE RULE customer_update AS ON UPDATE TO customer
DO INSTEAD (
INSERT INTO customer_history(customer_id, company, added, modified_by,
last_modified, suspended, suspended_date, deleted,
deleted_date)
VALUES (old.customer_id, old.company, old.added, old.modified_by,
old.last_modified, old.suspended, old.suspended_date,
old.deleted, old.deleted_date);
UPDATE customer_table
SET company = new.company,
last_modified = current_datetime(), modified_by = user,
suspended = new.suspended, suspended_date = new.suspended_date,
deleted = new.deleted, deleted_date = new.deleted_date
WHERE customer_id = old.customer_id;
);

This fails with the error...
ERROR: INSERT has more expressions than target columns"

Yet if I modify the rule and remove the UPDATE section it works fine.

I then tried switching the order of the INSERT and UPDATE section in the
rule and the rule was accepted fine. I assumed that the 'old.xxxxx' would
refer to the unmodified value for the whole rule, but as soon as the
update it executred it appears that the 'old.xxxxx' takes on the updated
values, meaning what's inserted into the history table is the new values
as opposed to the old ones.

It's proably somthing obvious but U can't see it so am hoping someone else
can help. :)

Many thanks in advance.

=================== Script ===============================

--
-- Sequence for customer_id in customer_table
--
CREATE SEQUENCE customer_seq START 1;

--
-- Customers table
--
CREATE TABLE customer_table(
customer_id INT4 NOT NULL PRIMARY KEY,
company VARCHAR(128),
added DATETIME,
modified_by VARCHAR(20),
last_modified DATETIME,
suspended BOOL DEFAULT FALSE,
suspended_date DATE,
deleted BOOL DEFAULT FALSE,
deleted_date DATE);

--
-- Customers history table
--
CREATE TABLE customer_history(
customer_id INT4 NOT NULL,
company VARCHAR(128),
added DATETIME,
modified_by VARCHAR(20),
last_modified DATETIME,
suspended BOOL DEFAULT FALSE,
suspended_date DATE,
deleted BOOL DEFAULT FALSE,
deleted_date DATE);

--
-- Users view to allow RULES to work
--
CREATE VIEW customer AS SELECT * FROM customer_table;

--
-- Rule to INSERT & add correct date
--
CREATE RULE customer_insert AS ON INSERT TO customer
DO INSTEAD INSERT INTO customer_table
VALUES (NEXTVAL('customer_seq'), new.company, current_datetime(), user,
current_datetime(), new.suspended, new.suspended_date,
new.deleted, new.deleted_date);

--
-- Rule to uppercase UPDATE & add correct modified date
--
CREATE RULE customer_update AS ON UPDATE TO customer
DO INSTEAD (
INSERT INTO customer_history(customer_id, company, added, modified_by,
last_modified, suspended, suspended_date, deleted,
deleted_date)
VALUES (old.customer_id, old.company, old.added, old.modified_by,
old.last_modified, old.suspended, old.suspended_date,
old.deleted, old.deleted_date);
UPDATE customer_table
SET company = new.company,
last_modified = current_datetime(), modified_by = user,
suspended = new.suspended, suspended_date = new.suspended_date,
deleted = new.deleted, deleted_date = new.deleted_date
WHERE customer_id = old.customer_id;
);

===========================================

Regards,

---[ Neil Burrows ]-----------------------------------------------------
E-mail: neil(at)pawprint(dot)co(dot)uk | Don't be humble ...
Web : http://www.remo.demon.co.uk/ | you're not that great
-----------< PGP Key available from http://www.zoit.net/pgp/ >------------