funny update, say update 1, updated 1 added 2nd. - Mailing list pgsql-sql

I have been having some trouble with a particular table view.  An UPDATE
command is not only changing the applicable record it is also creating a
new record as well.

wholesale=# select * from accounts_supplier;id |       name       | contact |   addr    | addr2 |      town      |
postcode| state | phone | fax | account_type
 
----+------------------+---------+-----------+-------+----------------+----------+-------+-------+-----+--------------
1| ABC construction | TOM     |           |       |                |          | NSW   |       |     | Cash Only 2 |
test            |         |           |       |                |          |       |       |     | 7 Day 3 | build-4-U
    | boss    | somewhere |       | back of beyond |          |       |       |     | 7 Day
 
(3 rows)

wholesale=# update accounts_supplier set addr='nowhere' where id=3;
UPDATE 1
wholesale=# select * from accounts_supplier;id |       name       | contact |  addr   | addr2 |      town      |
postcode| state | phone | fax | account_type
 
----+------------------+---------+---------+-------+----------------+----------+-------+-------+-----+-------------- 1
|ABC construction | TOM     |         |       |                |          | NSW   |       |     | Cash Only 2 | test
        |         |         |       |                |          |       |       |     | 7 Day 6 | build-4-U        |
boss   | nowhere |       | back of beyond |          |       |       |     | 7 Day 3 | build-4-U        | boss    |
nowhere|       | back of beyond |          |       |       |     | 7 Day
 
(4 rows)


Can anyone tell me why this is happening and how to fix it.

Here are the table and view definitions.

CREATE TABLE account_type (   number smallint,   name character varying(20)
);

CREATE TABLE address (   addr character varying(40),   addr2 character varying(40),   town character varying(20),
postcodecharacter varying(10),   state character(4)
 
);

CREATE TABLE supplier (   id bigserial NOT NULL,   name character varying(40),   phone character varying(20),   fax
charactervarying(20),   contact character varying(40),   account_type smallint DEFAULT 0
 
)
INHERITS (address);

CREATE VIEW accounts_supplier AS   SELECT supplier.id,        supplier.name,        supplier.contact,
supplier.addr,       supplier.addr2,        supplier.town,        supplier.postcode,        supplier.state,
supplier.phone,       supplier.fax,        account_type.name AS account_type    FROM supplier, account_type    WHERE
(account_type.number= supplier.account_type);
 

CREATE RULE accounts_supplier_update    AS ON UPDATE TO accounts_supplier    DO INSTEAD UPDATE supplier    SET name =
new.name,       contact = new.contact,        addr = new.addr,        addr2 = new.addr2,        town = new.town,
postcode= new.postcode,        state = upper((new.state)::text),        phone = new.phone,        fax = new.fax,
account_type= (SELECT account_type.number            FROM account_type            WHERE ((account_type.name)::text =
(new.account_type)::text))   WHERE (supplier.id = new.id);
 



wholesale=# select version();                                                        version

--------------------------------------------------------------------------------------------------------------------------PostgreSQL
7.4.8on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22)
 
(1 row)

wholesale=# select * from account_type;number |   name
--------+-----------     0 | Cash Only     1 | 7 Day     2 | 30 Day     3 | 60 Day     4 | 90 Day
(5 rows)

Thanks for any help
Regards Neil.