Rules on update behavior unexplained ... --> inheritance problem (I believe)
<snip>
I defined on my database the following :
-- This table is really useless as such
-- *************************************
CREATE TABLE historification ( pkh SERIAL,
-- This will be the moment the record is "archived"
dateend TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Here is a basic address table
CREATE TABLE addresses ( pk SERIAL,
street CHAR(50) NOT NULL,
street2 CHAR(50),
zip CHAR(10) NOT NULL,
city CHAR(30) NOT NULL,
Country CHAR(2) REFERENCES COUNTRIES,
datemodified TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
-- Here is the PostgreSQL historification
CREATE TABLE AddressHistorics ()
INHERITS (historification, addresses);
CREATE RULE update_address AS
ON UPDATE TO addresses
DO
INSERT INTO AddressHistorics (pk, street, street2, zip, city,
country, datemodified)
VALUES (old.pk, old.street, old.street2, old.zip, old.city,
old.country, old.datemodified);
CREATE RULE delete_address AS
ON DELETE TO Addresses
DO
INSERT INTO AddressHistorics (pk, street, street2, zip, city,
country, datemodified)
VALUES (old.pk, old.street, old.street2, old.zip, old.city,
old.country, old.datemodified);
</snip>
Now what ever I do on the addresses table the data are not altered but
duplicated & the dupicate seem to be modified !
Hence I have doubles of each rows in my table when I do an update.
My conclusion is that the current behavior of
SELECT * FROM ADDRESSES;
is similar to the
SELECT * FROM ADDRESSES*;
as explained in the books ...
The question is HOW CAN make my Postgres 7.1 behave has it should ?
tx,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
After looking for ages into the paper versions I had & finally posted teh
question ... right before I found this
http://www.postgresql.org/idocs/index.php?inherit.html
Sorry for the post !
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas
----- Original Message -----
From: <tsmets@brutele.be>
To: <pgsql-general@postgresql.org>
Sent: 27 March, 2002 11:39 PM
Subject: [GENERAL] Rules on update behavior unexplained ... --> inheritance
problem (I believe)
<snip>
I defined on my database the following :
-- This table is really useless as such
-- *************************************
CREATE TABLE historification ( pkh SERIAL,
-- This will be the moment the record is "archived"
dateend TIMESTAMP DEFAULT
CURRENT_TIMESTAMP);
-- Here is a basic address table
CREATE TABLE addresses ( pk SERIAL,
street CHAR(50) NOT NULL,
street2 CHAR(50),
zip CHAR(10) NOT NULL,
city CHAR(30) NOT NULL,
Country CHAR(2) REFERENCES COUNTRIES,
datemodified TIMESTAMP DEFAULT
CURRENT_TIMESTAMP);
Show quoted text
-- Here is the PostgreSQL historification
CREATE TABLE AddressHistorics ()
INHERITS (historification, addresses);CREATE RULE update_address AS
ON UPDATE TO addresses
DO
INSERT INTO AddressHistorics (pk, street, street2, zip, city,
country, datemodified)
VALUES (old.pk, old.street, old.street2, old.zip, old.city,
old.country, old.datemodified);CREATE RULE delete_address AS
ON DELETE TO Addresses
DO
INSERT INTO AddressHistorics (pk, street, street2, zip, city,
country, datemodified)
VALUES (old.pk, old.street, old.street2, old.zip, old.city,
old.country, old.datemodified);
</snip>Now what ever I do on the addresses table the data are not altered but
duplicated & the dupicate seem to be modified !
Hence I have doubles of each rows in my table when I do an update.
My conclusion is that the current behavior of
SELECT * FROM ADDRESSES;
is similar to the
SELECT * FROM ADDRESSES*;
as explained in the books ...
The question is HOW CAN make my Postgres 7.1 behave has it should ?tx,
thomas,
--
Thomas SMETS
rue J. Wytsmanstraat 62
1050 Bruxelles
yahoo-id : smetsthomas---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly