Re: insert rule not firing on insert with exists subselect

Thanks Tom for your quick response.

At the risk of sounding really stupid, how can this be the case?  The rule is
simply on an insert into table2, insert an additional record in table3.  The
subselect on the actual insert is the one using the exists clause and is
working correctly (as far as inserting the records).  The records are being
inserted into table2 as expected.  But the rule which should have fired for
each insert doesn't happen and table3 is empty.

What am I missing?

-Chris

On Tuesday 13 April 2004 4:40 pm, Tom Lane wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > create rule mycopyrule as on insert to table2 do insert into table3
> > (col1) values (new.col1);
> >
> > insert into table2 (col1) select col1 from table1 where not exists
> > (select 1 from table2 where table2.col1 = table1.col1); -- doesn't work
>
> The rule-generated query executes after the original INSERT, and can see
> its results.  So by the time the rule fires, there *is* an entry in
> table2 matching the table1 value, and of course the EXISTS condition
> fails.
>
> Rules are not triggers and make poor substitutes for them.  You're going
> to need to use a trigger for this...
>
>             regards, tom lane

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com