Re: deleting from a view

* 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."