Thread: Delete rules and functions

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?

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

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
REFERENCESsteering_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.