Re: inherited table and rules

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;