From: | "Kristian Eide" <kreide(at)online(dot)no> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Rule WHERE condition problem |
Date: | 2002-07-08 17:45:14 |
Message-ID: | 047101c226a7$36fb2da0$6b97f181@speed |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a table with a reference constraint and an ON DELETE SET NULL action.
When this action triggers, I also want to update another field in the table,
actually a timestamp which should be set to NOW().
After reading some documentation it would seem a rule is the easiest way to
accomplish this. However, I ran into a problem with this:
CREATE TABLE a (
id INT PRIMARY KEY
);
CREATE TABLE b (
id INT REFERENCES a ON DELETE SET NULL,
time TIMESTAMP DEFAULT 'infinity'
);
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1);
CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id
IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id;
DELETE FROM a WHERE id=1;
I would now expect a to by empty and b to contain a single row with id=NULL
and time=NOW(). However, this is what I get:
ERROR: query rewritten 10 times, may contain cycles
ERROR: query rewritten 10 times, may contain cycles
It would seem that my WHERE clause is not checked before the action is run.
Is this simply not implemented (yet, hopefully)?
Thanks.
---
Kristian
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-07-08 18:40:39 | Re: |
Previous Message | Richard Huxton | 2002-07-08 16:49:48 | Re: newbie question |