funny update, say update 1, changed 2 records. - Mailing list pgsql-general

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),
    postcode character varying(10),
    state character(4)
);

CREATE TABLE supplier (
    id bigserial NOT NULL,
    name character varying(40),
    phone character varying(20),
    fax character varying(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.8 on 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.