Rule - Delete or Upgrade will not work with primary key
Rule - Delete or Upgrade will not work with primary key
От:
miss <miss@flex.com.au>
Дата:
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
Re: Rule - Delete or Upgrade will not work with primary key
От:
The Hermit Hacker <scrappy@hub.org>
Дата:
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