Re: [GENERAL] inherited table and rules

Scott Frankel Wed, 23 Mar 2005 11:55:46 -0800


Close.  Thanks for the very helpful suggestions!

As I read the doco on rules and dissect the rule I've constructed, one issue remains: the UPDATE in my rule causes additional rows to be added to the parent table. How is that possible? How can it be suppressed?

i.e.: My rule specifies that when the parent table is updated, the inherited table receives an INSERT. There is nothing that I see that explicitly calls for a new row to be added to the parent table.

I've tried fiddling with INSTEAD; but my attempts haven't yielded the results I'm looking for. (Though the rule docs are quite opaque on the subect ...)

Thanks again!
Scott

Here's what my sample code (below) yields:

cs_test=# SELECT * FROM people;
 usr_pkey | usr_name |  color  |         timestamp
----------+----------+---------+----------------------------
        2 | carol    | green   | 2005-03-23 11:12:49.627183
        3 | ted      | blue    | 2005-03-23 11:12:49.637483
        1 | bob      | black   | 2005-03-23 11:12:49.616602
        1 | bob      | red     | 2005-03-23 11:12:49.616602
        1 | bob      | cyan    | 2005-03-23 11:12:49.616602
        1 | bob      | magenta | 2005-03-23 11:12:49.616602
        1 | bob      | yellow  | 2005-03-23 11:12:49.616602
(7 rows)

cs_test=# SELECT * FROM people_history; usr_pkey | usr_name | color | timestamp | hist_pkey | hist_tstamp ----------+----------+---------+---------------------------- +-----------+---------------------------- 1 | bob | red | 2005-03-23 11:12:49.616602 | 1 | 2005-03-23 11:13:17.04928 1 | bob | cyan | 2005-03-23 11:12:49.616602 | 2 | 2005-03-23 11:22:21.374629 1 | bob | magenta | 2005-03-23 11:12:49.616602 | 3 | 2005-03-23 11:23:49.253014 1 | bob | yellow | 2005-03-23 11:12:49.616602 | 4 | 2005-03-23 11:23:53.924315 (4 rows)


Here's what I'm looking for:

cs_test=# SELECT * FROM people;
 usr_pkey | usr_name |  color  |         timestamp
----------+----------+---------+----------------------------
        2 | carol    | green   | 2005-03-23 11:12:49.627183
        3 | ted      | blue    | 2005-03-23 11:12:49.637483
        1 | bob      | black   | 2005-03-23 11:12:49.616602
(3 rows)

sample code:

CREATE TABLE people (
usr_pkey     SERIAL    PRIMARY KEY,
usr_name     text      UNIQUE DEFAULT NULL,
color        text      DEFAULT NULL,
timestamp    timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE people_history (
hist_pkey    SERIAL    NOT NULL PRIMARY KEY,
hist_tstamp  timestamp DEFAULT CURRENT_TIMESTAMP
) INHERITS (people);

CREATE RULE
people_upd_history AS ON UPDATE TO people
DO INSERT INTO
people_history
SELECT * FROM ONLY people WHERE usr_pkey = old.usr_pkey;


-- populate table
INSERT INTO people (usr_name, color) VALUES ('bob',   'red');
INSERT INTO people (usr_name, color) VALUES ('carol', 'green');
INSERT INTO people (usr_name, color) VALUES ('ted',   'blue');

-- update table (1)
UPDATE ONLY people SET color = 'cyan' WHERE usr_pkey = 1;

-- update table (2)
UPDATE ONLY people SET color = 'magenta' WHERE usr_pkey = 1;

-- update table (3)
UPDATE ONLY people SET color = 'yellow' WHERE usr_pkey = 1;

-- update table (4)
UPDATE ONLY people SET color = 'black' WHERE usr_pkey = 1;


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] inherited table and rules

Reply via email to