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