From: Mark Volpe <volpe.mark@epamail.epa.gov>
To: pgsql-sql@postgresql.org
Subject: Rules aren't doing what I expect
Date: Wed, 09 Aug 2000 12:04:13 -0400
Message-ID: <399180FD.2ACF8B2D@epamail.epa.gov> (raw)
Hi again,
I have a table with a trigger that can potentially modify a row before it gets
inserted or updated:
CREATE TABLE t1 (a int);
CREATE FUNCTION t1_validate() RETURNS opaque AS
'
BEGIN
IF (NEW.a>10) THEN NEW.a=10; END IF;
IF (NEW.a<0) THEN NEW.a=0; END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER t1_trig BEFORE INSERT OR UPDATE
ON t1 FOR EACH ROW EXECUTE PROCEDURE t1_validate();
I have another table that tracks changes in the first table with rules:
CREATE TABLE t1_log (old_a int, new_a int);
CREATE RULE t1_insert AS ON INSERT TO t1
DO INSERT INTO t1_log VALUES(NULL, NEW.a);
CREATE RULE t1_update AS ON UPDATE TO t1
DO INSERT INTO t1_log VALUES(OLD.a, NEW.a);
CREATE RULE t1_delete AS ON DELETE TO t1
DO INSERT INTO t1_log VALUES(OLD.a, NULL);
When I try this out, however, the rule seems to use the original value, rather
than the "corrected" value.
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(8);
INSERT INTO t1 VALUES(15);
SELECT * FROM t1;
a
----
2
8
10
The table t1 shows the corrected value of 10, but,
SELECT * FROM FROM t1_log;
old_a | new_a
-------+-------
| 2
| 8
| 15
The t1_log table doesn't show what was actually inserted into t1!
Are there any changes I can make to the logic above so that t1_log can
show the correct value?
Thanks,
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: volpe.mark@epamail.epa.gov
Subject: Re: Rules aren't doing what I expect
In-Reply-To: <399180FD.2ACF8B2D@epamail.epa.gov>
References: <399180FD.2ACF8B2D@epamail.epa.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox