Thread: Update instead rules on Views
Hello, It seems that I have a fundamental misunderstanding how views work. See the following example: --- snip snap --- CREATE TABLE realdata (index int, data varchar, obsolete int); COPY realdata FROM STDIN; 1 hallo \N 2 test \N 3 blubb \N \. -- The obsolete row is a row that is now to be calculated on the -- fly. In our real data, this is to be a nested select, but random() -- works for this example as well. CREATE VIEW testview AS SELECT index, data, (random()*99)::int from realdata; -- But to remain compatibility with old apps, we also need to manage -- updates to the view, which are to be rewritten as follows: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL ; --- snip snap --- But now, when we issue an UPDATE testview SET data='nono' WHERE index=1; we get the result UPDATE 3 So it updated _all_ of the rows instead of the qualified rows (WHERE index=1). SELECT * FROM realdata; index | data | obsolete -------+------+---------- 1 | nono | 1 | nono | 1 | nono | But the documentation states: (rules-update.html): | No qualification but INSTEAD | | the query tree from the rule action with the original query | tree's qualification added I read this that the original qualification (WHERE index=1) is applied to the rule, resulting in a transformed query equivalent to: UPDATE realdata SET data='nono' WHERE index=1; which works as expected. Can anyone enlighten me? Thanks, Markus PS: My server version is PostgreSQL 7.4.5 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC) 3.3.4 (Debian 1:3.3.4-9) -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
Helo, On Tue, 2 Nov 2004 13:05:07 +0100 Markus Schaber <schabios@logi-track.com> wrote: > -- But to remain compatibility with old apps, we also need to manage > -- updates to the view, which are to be rewritten as follows: > CREATE RULE testview_update_rule > AS ON UPDATE TO testview DO INSTEAD > UPDATE realdata SET > index = NEW.index, > data = NEW.data, > obsolete=NULL > ; I now got it to work with adding a "WHERE index=NEW.index" to the view. Am I correct in my assumption that this means that this only works when I have a primary key (or at least unique) row in my dataset? Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
Hello, On Tue, 2 Nov 2004 16:20:37 +0100 Markus Schaber <schabios@logi-track.com> wrote: > > -- But to remain compatibility with old apps, we also need to manage > > -- updates to the view, which are to be rewritten as follows: > > CREATE RULE testview_update_rule > > AS ON UPDATE TO testview DO INSTEAD > > UPDATE realdata SET > > index = NEW.index, > > data = NEW.data, > > obsolete=NULL > > ; > > I now got it to work with adding a "WHERE index=NEW.index" to the view. This seems only to work when I update on the INDEX row. I now modified the rule to look like: CREATE RULE testview_update_rule AS ON UPDATE TO testview DO INSTEAD UPDATE realdata SET index = NEW.index, data = NEW.data, obsolete=NULL WHERE index = OLD.index; This seems to work now for arbitrary columns, provided that index is an unique row. When I have a table that looks like index | data | obsolete -------+-------+---------- 2 | test | 3 | blubb | 1 | nono | 3 | hallo | and I issue viewtest=# update testview set data='blob' where data='hallo'; I get: UPDATE 2 and it really updated 2 rows. As far as I understand now, I need a primary key in the underyling table as the qualification from the original statemet is applied to the view results, and not propagated to the underlying table. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com