Rules on update behavior unexplained ... --> inheritance problem (I believe) - Mailing list pgsql-general

From
Subject Rules on update behavior unexplained ... --> inheritance problem (I believe)
Date
Msg-id 002301c1d5e0$4c3f9330$6501a8c0@calvin
Whole thread Raw
List pgsql-general
<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





pgsql-general by date:

Previous
From: "Shabnam Nisha"
Date:
Subject: Type interval error in Postgresql7.1.3
Next
From:
Date:
Subject: Re: Rules on update behavior unexplained ... --> inheritance problem (I believe)