Triggers: Detecting if a column value is explicitly set in an UPD ATE statement or not... ("IS NULL" not working?)

From: "Weber, Geoffrey M(dot)" <Geoffrey(dot)Weber(at)mcleodusa(dot)com>
To: "'pgsql-general(at)postgresql(dot)org'" <pgsql-general(at)postgresql(dot)org>
Subject: Triggers: Detecting if a column value is explicitly set in an UPD ATE statement or not... ("IS NULL" not working?)
Date: 2007-08-01 14:30:25
Message-ID: F341EFC2EEF3294FB8D4D49350085C3305CDBF16@iacedexch04.mcld.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm having a problem, and can't seem to find a good answer in the mailing
list archives... sorry if I'm missing something obvious!

Postgres version: 8.2.4
O/S: Solaris 10

I want to set a BOOLEAN column value to FALSE by default for all INSERT and
UPDATE statements performed against a particular table _UNLESS_ it's
explicitly set to TRUE in the SQL statement. Here is the trigger I created:

CREATE TABLE table1 ( id INTEGER PRIMARY KEY, data VARCHAR(64), b_flag
BOOLEAN);

CREATE OR REPLACE FUNCTION func1() RETURNS trigger AS $func1$
BEGIN
RAISE NOTICE 'NEW.b_flag=%', NEW.b_flag;
IF ( NEW.b_flag IS NULL ) THEN
NEW.b_flag := FALSE;
END IF;

RETURN NEW;
END;
$func1$ LANGUAGE plpgsql;

CREATE TRIGGER func1 BEFORE INSERT OR UPDATE ON table1 FOR EACH ROW EXECUTE
PROCEDURE func1();

What I get, however, is that for an UPDATE, the "NEW.b_flag" value evaluates
to TRUE if it has been set to TRUE by a previous UPDATE that explicitly sets
it. So...

tqa=> INSERT INTO table1 VALUES ( '1', 'some data');
NOTICE: NEW.b_flag=<NULL>
INSERT 0 1
tqa=> SELECT b_flag FROM table1 WHERE id='1';
b_flag
--------
f
(1 row)

tqa=> UPDATE table1 SET b_flag=TRUE where id='1';
NOTICE: NEW.b_flag=t
UPDATE 1
tqa=> SELECT b_flag FROM table1 WHERE id='1';
b_flag
--------
t
(1 row)

tqa=> UPDATE table1 SET data='new data' where id='1';
NOTICE: NEW.b_flag=t
UPDATE 1
tqa=> SELECT b_flag FROM table1 WHERE id='1';
b_flag
--------
t
(1 row)

As you can see, I put a RAISE NOTICE and verified that for some reason, the
NEW row contains a 'TRUE' value for b_flag, even though I didn't explicitly
set it in the last UPDATE statement. Why does it seem to be reading the
value from the OLD row for that column unless I override it inside the SQL
statement? Is there any way to achieve the desired result without having to
explicitly set 'b_flag' each time I touch a row in the table?

NOTICE: This electronic mail transmission may contain confidential
information and is intended only for the person(s) named. Any use, copying
or disclosure by any other person is strictly prohibited. If you have
received this transmission in error, please notify the sender via e-mail.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tino Wildenhain 2007-08-01 14:31:55 Re: How do I connect postgres table structures and view structures to an existing svn repository?
Previous Message Raymond O'Donnell 2007-08-01 14:19:56 Re: Linux distro