Problem: PostgreSQL apparently isn't short-circuiting a logical
expression, causing an error when it tries to evaluate OLD in an
INSERT trigger.
PostgreSQL normally 'short-circuits' logical expressions; that is,
once it figures out that it can't resolve an expression to truth,
it stops evaluating all the possibilities.
For example:
CREATE FUNCTION crash() RETURNS boolean AS '
BEGIN
RAISE EXCEPTION ''crash()'';
RETURN TRUE; -- will never get here
END;
' LANGUAGE 'plpgsql';
SELECT 1 WHERE crash();
ERROR: crash()
SELECT 1 WHERE 1=2 AND crash();
?column?
----------
(0 rows)
doesn't crash() because it realizes that, as both 1=2 and crash() must
return true, that it isn't worth checking crash().
However, I have a procedure called by a trigger that is called for
both INSERTs and UPDATEs. For INSERTs, we always want to check a class
capacity. For UPDATEs, we only want to check the capacity if the
registration status has changed:
CREATE FUNCTION reg_chk_capacity() RETURNS opaque AS '
DECLARE
seats int;
BEGIN
IF TG_OP=''INSERT'' OR
(TG_OP=''UPDATE'' AND (OLD.statuscode <> NEW.statuscode))
THEN
seats := Reg_SeatsLeft(NEW.InstID);
IF seats < 1
THEN
RAISE EXCEPTION ''reg_chk_capacity__inst_filled: InstID=%,
RegID=%'', NEW.InstID, NEW.RegID;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
(Reg_SeatsLeft() is a simple SQL function w/o any references to NEW or
OLD)
If I try to INSERT into this table, I get
ERROR: record old is unassigned yet
So, why hasn't the logic short-circuited? Am I missing something?
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington