From: cpng@sophos.com (Cornelius Grotjahn)
Subject: What is the best way of writing update rule on view with joined
	tables?
Date: 16 Apr 2004 10:43:31 -0700
Organization: http://groups.google.com
Lines: 29
Message-ID: <195fde65.0404160943.722481e3@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

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