Greetings:
I am working on a health maintenance application in which I have some
calculations to derive things like body mass index (which is a static
value based on a person's weight and height). I hit my head against a
wall for a full day trying to do this with a stored procedure like:
---------------------------------------------
CREATE FUNCTION calcbmi() RETURNS trigger AS '
DECLARE
userrow RECORD;
userbmi FLOAT;
tempid INT;
BEGIN
SELECT INTO userrow * FROM tbluserhealthbasic WHERE userID =
NEW.userID;
IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid User ID!'';
END IF;
userbmi := (userrow.userWeight / (userrow.userHeight *
userrow.userHeight));
UPDATE tbluserhealthbasic SET bmi=userbmi WHERE userID =
NEW.userID;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER docalcbmi AFTER INSERT OR UPDATE
ON tblUserHealthBasic FOR EACH ROW EXECUTE
PROCEDURE calcbmi();
---------------------------------------------
The problem with this, as I now know, is that any insert or update query
to that table would hang on the update statement, as the row is of
course locked at the time. Once I moved the calculated values to a
separate table, everything works fine, with the following changes to the
function:
---------------------------------------------
CREATE FUNCTION calcbmi() RETURNS trigger AS '
DECLARE
userrow RECORD;
userbmi FLOAT;
tempid INT;
BEGIN
SELECT INTO userrow * FROM tbluserhealthbasic WHERE userID =
NEW.userID;
IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid User ID!'';
END IF;
userbmi := (userrow.userWeight / (userrow.userHeight *
userrow.userHeight));
-- We need to determine whether to insert or update
SELECT INTO tempid userID FROM tblUserHealthCalculated where userID
= NEW.userID;
IF NOT FOUND THEN
INSERT INTO tbluserHealthCalculated (userID, bmi) values
(NEW.userID, userbmi);
ELSE
UPDATE tbluserhealthcalculated SET bmi=userbmi WHERE userID =
NEW.userID;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
---------------------------------------------
As you can see, I created a new table, tbluserhealthcalculated, that is
actually written to on any insert or update to tbluserhealthbasic.
What I want to know: Is there a way around the locking issue, so I can
use a trigger to update columns in the same table that is being written
to?
Thanks,
Bryan
---x-----x-----x-----x-----x---
Bryan White of Flying I Ranch
Technology Wrangler
bryan@flyingiranch.com
http://www.flyingiranch.com
(503) 777-2895
