inherited table and rules

From: Scott Frankel <leknarf(at)pacbell(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: inherited table and rules
Date: 2005-03-23 01:53:11
Message-ID: d79254aba7f2ca46083f5a837be60eb0@pacbell.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lonni J Friedman 2005-03-23 02:15:11 Re: postgres oracle emulation question
Previous Message Randy Samberg 2005-03-23 01:51:06 postgres oracle emulation question