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.