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.