Received: from superfly.archi-me-des.de (unknown [62.8.212.177]) by postgresql.org (Postfix) with ESMTP id 7BAD84760A9 for ; Thu, 4 Apr 2002 12:12:56 -0500 (EST) Received: (from frank@localhost) by superfly.archi-me-des.de (8.9.3/8.9.3) id TAA32040 for pgsql-sql@postgresql.org; Thu, 4 Apr 2002 19:17:19 +0200 Date: Thu, 4 Apr 2002 19:17:19 +0200 From: Frank Joerdens To: pgsql-sql@postgresql.org Subject: Rule trouble (looks to me exactly like the example) Message-ID: <20020404191719.C30469@superfly.archi-me-des.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.1.2i X-Subliminal-Message: Use Linux! Use Linux! X-Archive-Number: 200204/59 X-Sequence-Number: 7324 This is my table: CREATE TABLE tree ( id serial PRIMARY KEY, parentid int4 NOT NULL REFERENCES tree ( id ), ordering int4 NOT NULL, leveling int4 NOT NULL, status int4 NOT NULL, title text NOT NULL ); And here's the rule (it doesn't do anything real or interesting, I just simplified it to the point where it looked like the example): CREATE RULE insert_level AS ON INSERT TO tree WHERE new.id > 0 DO UPDATE tree SET leveling = 1 WHERE tree.oid = new.oid; which looks to me like one of the examples in Bruce's book (page 299 in my pdf version): CREATE RULE example_5 AS ON INERT TO emp WHERE new.salary > 5000 DO UPDATE emp SET salary = 5000 WHERE emp.oid = new.oid; If I do an insert like INSERT INTO tree (parentid,ordering,leveling,status,title) VALUES (0,0,0,1,'foo'); nothing happens to the leveling column though: archiweb=# select * from tree ; id | parentid | ordering | leveling | status | title ----+----------+----------+----------+--------+----------- 0 | 0 | 0 | 0 | 0 | root node 11 | 0 | 0 | 0 | 1 | foo 12 | 0 | 0 | 0 | 1 | foo (3 rows) Although the rule definitely is there: archiweb=# select tablename,rulename from pg_rules; tablename | rulename -----------+-------------- tree | insert_level (1 row) (the definition column looks ok too, I didn't include it because it'd look all messed up in the email here because it's in a single line.) I am using Postgres version 7.2.1 on Linux. Can anyone explain that? Regards, Frank