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

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

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