OK, that makes sense. I guess we will be implementing some triggers.
Thanks,
-Chris
On Tuesday 13 April 2004 5:13 pm, Tom Lane wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > What am I missing?
>
> You're thinking that the rule is a trigger. It's not. It's a query
> transformation mechanism that adds a new query to be executed after your
> INSERT. What actually gets executed is effectively
>
> Original query:
>
> insert into table2 (col1) select col1 from table1 where not exists
> (select 1 from table2 where table2.col1 = table1.col1);
>
> Query added by rule:
>
> insert into table3 (col1)
> select col1 from table1 where not exists
> (select 1 from table2 where table2.col1 = table1.col1);
>
> By the time the second query begins, there *is* a table2 row matching
> every row in table1, because you just got done inserting ones to match
> any that didn't have a match. So in the second query, the EXISTS test
> succeeds at every row of table1 and no rows are produced to insert into
> table3.
>
> This could be made to work if the order of the queries were reversed,
> but that isn't going to happen because it would break other uses of
> ON INSERT rules that need to be able to see the inserted row(s).
> So AFAICS you're gonna have to use a trigger.
>
> regards, tom lane
--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com