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
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

In response to

Browse pgsql-bugs by date

  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