Rules aren't doing what I expect

From: Mark Volpe <volpe(dot)mark(at)epamail(dot)epa(dot)gov>
To: pgsql-sql(at)postgresql(dot)org
Subject: Rules aren't doing what I expect
Date: 2000-08-09 16:04:13
Message-ID: 399180FD.2ACF8B2D@epamail.epa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Ang Chin Han 2000-08-10 02:18:20 Re: Rules aren't doing what I expect
Previous Message luc00 2000-08-09 13:31:59 any tool "return query results to a grid "