On Wed, Aug 01, 2007 at 09:30:25AM -0500, Weber, Geoffrey M. wrote: > 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: The insert case is easy, just set the default for the field to false. For updates, you'll want a trigger that compares NEW.field to OLD.field, and if they're the same sets NEW.field to false. > 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. > > > -- Decibel!, aka Jim Nasby decibel@xxxxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Attachment:
pgpbISjL0L7uU.pgp
Description: PGP signature
