inherited table and rules

Поиск
Список
Период
Сортировка
От Scott Frankel
Тема inherited table and rules
Дата
Msg-id d79254aba7f2ca46083f5a837be60eb0@pacbell.net
обсуждение исходный текст
Ответы Re: inherited table and rules  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Re: inherited table and rules  (Klint Gore <kg@kgb.une.edu.au>)
Список 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 по дате отправления:

Предыдущее
От: "Randy Samberg"
Дата:
Сообщение: postgres oracle emulation question
Следующее
От: Lonni J Friedman
Дата:
Сообщение: Re: postgres oracle emulation question