Received: from zee.computing.com (zee.computing.com [207.8.37.242])
	by hub.org (8.9.3/8.9.3) with ESMTP id BAA56066
	for <pgsql-sql@hub.org>; Wed, 14 Jul 1999 01:17:28 -0400 (EDT)
	(envelope-from jmr@computing.com)
Received: from zee.computing.com (localhost [127.0.0.1])
	by zee.computing.com (8.9.3/8.8.5) with ESMTP id AAA23407
	for <pgsql-sql@hub.org>; Wed, 14 Jul 1999 00:17:13 -0500 (CDT)
Message-Id: <199907140517.AAA23407@zee.computing.com>
To: pgsql-sql@hub.org
Subject: WHERE clause?
In-reply-to: Your message of "Tue, 13 Jul 1999 23:00:25 EDT."
	<199907140300.XAA36099@hub.org>
Date: Wed, 14 Jul 1999 00:17:12 -0500
From: Jim Rowan <jmr@computing.com>


I'm trying to create a log table to track changes to a table..  so I've
created a set of rules that look something like this:

CREATE RULE "m_log_change" AS ON UPDATE TO "machine"
do (
  INSERT INTO machine_log (who, date, machnum, col, newval)
    SELECT getpgusername(), 'now'::text, old.machnum,
	 'host', new.host
    WHERE (new.host != old.host) or 
	(old.host IS NOT NULL and new.host IS NULL) or
		(old.host IS NULL and new.host IS NOT NULL);
... more similar INSERT statements with different WHERE clauses..
);

My question is:

Is this the best way to code the WHERE?  What I'm really after is "did the
value change?".  I found that the tests to see if one-but-not-both of the
values is NULL are required to identify times when the value changed to or
from NULL.  

Seems like a lot of extra work...


Jim Rowan			DCSI	        DCE/DFS/Sysadmin Consulting
jmr@computing.com                                            (512) 374-1143