Re: inherited table and rules - Mailing list pgsql-general

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)

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)



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;