Re: update record with two-column primary key

Beautiful, Scott. You nailed it. Thanks for the help!

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Monday, November 12, 2007 5:10 PM
To: Charles Mortell
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] update record with two-column primary key


OK, a followup.  The problem is that your where clause in your update
rule isn't selective enough, so you're actually trying to update all
the rows that match just the one column in your where clause.  Here's
an example:

Note that I've disabled your pk so you can see what's happening:

drop table projectdata.data_business_list cascade;
CREATE TABLE projectdata.data_business_list
(
 item_id int4 NOT NULL,
 business int4 NOT NULL,
 comments varchar(256)
-- , CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business)
);

CREATE OR REPLACE VIEW projectdata.business_list AS
 SELECT t.item_id, t.business, t.comments
  FROM projectdata.data_business_list t;

CREATE OR REPLACE RULE update_buslistview AS
   ON UPDATE TO projectdata.business_list DO INSTEAD  UPDATE
projectdata.data_business_list
SET business = new.business, item_id=new.item_id, comments = new.comments
 WHERE item_id = old.item_id; -- and business=old.business;

insert into projectdata.data_business_list
(item_id, business, comments)
values
(1,2,'abc'), (1,3,'xyz');

UPDATE projectdata.business_list SET business = 13 Where item_id = 1
and business = 2;
select * from projectdata.business_list ;

You'll see the output is this:

 item_id | business | comments
---------+----------+----------
       1 |       13 | abc
       1 |       13 | abc

Note that even the comments are the same.  However, if we make your
where clause in your rule more selective, by removing the ; and -- in
the middle of it, and it looks like this:

 WHERE item_id = old.item_id and business=old.business;

and run the query again, we get:

select * from projectdata.business_list ;
 item_id | business | comments
---------+----------+----------
       1 |        3 | xyz
       1 |       13 | abc

Now we test it with a real primary key and it also works the same.