According to the documentation at Rules and Command Status <http://www.postgresql.org/docs/8.2/static/rules-status.html> when creating rules, the resulting command status from the rules executed is dependent upon the rules that were added.
According to the documentation: "If there is any unconditional INSTEAD rule for the query, then the original query will not be executed at all. In this case, the server will return the command status for the last query that was inserted by an INSTEAD rule (conditional or unconditional) and is of the same command type (INSERT, UPDATE, or DELETE) as the original query. If no query meeting those requirements is added by any rule, then the returned command status shows the original query type and zeroes for the row-count and OID fields." My understanding (and that of at least several others) indicates that only those conditional rules that are true should be added to the query plan. So if there are 3 rules, and 2 of them are false and only one is true, then the rule that met the "WHERE" criteria should be the only one added to the query plan. In the case described in the documentation, if there are one or more unconditional rules, then the result of the query will be the result of the last rule that was added to the query plan. Therefore if there is an unconditional rule, and several conditional rules, of which only one of those whose where clause is true, then the result of the query should be that of the single conditional query that should have been executed (taking into account that it must be alphabetically after the unconditional rule). The behavior I am seeing is not following this type of activity, consider the following case: test=# create table foo (x integer); CREATE TABLE test=# create table foo1 (x integer); CREATE TABLE test=# create table foo2 (x integer); CREATE TABLE test=# create rule foo_0 as on insert to foo do instead nothing; CREATE RULE test=# create rule foo_1 as on insert to foo where NEW.x = 1 do instead insert into foo1 (x) values (NEW.x); CREATE RULE test=# create rule foo_2 as on insert to foo where NEW.x = 2 do instead insert into foo2 (x) values (NEW.x); CREATE RULE test=# insert into foo (x) values (0); INSERT 0 0 test=# insert into foo (x) values (1); INSERT 0 0 test=# insert into foo (x) values (2); INSERT 0 1 test=# insert into foo (x) values (3); INSERT 0 0 The line highlighted in red (annotated with >>) indicates a return of "INSERT 0 0" where it should have returned "INSERT 0 1" due to the fact that only one rule should have been added to the query plan. However, the rule foo_2 was added to the query plan and inserted no rows and therefore the result was insert 0 0. Refer to this postgresql forums thread <http://www.postgresqlforums.com/forums/viewtopic.php?f=42&t=606> for more discussion on this topic. Shawn Chasse ExaGrid Systems, Inc. 2000 West Park Drive Westborough, MA 01581 Office: 508-898-2872 Ext 332 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> www.exagrid.com <http://www.exagrid.com/>
