From: | Petter Reinholdtsen <pere(at)hungry(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Bug in UPDATE RULE? |
Date: | 1998-06-12 16:35:53 |
Message-ID: | 199806121635.SAA12927@sleeper.games.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)td(dot)org(dot)uit(dot)no
O- <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/ | Go Mozilla, go! Go!
From | Date | Subject | |
---|---|---|---|
Next Message | Jose' Soares Da Silva | 1998-06-12 17:33:57 | Re: [INTERFACES] Re: M$-Access'97 and TIMESTAMPs |
Previous Message | Jackson, DeJuan | 1998-06-12 16:27:07 | RE: [SQL] DefineQueryRewrite: rule plan string too big |