Update rule
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
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.
Show quoted text
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
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
Show quoted text
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