Postgres Pro
Downloads
Home   >   mailing lists

Thread: Update rule

Update rule

From
Secrétariat
Date:
Hello !
 
I wish to create a rule to write in records who and when modified them.
I wrote :
CREATE OR REPLACE RULE tarif_upd AS
   ON UPDATE TO tarifs
   DO ALSO
      UPDATE tarifs
            SET dmodtar = current_date, umodtar = current_user
      WHERE dmodtar <> current_date AND umodtar <> current_user ;
But I obtain an infinite recursion !
It seems not using the where clause ?
Is it a bug ?
How can I workaround ...
 
Best regards.
Luc

Re: Update rule

From
Pierre-Frédéric Caillaud
Date:
    You get infinite recursion because your ON UPDATE rule does another
UPDATE which of course calls the rule ; so no, it's not a bug ; also your
UPDATE updates almost the whole table so it won't do what you had in mind
in the first place. You should rather change the NEW row in your update so
that NEW.dmodtar = current_date and NEW.umodtar = current_user. Which
means you'll rather be using a trigger for this. Read the docs on CREATE
TRIGGER and see the examples, I think there's one which looks like what
you want.


> Hello !
>
> I wish to create a rule to write in records who and when modified them.
> I wrote :
> CREATE OR REPLACE RULE tarif_upd AS
>    ON UPDATE TO tarifs
>    DO ALSO
>       UPDATE tarifs
>             SET dmodtar = current_date, umodtar = current_user
>       WHERE dmodtar <> current_date AND umodtar <> current_user ;
> But I obtain an infinite recursion !
> It seems not using the where clause ?
> Is it a bug ?
> How can I workaround ...
>
> Best regards.
> Luc

Re: Update rule

From
Secrétariat
Date:
Many thanks for your explanation.
I found in the contrib the function moddatetime
CREATE TRIGGER dmodtar_upd
   BEFORE UPDATE ON tarifs
   FOR EACH ROW
   EXECUTE PROCEDURE public.moddatetime(dmodtar) ;
So all works fine !
PostgreSQL 8 is great.

----- Original Message -----
From: "Pierre-Frédéric Caillaud" <lists@boutiquenumerique.com>
To: "Secrétariat" <ets@rolland-fr.com>; <pgsql-general@postgresql.org>
Sent: Thursday, December 30, 2004 7:08 PM
Subject: Re: [GENERAL] Update rule


>
> You get infinite recursion because your ON UPDATE rule does another
> UPDATE which of course calls the rule ; so no, it's not a bug ; also your
> UPDATE updates almost the whole table so it won't do what you had in mind
> in the first place. You should rather change the NEW row in your update so
> that NEW.dmodtar = current_date and NEW.umodtar = current_user. Which
> means you'll rather be using a trigger for this. Read the docs on CREATE
> TRIGGER and see the examples, I think there's one which looks like what
> you want.
>
>
>> Hello !
>>
>> I wish to create a rule to write in records who and when modified them.
>> I wrote :
>> CREATE OR REPLACE RULE tarif_upd AS
>>    ON UPDATE TO tarifs
>>    DO ALSO
>>       UPDATE tarifs
>>             SET dmodtar = current_date, umodtar = current_user
>>       WHERE dmodtar <> current_date AND umodtar <> current_user ;
>> But I obtain an infinite recursion !
>> It seems not using the where clause ?
>> Is it a bug ?
>> How can I workaround ...
>>
>> Best regards.
>> Luc
>
>
>