Re: RULES doesn't work as expected

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?

... John

> -----Original Message-----
> From: Richard Huxton [mailto:dev(at)archonet(dot)com]
> Sent: Wednesday, January 12, 2005 10:22 PM
> To: John Hansen
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] 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
>
>