pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete rules and functions
3+ messages / 2 participants
[nested] [flat]
* Delete rules and functions
@ 2007-06-26 19:32 Wiebe Cazemier <halfgaar@gmx.net>
2007-06-26 20:50 ` Re: Delete rules and functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Wiebe Cazemier @ 2007-06-26 19:32 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have the following scenerio:
a rule on a view which executes a function by means of a select call, which in
turn deletes from a table which has on-delete rules on it. When the function
is called from the rule, the subsequent delete call in the function doesn't
cause the on-delete rules on the table to be taken into account.
Is that a bug? Or is the query rewriting not possible from inside functions?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete rules and functions
2007-06-26 19:32 Delete rules and functions Wiebe Cazemier <halfgaar@gmx.net>
@ 2007-06-26 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-26 21:37 ` Re: Delete rules and functions Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-06-26 20:50 UTC (permalink / raw)
To: Wiebe Cazemier <halfgaar@gmx.net>; +Cc: pgsql-sql
Wiebe Cazemier <halfgaar@gmx.net> writes:
> I have the following scenerio:
> a rule on a view which executes a function by means of a select call, which in
> turn deletes from a table which has on-delete rules on it. When the function
> is called from the rule, the subsequent delete call in the function doesn't
> cause the on-delete rules on the table to be taken into account.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete rules and functions
2007-06-26 19:32 Delete rules and functions Wiebe Cazemier <halfgaar@gmx.net>
2007-06-26 20:50 ` Re: Delete rules and functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-26 21:37 ` Wiebe Cazemier <halfgaar@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Wiebe Cazemier @ 2007-06-26 21:37 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-06-26 21:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-26 19:32 Delete rules and functions Wiebe Cazemier <halfgaar@gmx.net>
2007-06-26 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-26 21:37 ` Wiebe Cazemier <halfgaar@gmx.net>
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