Date: Tue, 2 Nov 2004 16:42:36 +0100
From: Markus Schaber <schabios@logi-track.com>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Update instead rules on Views
Message-ID: <20041102164236.639f10c9@kingfisher.intern.logi-track.com>
In-Reply-To: <20041102162037.306d4d0a@kingfisher.intern.logi-track.com>
References: <20041102130507.2acf486a@kingfisher.intern.logi-track.com>
	<20041102162037.306d4d0a@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,

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=20
> > AS ON UPDATE TO testview DO INSTEAD=20
> >     UPDATE realdata SET=20
> >         index =3D NEW.index,
> >         data =3D NEW.data,
> >         obsolete=3DNULL
> >     ;
>=20
> I now got it to work with adding a "WHERE index=3DNEW.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=20
AS ON UPDATE TO testview DO INSTEAD=20
    UPDATE realdata SET=20
        index =3D NEW.index,
        data =3D NEW.data,
        obsolete=3DNULL
    WHERE index =3D 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=20
-------+-------+----------
     2 | test  |        =20
     3 | blubb |        =20
     1 | nono  |        =20
     3 | hallo |        =20

and I issue
 =20
  viewtest=3D# update testview set data=3D'blob' where data=3D'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

--=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