Rule - Delete or Upgrade will not work with primary key

From: miss <miss(at)flex(dot)com(dot)au>
To: pgsql-admin(at)postgresql(dot)org
Subject: Rule - Delete or Upgrade will not work with primary key
Date: 2001-01-14 02:44:09
Message-ID: 3A611279.C46E2DE5@flex.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message rob 2001-01-14 14:39:38 Re: Re: [ADMIN] ODBC connect in ERWin
Previous Message Ed Loehr 2001-01-13 00:13:27 Re: [GENERAL] Re: ODBC connect in ERWin