On Tuesday 26 June 2007 22:50, Tom Lane wrote: > Please provide an example, because the rewriter is most certainly > applied to queries from functions. > > I suspect you are actually being burnt by some other effect, like a row > disappearing from the view as soon as its underlying data is deleted.
Here is an example (and it's nothing more than an example...): -------------- CREATE TABLE cars ( id SERIAL PRIMARY KEY ); -------------- CREATE TABLE car_parts ( id SERIAL PRIMARY KEY, car_id INTEGER NOT NULL REFERENCES cars ON DELETE CASCADE, steering_wheel_id INTEGER NOT NULL REFERENCES steering_wheels ); -------------- CREATE RULE AS ON DELETE TO car_parts DO ALSO ( DELETE FROM steering_wheels WHERE id = OLD.steering_wheel_id; ); -------------- CREATE VIEW cars_view AS SELECT * FROM cars; -------------- CREATE FUNCTION cars_delete(p_old) RETURNS VOID AS $$ BEGIN DELETE FROM cars where id = p_old.id; END; $$ LANGUAGE plpgsql; -------------- CREATE RULE AS ON DELETE TO cars_view DO INSTEAD ( SELECT cars_delete(); ); -------------- Now, when I delete a row from the cars_view, the underlying record from car is deleted, which cascades to car_parts. The rule intended for removing the steering wheel doesn't do anything. And now that I wrote it, I can see that it's indeed because OLD no longer exists. I knew this was the case for rules, but I overlooked it, apparently... I had already converted it to use triggers, and I'll leave it that way. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
