What is the best way of writing update rule on view with joined tables?

From: cpng(at)sophos(dot)com (Cornelius Grotjahn)
To: pgsql-sql(at)postgresql(dot)org
Subject: What is the best way of writing update rule on view with joined tables?
Date: 2004-04-16 17:43:31
Message-ID: 195fde65.0404160943.722481e3@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Please, how can I rewrite the rule below so that it works as intended for this
update:

update v set ad=0, bd=0 where ad=1;

As it is now, this will change ad but not bd, presumably because when the
rule's first action has updated ad, the "where ad=1" returns 0 rows for the
second action.

I want this because that is the way MS Access puts data into updates' where
clauses and I want updateable forms on joined tables.

create table a (k integer primary key, ad integer);
create table b (k integer primary key, bd integer);
create view v as select a.k, ad, bd from a join b on a.k=b.k;
create rule r as on update to v do instead
(
update a set ad=new.ad where k=old.k;
update b set bd=new.bd where k=old.k;
);

insert into a values(1,1);
insert into a values(2,2);
insert into b values(1,1);
insert into b values(2,2);

Thank you -- Cornelius

Browse pgsql-sql by date

  From Date Subject
Next Message Yudie 2004-04-16 17:47:58 Re: Update is very slow on a bigger table
Previous Message Dimitar Georgievski 2004-04-16 14:40:36 Re: Update is very slow on a bigger table