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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
