Re: RULES doesn't work as expected

Removed cc to pgsql-patches since that's not the list for this.

John Hansen wrote:
> It seems rules don't work as expected.
> I could be wrong,... In which case, what am I doing wrong?

A rule is like a macro, rewriting the query plan. You're trying to use
it as though it is a trigger. The side-effects of rules can be quite
subtle and catches most of us out at least once.

> Clearly, the first insert below should not update the table as well.

> CREATE TABLE test (a text, b int4[]);
>
> CREATE RULE test_rule AS
>   ON INSERT TO test
>   WHERE exists(SELECT 1 FROM test WHERE a = NEW.a)
>   DO INSTEAD
>     UPDATE test SET b = b + NEW.b WHERE a = NEW.a;
>
>
> db1=# INSERT INTO test (a,b) VALUES (1,'{1}'::int4[]);

The NEW.a doesn't refer to a variable as such, it refers to the
updated/inserted value of an actual row in "test". Does that clarify?

In your particular usage you'd want to consider concurrency and locking
issues too.

Repost your question on the general/sql lists if you'd like some
discussion. It's probably worth checking the list archives too - plenty
in there about rule/trigger differences.
--
   Richard Huxton
   Archonet Ltd