Re: Problem with a sequence being acted on by an on insert rule.

Mark Le Huray <mark(dot)lehuray(at)dsl(dot)pipex(dot)com> writes:
> - create sequence autonumber increment 1 minvalue 0 start 0;
> - create table testtable1 ( pk int primary key );
> - create table testtable2 ( fk int primary key references
> testtable1(pk));
> - create rule updatetesttable2 as on insert to testtable1 do (insert
> into testable2 (fk) values( new.pk ));
> - insert into testtable1 values ( (select nextval('autonumber')));

This does not work very well, as you've discovered, because a rule is
really a macro. As such, it has all the usual issues with multiple
evaluations of arguments.

I'd recommend a trigger, not a rule, for propagating information from
one table to another. Although the notational cruft is messier, the
trigger will likely be faster as well as impervious to this type of
problem.

regards, tom lane