Re: inherited table and rules

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(at)pacbell(dot)net>
To: pgsql-general(at)postgresql(dot)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 -------