Re: Rule Question

Luca Ferrari <fluca1978(at)infinito(dot)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