BUG #3945: unexpected ON INSERT rule behaviour - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3945
Logged by:          Holger Klawitter
Email address:      info@klawitter.de
PostgreSQL version: 8.2.6
Operating system:   Linux/i386
Description:        unexpected ON INSERT rule behaviour
Details:

Well,
this is probably not really a bug, more a feature
deeply buried in the query-tree-concept worth placed as a pitfall warning in
the documentation :-)

The following code:
CREATE TABLE a (a int);
CREATE TABLE b (b int);

CREATE OR REPLACE RULE a_to_b
AS ON INSERT TO a
DO ALSO INSERT INTO b VALUES (NEW.a);

INSERT INTO a VALUES ( 1 );
INSERT INTO a VALUES ((SELECT max(a)+1 from a));

SELECT * from b;

Produces the following output:
 b
---
 1
 3         <---- 2 expected here
(2 rows)

One would expect 1 and 2 to be stored in b.

The problem is that NEW.a is not the *value* being inserted. Instead, NEW
reproduces the unevaluated
selected statement.

Regards
  Holger