Re: BUG #7662: INSERT rule doesn't work as expected

mtesfaye(at)gmail(dot)com writes:
> ON INSERT RULE doesn't always work as expected.
> The reasoning for this particular rule was to add the new value to an
> existing value if it is determined that the key already exists.

> CREATE OR REPLACE RULE "dup_add_test_r_rule" AS
> ON INSERT TO test_r
> WHERE
> EXISTS(SELECT 1 FROM test_r a WHERE a.id=NEW.id)
> DO INSTEAD
> (UPDATE test_r a SET val=a.val+NEW.val WHERE a.id=NEW.id);

Per the manual:

For ON INSERT rules, the original query (if not suppressed by INSTEAD)
is done before any actions added by rules. This allows the actions to
see the inserted row(s).

So the behavior in your example is

(1) The EXISTS test fails, so the INSERT is allowed to execute.

(2) Now the EXISTS test passes, so the UPDATE is allowed to execute.

This might not be what you wished would happen, but it's not a bug;
it's the way rules are defined to work. You might have better luck
with a BEFORE INSERT trigger.

regards, tom lane