Date: Tue, 2 Nov 2004 13:05:07 +0100
From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Update instead rules on Views
Message-ID: <20041102130507.2acf486a@kingfisher.intern.logi-track.com>
Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?=
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

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=20
    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=20
AS ON UPDATE TO testview DO INSTEAD=20
    UPDATE realdata SET=20
        index =3D NEW.index,
        data =3D NEW.data,
        obsolete=3DNULL
    ;

--- snip snap ---

But now, when we issue an

  UPDATE testview SET data=3D'nono' WHERE index=3D1;

we get the result=20

  UPDATE 3

So it updated _all_ of the rows instead of the qualified rows (WHERE index=
=3D1).
  SELECT * FROM realdata;
   index | data | obsolete=20
  -------+------+----------
       1 | nono |        =20
       1 | nono |        =20
       1 | nono |        =20

But the documentation states: (rules-update.html):

| No qualification but INSTEAD=20
|=20
| the query tree from the rule action with the original query
| tree's qualification added

I read this that the original qualification (WHERE index=3D1) is applied
to the rule, resulting in a transformed query equivalent to:

  UPDATE realdata SET data=3D'nono' WHERE index=3D1;

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)
--=20
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 z=FCrich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com