From: Wiebe Cazemier <halfgaar@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Delete rules and functions
Date: Tue, 26 Jun 2007 23:37:11 +0200
Message-ID: <f5s0u7$kiv$1@sea.gmane.org> (raw)
References: <f5rpk6$r8n$1@sea.gmane.org>
<28442.1182891012@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: halfgaar@gmx.net
Subject: Re: Delete rules and functions
In-Reply-To: <f5s0u7$kiv$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox