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