Thread

1. update rule loops

   Poul L. Christiansen <plc@faroenet.fo> — 2000-08-18T15:01:36Z

   Hi
   
   I'm trying to make a field in my table (datechanged) to automatically be
   updated with the value 'now()' when an update on the table occurs.
   
   plc=# create rule datechanged_radius AS ON update to radius do update
   radius set datechanged ='now()';
   CREATE 22025360 1
   plc=# update radius set destinationip = '212.055.059.001';
   ERROR:  query rewritten 10 times, may contain cycles
   
   This means that it's going in a loop, because the rule triggers itself.
   
   Is there another way to do this?
   
   Thanks,
   Poul L. Christiansen
   
   
   

2. Re: update rule loops

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-18T15:21:45Z

   On Fri, 18 Aug 2000, Poul L. Christiansen wrote:
   
   > Hi
   > 
   > I'm trying to make a field in my table (datechanged) to automatically be
   > updated with the value 'now()' when an update on the table occurs.
   > 
   > plc=# create rule datechanged_radius AS ON update to radius do update
   > radius set datechanged ='now()';
   > CREATE 22025360 1
   > plc=# update radius set destinationip = '212.055.059.001';
   > ERROR:  query rewritten 10 times, may contain cycles
   > 
   > This means that it's going in a loop, because the rule triggers itself.
   > 
   > Is there another way to do this?
   
   Two ways I can think of are either have a "view" where you do the work
   on the view, but the underlying table is named something else, which
   means you actually need to do an instead rule that does the update on
   that table and the setting of datechanged. (Not 100% sure of this, 
   but should work).
   
   Second is use triggers.  Write a pl/pgsql before update trigger.
   Assigning to NEW.datechanged should work I believe.