[SQL] Rules aren't doing what I expect

[Mark Volpe]

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