Re: Rule - Delete or Upgrade will not work with primary key

have you tried this under v7.0.3 and/or 7.1, to see if its long since been
fixed?

On Sun, 14 Jan 2001, miss wrote:

> Hello there
>
> I am using Postgres 6.5.3 on SuSE Linux 6.4
>
> The problem is that on using rules on a view it will only work for
> insert and delete - not update.  Even for insert and delete to work,
> read and write permission must be given the user on the parent file.
> This does somewhat negate the purpose of using the view in the first
> place.  It is my suspicion that this is due to the primary index on the
> first three fields.
>
> CREATE TABLE plan (
> date date,
> period int2 DEFAULT 0,
> artisan int2 DEFAULT 0,
> jobnum int4,
> available char(3),
> PRIMARY KEY (date,period,artisan));
>
> CREATE VIEW view_plan
> AS SELECT * FROM plan;
>
> CREATE RULE view_plan_insert AS
> ON INSERT TO view_plan
> DO INSTEAD
>   INSERT INTO plan
>   VALUES (
>     date = new.date,
>     period = new.period,
>     artisan = new.artisan,
>     jobnum = new.artisan,
>     available = new.available,);
>
> CREATE RULE view_plan_delete AS
> ON DELETE TO view_plan
> DO INSTEAD
>   DELETE FROM plan
>   WHERE date = old.date
>   AND period = old.period
>   AND artisan = old.artisan;
>
> Similarly for UPDATE
>
> As already stated, INSERT and DELETE works OK if permission ALL is given
> the user for the file "plan", but UPDATE will not. Again I make the
> point of giving such permissions on the parent file negates the purpose
> of using a view.  Is this outcome caused by the PRIMARY KEY.
>
>
> On spending further time on it I find that any primary index will give
> the same result.
>
> Yes, I have given the user permissions "arw" on the view.
>
>
>
> Can anyone comment or advise
>
> Regards
>
> Max Wood
>
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org