Received: from yuubin.games.no (yuubin.link.no [193.213.36.89]) by hub.org (8.8.8/8.7.5) with ESMTP id MAA20112 for ; Fri, 12 Jun 1998 12:36:28 -0400 (EDT) Received: from sleeper.games.no (pere@sleeper.link.no [193.213.36.74]) by yuubin.games.no (8.8.7/8.8.7) with ESMTP id SAA14407; Fri, 12 Jun 1998 18:37:15 +0200 Received: (from pere@localhost) by sleeper.games.no (8.8.7/8.8.7) id SAA12927; Fri, 12 Jun 1998 18:35:53 +0200 Date: Fri, 12 Jun 1998 18:35:53 +0200 Message-Id: <199806121635.SAA12927@sleeper.games.no> To: pgsql-sql@postgresql.org Subject: Bug in UPDATE RULE? From: Petter Reinholdtsen Reply-to: pere@hungry.com I have the following tables: CREATE TABLE orders( order_id int4 NOT NULL, state char DEFAULT 'R', whochanged text DEFAULT 'auto'); CREATE TABLE orders_timeline( order_id int4 NOT NULL, oldstate char NOT NULL, newstate char NOT NULL, whochanged text NOT NULL); I want to track all changes to table orders in orders_timeline. For this purpose, I have made the following rules: CREATE RULE orders_ins_r AS ON INSERT TO orders DO INSERT INTO orders_timeline (order_id, oldstate, newstate, whochanged) VALUES (CURRENT.order_id, '-', CURRENT.state, CURRENT.whochanged); CREATE RULE orders_upd_r AS ON UPDATE TO orders DO INSERT INTO orders_timeline (order_id, oldstate, newstate, whochanged) VALUES (CURRENT.order_id, CURRENT.state, NEW.state, NEW.whochanged); The first one works, and adds a row (#, '-', 'R', 'auto') when a new order is inserted into the table: > insert into orders (order_id) VALUES (1); > select * from orders_timeline; order_id|oldstate|newstate|whochanged --------+--------+--------+---------- 1| -|R |auto (1 row) The second one does not work. It inserts (#, 'M', 'M', 'auto') when I update the orders table with the following command: > update orders set state = 'M', whochanged = 'pere' where order_id = 1; > select * from orders_timeline; order_id|oldstate|newstate|whochanged --------+--------+--------+---------- 1|- |R |auto 1|M |M |pere (2 rows) I was expecting the second line to contain (1, 'R', 'M', 'pere'). It seems that I don't understand what CURRENT and NEW means in a RULE. Could someone enlighten me, or tell me how this should be done. I'm using PostgreSQL 6.3.2 on RedHat Linux 5.1. Please copy replies to mail mail address, as I don't follow this mailing-list. -- ##> Petter Reinholdtsen <## | pere@td.org.uit.no O- http://www.hungry.com/~pere/ | Go Mozilla, go! Go!