Re: Rule Question

Luca Ferrari <fluca1978@infinito.it> writes:
> The original post was related to the update of b, so I guess it is
> better to limit the trigger scope to update on such column:

> CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS
>  $mirror$
>  BEGIN
>  NEW.a = NEW.b;
>  RETURN NEW;
>  END;
>  $mirror$ LANGUAGE plpgsql;

> CREATE TRIGGER tr_b_mirror AFTER UPDATE OF b ON cats FOR EACH ROW
> EXECUTE PROCEDURE b_mirror();

> It is worth noting that the trigger could be an after one,

No, it has to be a BEFORE trigger, else it's too late to affect the
row value that gets stored.  Other than that I think this is the
best solution --- there's no reason to make the trigger any more
complicated than this.

BTW, I didn't see anyone pointing out the real reason why a rule isn't
a suitable solution for the OP's problem.  Namely, that a rule is a
macro, so if you have

create rule cats_test as on update to cats do set a = new.b;

the effect of that will be that the *expression* for the new value of b
will be inserted into the rule.  So you'll end up with double evaluation
of that expression, exactly what he wanted to avoid.  A trigger is
handed the fully calculated intended-new-row value, so it doesn't have
this issue.

            regards, tom lane