Re: rules on INSERT can't UPDATE new instance?

On Sat, May 20, 2000 at 10:41:53AM -0400, Bruce Momjian wrote:
> > But this doesn't work in PG 7.0:
> >
> > auction=> create table test (price float);
> > CREATE
> > auction=> create rule price_control AS ON INSERT TO test WHERE new.price > 100 DO UPDATE test SET price = 100 where
test.oid= new.oid; 
> > CREATE 27913 1
> > auction=> INSERT INTO test VALUES (101);
> > INSERT 27914 1
> > auction=> SELECT test.*;
> >  price
> > -------
> >    101
> > (1 row)
>
> Yes, I see it failing too.  I tried old.oid, and that failed too.
>
> I know there is a recursive problem with rules acting on their own
> table, where if you have an INSERT rule that performs an INSERT on the
> same table, the rules keep firing in a loop.
>
> I thought an INSERT rule with an UPDATE action would work on the same
> table, but that fails.  Seems the rule is firing before the INSERT
> happens.
>
> I am not really sure what to recommend.  The INSERT rule clearly doesn't
> fix cases where someone UPDATE's the row to != 100.  A CHECK constraint
> could be used to force the column to contain 100, but that doesn't
> silently fix non-100 values, which seemed to be your goal.  A trigger
> will allow this kind of action, on INSERT and UPDATE, though they are a
> little more complicated than rules.

Thanks for all your help. You are right: this seems more like the job of
a trigger and I am exploring that topic in depth right now.

Cheers,

--
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr

       Parkinson's Law:  Work expands to fill the time alloted it.