Thread: Stored procedure - change columns in a table that is being updated / inserted on?

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

bryan@flyingiranch.com writes:
> As you can see, I created a new table, tbluserhealthcalculated, that is
> actually written to on any insert or update to tbluserhealthbasic.

This sure seems like the hard way to do it.  If you really want
tbluserhealthcalculated to be separate from the underlying table,
why don't you make it a view?

CREATE VIEW tbluserhealthcalculated AS
SELECT *, (userWeight / (userHeight * userHeight)) AS bmi
FROM tbluserhealthbasic;

On the other hand, if you'd rather there were only one table, you
should be fixing the bmi value in a BEFORE trigger not an AFTER trigger.

CREATE FUNCTION calcbmi() RETURNS trigger AS '
  BEGIN
    NEW.bmi := (NEW.userWeight / (NEW.userHeight * NEW.userHeight));
    RETURN NEW;
  END' language plgsql;

CREATE TRIGGER docalcbmi BEFORE INSERT OR UPDATE
  ON tblUserHealthBasic FOR EACH ROW EXECUTE
  PROCEDURE calcbmi();

            regards, tom lane

> This sure seems like the hard way to do it.  If you really want
> tbluserhealthcalculated to be separate from the underlying table,
> why don't you make it a view?
>
> CREATE VIEW tbluserhealthcalculated AS
> SELECT *, (userWeight / (userHeight * userHeight)) AS bmi
> FROM tbluserhealthbasic;

Excellent idea - thanks. I _am_ a novice, so that advice really gives me
some insight.

>
> On the other hand, if you'd rather there were only one table, you
> should be fixing the bmi value in a BEFORE trigger not an
> AFTER trigger.

Also good advice - I didn't really understand the purpose of BEFORE, but
now I undestand that it preps the values before they are committed to
the table.

Thanks again,

Bryan

> This sure seems like the hard way to do it.  If you really want
> tbluserhealthcalculated to be separate from the underlying table,
> why don't you make it a view?
>
> CREATE VIEW tbluserhealthcalculated AS
> SELECT *, (userWeight / (userHeight * userHeight)) AS bmi
> FROM tbluserhealthbasic;

Next question: One of the reasons a function is attractive to me in this
situation is that I also have some conditionals to handle. For instance,
Base Metabolic Rate is different whether you are male or female (gender
is a boolean value in my table). Can I use IF/THEN syntax in a view
definition?

Bryan

bryan@flyingiranch.com wrote:
> Next question: One of the reasons a function is attractive to me in this
> situation is that I also have some conditionals to handle. For instance,
> Base Metabolic Rate is different whether you are male or female (gender
> is a boolean value in my table). Can I use IF/THEN syntax in a view
> definition?

Take a look at the CASE conditional expression:
http://developer.postgresql.org/docs/postgres/functions-conditional.html


HTH,

Joe

Joe Conway <mail@joeconway.com> writes:
> bryan@flyingiranch.com wrote:
>> Next question: One of the reasons a function is attractive to me in this
>> situation is that I also have some conditionals to handle. For instance,
>> Base Metabolic Rate is different whether you are male or female (gender
>> is a boolean value in my table). Can I use IF/THEN syntax in a view
>> definition?

> Take a look at the CASE conditional expression:
> http://developer.postgresql.org/docs/postgres/functions-conditional.html

Also, if your needs go beyond what seems reasonable to wedge into a
CASE, you could define a view that uses a function.  For example,

CREATE FUNCTION calc_bmi(basetable) returns float8 as
'compute appropriate value from fields of $1' ...;

CREATE VIEW derivedtable AS
SELECT *, calc_bmi(basetable) FROM basetable;

Passing in the whole row isolates the view definition from needing to
know exactly which fields go into the BMI calculation.  See
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-sql.html#AEN31256
for discussion of this.  As of recent versions you can also say

SELECT *, calc_bmi(basetable.*) FROM basetable;

which might or might not seem clearer to you...

            regards, tom lane