pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mark Volpe <volpe.mark@epamail.epa.gov>
To: pgsql-sql@postgresql.org
Subject: Rules aren't doing what I expect
Date: Wed, 09 Aug 2000 12:04:13 -0400
Message-ID: <399180FD.2ACF8B2D@epamail.epa.gov> (raw)

Hi again,

I have a table with a trigger that can potentially modify a row before it gets
inserted or updated:

	CREATE TABLE t1 (a int);
	CREATE FUNCTION t1_validate() RETURNS opaque AS
	'
		BEGIN
		IF (NEW.a>10) THEN NEW.a=10; END IF;
		IF (NEW.a<0) THEN NEW.a=0; END IF;
		RETURN NEW;
		END;
	' LANGUAGE 'plpgsql';
	CREATE TRIGGER t1_trig BEFORE INSERT OR UPDATE
		ON t1 FOR EACH ROW EXECUTE PROCEDURE t1_validate();

I have another table that tracks changes in the first table with rules:

	CREATE TABLE t1_log (old_a int, new_a int);
	CREATE RULE t1_insert AS ON INSERT TO t1
		DO INSERT INTO t1_log VALUES(NULL, NEW.a);
	CREATE RULE t1_update AS ON UPDATE TO t1
		DO INSERT INTO t1_log VALUES(OLD.a, NEW.a);
	CREATE RULE t1_delete AS ON DELETE TO t1
		DO INSERT INTO t1_log VALUES(OLD.a, NULL);

When I try this out, however, the rule seems to use the original value, rather
than the "corrected" value.

	INSERT INTO t1 VALUES(2);
	INSERT INTO t1 VALUES(8);
	INSERT INTO t1 VALUES(15);
	SELECT * FROM t1;

	 a  
	----
	  2
	  8
	 10

The table t1 shows the corrected value of 10, but,

	SELECT * FROM FROM t1_log;
	
	 old_a | new_a 
	-------+-------
	       |     2
	       |     8
	       |    15
	
The t1_log table doesn't show what was actually inserted into t1!
Are there any changes I can make to the logic above so that t1_log can
show the correct value?

Thanks,
Mark



view thread (5+ messages)  latest in thread

Message-ID: <399180FD.2ACF8B2D@epamail.epa.gov>
Permalink:  ../399180FD.2ACF8B2D@epamail.epa.gov/
Also on:    postgresql.org/message-id/399180FD.2ACF8B2D@epamail.epa.gov

 · 

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: volpe.mark@epamail.epa.gov
  Subject: Re: Rules aren't doing what I expect
  In-Reply-To: <399180FD.2ACF8B2D@epamail.epa.gov>
  References: <399180FD.2ACF8B2D@epamail.epa.gov>

* 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