Re: Problems with rules

Hello

> Long Description
> The given code is destilled from a reallife DB. All attempts to define a rule which automatically updates the column
aenderungsdatumfrom table mitglieder on updates failed. The result was every 
> time

> psql:ruleerror:17: ERROR:  query rewritten 10 times, may contain cycles
This error raises because your rule executed recursively. (You are
using UPDATE statment in rule for UPDATE).

> Sample Code
> DROP TABLE "mitglieder";
> CREATE TABLE "mitglieder" (
>         "mitid" int4 PRIMARY KEY,
>         "name" character varying(100) NOT NULL,
>         "vorname" character varying(100),
>         "aenderungsdatum" date DEFAULT 'now'
                                        ^^^^^^^^^^^
Suggestion: If you want date of insertion to be written here use
CURRENT_DATE insterad of 'now'
> );

> INSERT INTO "mitglieder" (mitid, name, vorname)
>          VALUES (42,'Surname','firstname');

> DROP RULE "test";
> CREATE RULE "test" AS
>   ON UPDATE TO "mitglieder" WHERE OLD.name <> NEW.name
>   DO UPDATE "mitglieder" SET "aenderungsdatum" = 'now' ;
Suggestion: If you want date of insertion to be written here use
CURRENT_DATE insterad of 'now'

> UPDATE "mitglieder" SET name='Nachname' where mitid=42;

I had the same problem some months ago. Tom Lane advised me to use
triggers instead of rules for this case. It's faster and more
convinient.

But if you want to use RULES you have to create table t_mitglieder
and view mitglieder as select * from t_mitglieder,
and you have to create rules for insert, update and delete from
mitglieder that will do appropriate operations on t_mitglieder.


Gena