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

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

From
maillist@remo.demon.co.uk
Date:
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@pawprint.co.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/ >------------


Re: [SQL] Rule (which multiple actions) problem with history table.....

From
Tom Lane
Date:
maillist@remo.demon.co.uk writes:
> The problem is with the rule on update.
> This fails with the error... 
> ERROR:  INSERT has more expressions than target columns

FWIW, this seems to work fine in current sources (7.0beta1).  It's
difficult to muster much enthusiasm for figuring out why it was
broken in 6.5...
        regards, tom lane


[SQL] 7.0beta1?

From
Christopher Sawtell
Date:
On Mon, 28 Feb 2000, you wrote:

> FWIW, this seems to work fine in current sources (7.0beta1). 

Is there an even vaguely expected date for the public release of 7.0?

--
Sincerely etc.,
NAME       Christopher Sawtell - Support Engineer - iOpen Technologies Ltd.CELL PHONE 021 257 4451ICQ UIN
45863470EMAIL     chris @ iopen . co . nz,  csawtell @ xtra . co . nzCNOTES
ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
---->>> Please refrain from using HTML attachments in e-mails to me. <<<----



Re: [SQL] 7.0beta1?

From
Bruce Momjian
Date:
> On Mon, 28 Feb 2000, you wrote:
> 
> > FWIW, this seems to work fine in current sources (7.0beta1). 
> 
> Is there an even vaguely expected date for the public release of 7.0?
> 

Usually about 1-1.5 months after start of beta, which would put 7.0
release around April 1-15.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026