update column in table - postgresql

Question

I’m a beginner in pgsql... I use version 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 am trying to create this Function to meet my need:

 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_update_dif_area BEFORE INSERT OR UPDATE ON sch_cap.tbl_cap FOR EACH ROW EXECUTE PROCEDURE sch_cap.fc_update_dif_area();

But when I try to enter a record, the following msg is shown: 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.

Answer 1

Its TRIGGER is executed at each UPDATE and itself also executes an UPDATE, generating an infinite loop. Hence the HINT warning you that you are breaking the memory limit reserved for stack recursiveness.

A simple output for this is just run FUNCTION if it is a first-level "instance" of TRIGGER, and ignore UPDATES that come from other iterations, breaking the loop.

For this use FUNCTION pg_trigger_depth(), that returns a number that represents the recursive level of the current run. Thus, your TRIGGER must be conditioned to return 0 of that function, using the clause WHEN:

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