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