Rules aren't doing what I expect
От | Mark Volpe |
---|---|
Тема | Rules aren't doing what I expect |
Дата | |
Msg-id | 399180FD.2ACF8B2D@epamail.epa.gov обсуждение исходный текст |
Ответы |
Re: Rules aren't doing what I expect
Re: Rules aren't doing what I expect |
Список | 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 INSERTOR 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_deleteAS 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
В списке pgsql-sql по дате отправления: