inherited table and rules
От | Scott Frankel |
---|---|
Тема | inherited table and rules |
Дата | |
Msg-id | d79254aba7f2ca46083f5a837be60eb0@pacbell.net обсуждение исходный текст |
Ответы |
Re: inherited table and rules
Re: inherited table and rules |
Список | pgsql-general |
This is weird. I have two tables: one inherits from the other. And I have a rule that populates the inherited table with changes from the first. When I update a row in the first table, I get an ever-larger number of rows added to both it and the inherited table. i.e.: update 1 yields 2 new rows update 2 yields 6 new rows update 3 yields 42 new rows update 4 yields 1806 new rows I'm clearly doing something wrong ;) My hope was that on update, a field in the first table would be changed (leaving the same number of total rows as prior to update). And the inherited table would have one row added to it per update, reflecting a "change log" of the updates. Thanks in advance! Example code follows. Scott 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 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) -- 2 UPDATE people SET color = 'cyan' WHERE usr_pkey = 1; -- update table (2) -- 6 UPDATE people SET color = 'magenta' WHERE usr_pkey = 1; -- update table (3) -- 42 UPDATE people SET color = 'yellow' WHERE usr_pkey = 1; -- update table (4) -- 1806 UPDATE people SET color = 'black' WHERE usr_pkey = 1;
В списке pgsql-general по дате отправления: