Trigger function to update column

I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi.

I'm trying to create this function to suit my case:

CREATE OR REPLACE FUNCTION sch_cap.fc_atualiza_dif_area()
  RETURNS trigger AS
$
BEGIN
    UPDATE
        sch_cap.tbl_cap
    SET
        dif_area = abs(100 - (tbl_cap.area_pol / (tbl_cap.area_ofi * 100)));
END;
$
LANGUAGE plpgsql;

CREATE TRIGGER tg_atualiza_dif_area
  BEFORE INSERT OR UPDATE ON sch_cap.tbl_cap
  FOR EACH ROW EXECUTE PROCEDURE sch_cap.fc_atualiza_dif_area();

But when I try to insert a record, the following error appears:


ERROR: stack depth limit exceeded

HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

How to get it right?

This isn't how a trigger works. You should fire the TRIGGER before the update, then you just use SET on the NEW record.

SET new.dif_area = abs(100 - (OLD.area_pol / (OLD.area_ofi * 100)));

But even better, just drop this entirely and use a VIEW.

CREATE VIEW sch_cap.tbl_cap AS
  SELECT *, abs(100 - (area_pol / (area_ofi * 100))) AS dif_area
  FROM tbl_cap;

StackExchange Database Administrators Q#202651, answer score: 6