From: | bryan(at)flyingiranch(dot)com |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Stored procedure - change columns in a table that is being updated / inserted on? |
Date: | 2003-03-08 22:41:15 |
Message-ID: | H00000660001d077.1047163275.mule.flyingiranch.com@MHS |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)flyingiranch(dot)com
http://www.flyingiranch.com
(503) 777-2895
From | Date | Subject | |
---|---|---|---|
Next Message | bryan | 2003-03-08 23:11:17 | Re: Stored procedure - change columns in a table that is being updated / inserted on? |
Previous Message | Luis Miedzinski | 2003-03-08 21:09:53 | best php coding practices |