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=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ürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Update instead rules on Views

Helo,

On Tue, 2 Nov 2004 13:05:07 +0100
Markus Schaber 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
> ;

I now got it to work with adding a "WHERE index=3DNEW.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
--=20
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Update instead rules on Views

Hello,

On Tue, 2 Nov 2004 16:20:37 +0100
Markus Schaber 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

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ürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org