Re: RULES doesn't work as expected

John Hansen wrote:
> Right, except:
>
> create table test (a text, b int); create or replace rule test_rule
> as on insert to test where exists(select 1 from test where a = NEW.a)
> do instead select * from test;
>
> insert into test (a,b) VALUES ('first',2); a   | b -------+--- first
> | 2 (1 row)
>
> select * from test; a   | b -------+--- first | 2 (1 row)
>
> Now, the select on the first insert should NOT have happened.....
> Since this is a do instead rule. The insert should of course happen,
> since it's not present in the table.
>
> Or am I missing the point completely?

You are, but it's not your fault. You're still thinking of it as a
sequence of instructions, try thinking of it as a single expression that
gets evaluated.

To quote from the (v8.0) docs (ch 32 - The Rule System), for your case:
"Qualification given and INSTEAD
     the query tree from the rule action with the rule qualification and
the original query tree's qualification; and the original query tree
with the negated rule qualification added"

So, in your case you get two branches:
1. INSERT ... WHERE NOT EXISTS (...)
2. SELECT * FROM TEST WHERE EXISTS (...)

Is this making sense?
--
   Richard Huxton
   Archonet Ltd