pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Petter Reinholdtsen <pere@hungry.com>
To: pgsql-sql@postgresql.org
Subject: Bug in UPDATE RULE?
Date: Fri, 12 Jun 1998 18:35:53 +0200
Message-ID: <199806121635.SAA12927@sleeper.games.no> (raw)


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-  <SCRIPT Language="Javascript">window.close()</SCRIPT>
http://www.hungry.com/~pere/    | Go Mozilla, go! Go!




Message-ID: <199806121635.SAA12927@sleeper.games.no>
Permalink:  ../199806121635.SAA12927@sleeper.games.no/
Also on:    postgresql.org/message-id/199806121635.SAA12927@sleeper.games.no

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: pere@hungry.com
  Subject: Re: Bug in UPDATE RULE?
  In-Reply-To: <199806121635.SAA12927@sleeper.games.no>
  References: <199806121635.SAA12927@sleeper.games.no>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox