"Lim Berger" <straightfwd007@gmail.com> writes:
> create table test (id serial primary key, nowd timestamp without time
> zone, processed_id varchar(10));
> create or replace rule test_ins as on insert to test
> DO UPDATE test
> SET processed_id = MYFUNCTION(NEW.id)
> WHERE id = NEW.id
> ;
I think you are getting burnt by the fact that a rule is a macro
and therefore subject to multiple-evaluation-of-arguments hazards.
In particular, the reference to NEW.id probably results in an extra
evaluation (or two?) of nextval() on the serial sequence.
Even if this worked, it'd be horrendously inefficient, because of having
to apply the full machinery of UPDATE to fix up the row. Instead you
should use a BEFORE INSERT trigger to apply the change to the NEW
record before it ever gets stored.
regards, tom lane
