Re: rules on INSERT can't UPDATE new instance?
От | Louis-David Mitterrand |
---|---|
Тема | Re: rules on INSERT can't UPDATE new instance? |
Дата | |
Msg-id | 20000520180634.A638@styx обсуждение исходный текст |
Ответ на | Re: rules on INSERT can't UPDATE new instance? (Bruce Momjian <pgman@candle.pha.pa.us>) |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: