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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | pgsql-bugs | 2002-01-23 15:46:20 | Bug #565: Primary key not working correctly with inheritance |
Previous Message | Tatsuo Ishii | 2002-01-23 01:12:54 | Re: A Chinese handling bug |