funny update, say update 1, updated 1 added 2nd.

From: Neil Dugan <postgres(at)butterflystitches(dot)com(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: funny update, say update 1, updated 1 added 2nd.
Date: 2005-06-16 03:38:58
Message-ID: 1118893139.10321.0.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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),
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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Postgres Admin 2005-06-16 03:49:29 PostgreSQL and Delphi 6
Previous Message Vsevolod (Simon) Ilyushchenko 2005-06-15 22:45:56 cursor "<unnamed portal 1>" does not exist