Re: Stored procedure - change columns in a table that is being updated / inserted on? - Mailing list pgsql-novice

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