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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026