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

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

From
Neil Dugan
Date:
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),
postcodecharacter varying(10),   state character(4)
 
);

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





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

From
"Jim Buttafuoco"
Date:
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 -------