Re: [GENERAL] inherited table and rules

2005-03-23 Thread Scott Frankel
On Mar 23, 2005, at 2:42 PM, Klint Gore wrote: Rows inserted into inherited tables are visible to the parent. It's effectively the same as having a union all on the 2 tables. Using the only qualifier is how you stop the "union" happening. This explains it. Thanks! Scott --

Re: [GENERAL] inherited table and rules

2005-03-23 Thread Klint Gore
On Wed, 23 Mar 2005 11:48:46 -0800, Scott Frankel <[EMAIL PROTECTED]> wrote: > > 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 pare

Re: [GENERAL] inherited table and rules

2005-03-23 Thread Jim Buttafuoco
Frankel <[EMAIL PROTECTED]> 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

Re: [GENERAL] inherited table and rules

2005-03-23 Thread Scott Frankel
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 whe

Re: [GENERAL] inherited table and rules

2005-03-23 Thread Stephan Szabo
On Tue, 22 Mar 2005, Scott Frankel wrote: > Syntax troubles. > > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? According to the docs, In a FROM clause, I should be able to > use the ONLY keyword preceding the table name. This throws an error: > > UPDATE

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Klint Gore
I think you can get what you want if you change the rule definition to 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; and your updates to be only's -- update table (1) -- 2 UPDATE ONLY people SET color

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Klint Gore
I thought that all rows in inherited tables are visible to the table that they are inherited from (ie all rows in people_history are visible to people). step by step would be > INSERT INTO people (usr_name, color) VALUES ('bob', 'red'); make one row in people > -- update table (1) -- 2 > UPDA

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Michael Fuhr
On Tue, Mar 22, 2005 at 09:59:23PM -0800, Scott Frankel wrote: > What is the proper syntax for using FROM ONLY table_name in an UPDATE > statement? See the UPDATE documentation: http://www.postgresql.org/docs/8.0/static/sql-update.html > What is the proper syntax for specifying FROM ONLY in th

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Thomas F . O'Connell
Did you happen to look at the manual? http://www.postgresql.org/docs/7.4/static/sql-update.html It pretty clearly indicates that the syntax is UPDATE ONLY table, so try eliminating the FROM in your UPDATE example below. http://www.postgresql.org/docs/7.4/static/sql-createtable.html The CREATE TAB

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Scott Frankel
Syntax troubles. What is the proper syntax for using FROM ONLY table_name in an UPDATE statement? According to the docs, In a FROM clause, I should be able to use the ONLY keyword preceding the table name. This throws an error: UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey =

Re: [GENERAL] inherited table and rules

2005-03-22 Thread Stephan Szabo
On Tue, 22 Mar 2005, Scott Frankel wrote: > > 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 i

[GENERAL] inherited table and rules

2005-03-22 Thread Scott Frankel
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 yiel