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

try select * from ONLY people.

also check out this query
select relname,people.* from people join pg_class on people.tableoid=pg_class.oid;
and
select relname,people.* from ONLY people join pg_class on people.tableoid=pg_class.oid;

Jim


---------- Original Message -----------
From: Scott Frankel <leknarf@pacbell.net>
To: pgsql-general@postgresql.org
Sent: Wed, 23 Mar 2005 11:48:46 -0800
Subject: Re: [GENERAL] 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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
------- End of Original Message -------