From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Update instead rules on Views
Date: Tue, 2 Nov 2004 13:05:07 +0100
Message-ID: <20041102130507.2acf486a@kingfisher.intern.logi-track.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: schabios@logi-track.com
Subject: Re: Update instead rules on Views
In-Reply-To: <20041102130507.2acf486a@kingfisher.intern.logi-track.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox