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

> On Sat, May 20, 2000 at 07:35:38AM -0400, Bruce Momjian wrote:
> > >  From the create_rule man page this example is offered:
> > >
> > >   CREATE RULE example_5 AS
> > >            ON INERT TO emp WHERE new.salary > 5000
> > >            DO
> > >             UPDATE NEWSET SET salary = 5000;
> > >
> > > But what is "NEWSET"? Is it a keyword?
> >
> > It should be:
> >
> > CREATE RULE example_5 AS
> >     ON INERT TO emp WHERE new.salary > 5000
> >     DO
> >         UPDATE emp SET salary = 5000
> >         WHERE emp.oid = new.oid;
> >
> > Fixing now.
>
> 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.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026