Subject: funny update, say update 1, updated 1 added 2nd.
From: Neil Dugan <postgres@butterflystitches.com.au>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Date: Thu, 16 Jun 2005 13:38:58 +1000
Message-Id: <1118893139.10321.0.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


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.