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

works fine for me.  Do you have any triggers on the tables or other rules?  Can you provide a complete SQL script that
starts from an empty database.

Jim



---------- Original Message -----------
From: Neil Dugan <postgres@butterflystitches.com.au>
To: pgsql-sql@postgresql.org
Sent: Thu, 16 Jun 2005 13:38:58 +1000
Subject: [SQL] funny update, say update 1, updated 1 added 2nd.

> 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.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq
------- End of Original Message -------