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
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 " |