postgresql wrote:
> Hi, all
> Many talks have been dealed with the updatable view.
> In postgresql, It seems that we can use the powerful rule system
> to implement the update, insert, delete on view.
> But could we give a generalized update rule for a view?
> For example, we can construct the following rule:
> create table data(a int,b int,c int);
> create view view_data as select * from data where c=0;
>
> CREATE RULE view_data_update AS ON UPDATE TO view_data
> DO INSTEAD
> UPDATE data SET
> a=(NEW.c),
> b=(NEW.c);
>
> If we submit the query" update view data set a=1 where b=2", we could not
> rely on the "view_data_update" to finish the task. and I can not find how I can
> find a correct generalized rule to implement the update on view.
To work reliable especially with possible NULL values, it is a good idea
to give your table a primary key.
If your table "data" would have a primary key, let's say "b", then the
correct rule to accomplish your goal would be
CREATE RULE view_data_update AS ON UPDATE TO view_data DO INSTEAD UPDATE data SET a = NEW.a, b = NEW.b,
c= NEW.c WHERE b = OLD.b;
which would modify your above UPDATE query into a parsetree looking like
if you really typed
UPDATE data SET a = 1, b = b, c = c WHERE b = b AND c = 0 AND b = 2;
This will finish the task exactly and reliably.
The PostgreSQL rule system is in fact more powerfull than what's
required to implement updateable views per specification I think. And I
suggest reading the rule system documentation in the programmers manual.
The reason why I am against any attempt to automate the creation of
those rules is that it adds more confusion and uncertainty than it is
worth. Except for the experts who write those simple rules like above
just on the side, noone could ever predict for what view a reliable
ruleset will get created, when to double check the created rules and
when better to throw them away.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #