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.
   
   In addition, could we just rely on the rule system to implement the updatable
view with "check option"? 

   Please give me some hint if you have some free time.

   Best regards

   Josh
   


Tue, 13 May 2003 17:1:42 +0800
"postgresql" <postgresql@db.pku.edu.cn>
can we implement the updatable view through rule system?
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 #


Sun, 18 May 2003 09:54:58 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: can we implement the updatable view through rule system?