Thread: deleting from a view

Before I get flamed for not RTFM I do know I can use a rule to do this.

Here's the problem:

I have a view that is made up of two tables.

When I delete from the view I would like the records to be deleted from
both tables that make up my view.  What is the best way to accomplish
this?

Should I use a trigger or a rule?  If I use a trigger how should it be
written.  If I use a rule how do I put two delete statements in it.

I'd like to be able to do something along the lines of:

CREATE RULE view_raw_reman_prod_delete3 AS
ON DELETE TO view_raw_reman_prod
DO INSTEAD
'BEGIN
    DELETE FROM reman_costraw1 WHERE prod_date=old.prod_date;
    DELETE FROM reman_cost_total_raw1 WHERE prod_date=old.prod_date;
END;';

This doesn't work :)

I've tried splitting things up and using two rules however the second rule
always fails.  I suspect that's becuase old.prod_date is set to null.

Any tips or advice would be greatly aprecated.

Shawn

* shawn everett <everett@pgweb.com> [001017 16:08] wrote:
> Before I get flamed for not RTFM I do know I can use a rule to do this.
>
> Here's the problem:
>
> I have a view that is made up of two tables.
>
> When I delete from the view I would like the records to be deleted from
> both tables that make up my view.  What is the best way to accomplish
> this?
>
> Should I use a trigger or a rule?  If I use a trigger how should it be
> written.  If I use a rule how do I put two delete statements in it.
>
> I'd like to be able to do something along the lines of:
>
> CREATE RULE view_raw_reman_prod_delete3 AS
> ON DELETE TO view_raw_reman_prod
> DO INSTEAD
> 'BEGIN
>     DELETE FROM reman_costraw1 WHERE prod_date=old.prod_date;
>     DELETE FROM reman_cost_total_raw1 WHERE prod_date=old.prod_date;
> END;';
>
> This doesn't work :)
>
> I've tried splitting things up and using two rules however the second rule
> always fails.  I suspect that's becuase old.prod_date is set to null.
>
> Any tips or advice would be greatly aprecated.

Blind guess, define a SQL or plpgsql function, call it from your rule.

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

On Tue, 17 Oct 2000, shawn everett wrote:

> Before I get flamed for not RTFM I do know I can use a rule to do this.
>
> Here's the problem:
>
> I have a view that is made up of two tables.
>
> When I delete from the view I would like the records to be deleted from
> both tables that make up my view.  What is the best way to accomplish
> this?
>
> Should I use a trigger or a rule?  If I use a trigger how should it be
> written.  If I use a rule how do I put two delete statements in it.
>
> I'd like to be able to do something along the lines of:
>
> CREATE RULE view_raw_reman_prod_delete3 AS
> ON DELETE TO view_raw_reman_prod
> DO INSTEAD
> 'BEGIN
>     DELETE FROM reman_costraw1 WHERE prod_date=old.prod_date;
>     DELETE FROM reman_cost_total_raw1 WHERE prod_date=old.prod_date;
> END;';
>
> This doesn't work :)

IIRC, the correct syntax for multiple actions is
CREATE RULE ...
DO INSTEAD (
 <action>;
 <action>;
);

(My test set was three tables a b and c, each having a column named a
which was an int, and my rule was
 create rule rule as on delete to a do instead (
 delete from b where a=OLD.a; delete from c where a=OLD.a);
which appeared to work in my minimal test (delete a row from
a and see what happens)

shawn everett <everett@pgweb.com> writes:
> Should I use a trigger or a rule?  If I use a trigger how should it be
> written.  If I use a rule how do I put two delete statements in it.

You can't use a trigger, because a trigger is fired at the point where
a physical tuple is about to be inserted/deleted/updated.  The view has
no physical tuples, therefore nothing to fire a trigger on.

The problem with the rule, as you guessed, is that once you delete from
the first table there are no longer any matching tuples in the view
(which is what OLD is referring to), so second delete doesn't find any
matches.  Perhaps Jan Wieck can think of a solution ... I'm not sure
how to do it.

            regards, tom lane