Skip site navigation (1) Skip section navigation (2)

Create a rule for updating on a view

From: "Dong Meng" <eddy(at)securities(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Create a rule for updating on a view
Date: 2002-01-23 02:18:28
Message-ID: AEEBJOIDAHEOMKHEOGEIIEJCCEAA.eddy@securities.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom,

My PG environment:

psql (PostgreSQL) 7.1rc4
contains readline, history support


Here is my question: By update a view, I want to update a table, it works if I create a rule without 'where' restriction 'create rule xxx as on update yyy to zzz ...', but it fails if I add a WHERE after this clause.


An example can be seen: ( "t1" is a simple table including only one column "c1";   "v_t1" is a simple view of "t1" and rule "update_v_t1" update "t1" instead of update view "v_t1", which is not applicate directly)


First, I create a table ("t1"):
create table "t1" ("c1" char(20));

Second, I create its view:
create view "v_t1" as select * from "t1";

Thirdly, I create the update-related rule as:
create rule update_v_t1 as on update to v_t1 do instead update t1 set "c1" = new."c1" where "c1" = old."c1";

OK. After I inserted some data into table "t1", I can update view "v_t1" just like what I want on "t1".

Problem comes when I try to define some more complex rule:

Forthly, I drop my previous rule update_v_t1
drop rule update_v_t1

5th, I re-create that rule
create rule update_v_t1 as on update to v_t1 where 1 = 1 do instead update t1 set "c1" = new."c1" where "c1" = old."c1";       ( just added some RESTRICTION "where 1=1" )

I was told 
"CREATED"

But when I try to update on "v_t1" any more, I was always told:
ERROR:  Cannot update a view without an appropriate rule




Is this a bug?

Please help.

Thank all of you


Eddy





In response to

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2002-01-23 15:46:20
Subject: Bug #565: Primary key not working correctly with inheritance
Previous:From: Tatsuo IshiiDate: 2002-01-23 01:12:54
Subject: Re: A Chinese handling bug

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group