pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunny update, say update 1, updated 1 added 2nd.
2+ messages / 2 participants
[nested] [flat]
* funny update, say update 1, updated 1 added 2nd.
@ 2005-06-16 03:38 Neil Dugan <postgres@butterflystitches.com.au>
2005-06-16 14:05 ` Re: funny update, say update 1, updated 1 added 2nd. Jim Buttafuoco <jim@contactbda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Neil Dugan @ 2005-06-16 03:38 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: funny update, say update 1, updated 1 added 2nd.
2005-06-16 03:38 funny update, say update 1, updated 1 added 2nd. Neil Dugan <postgres@butterflystitches.com.au>
@ 2005-06-16 14:05 ` Jim Buttafuoco <jim@contactbda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jim Buttafuoco @ 2005-06-16 14:05 UTC (permalink / raw)
To: Neil Dugan <postgres@butterflystitches.com.au>; pgsql-sql
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 -------
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-06-16 14:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-16 03:38 funny update, say update 1, updated 1 added 2nd. Neil Dugan <postgres@butterflystitches.com.au>
2005-06-16 14:05 ` Jim Buttafuoco <jim@contactbda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox