update table column - postgresql

Question

update table column - postgresql

Navigation

#1 by (1 votes)

1

I'm new to pgSQL ... I use version 9.5.0 and need to update a column every time a new record is inserted. The column must be populated from the values entered in area_pol and area_ofi.

I'm 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_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 msg is displayed: 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.

postgresql

asked by anonymous 29.03.2018 / 21:46

1 answer

Help in C Algorithm activity How to remove items from an array?

score 1 · Accepted Answer

Your TRIGGER is executed on every UPDATE and it also executes an UPDATE, generating an infinite loop. Hence HINT warning you that the memory limit is being set aside for stack recursion.

A simple way out is to run FUNCTION if it is a first-level "instance" of TRIGGER, and ignore UPDATES that come from other iterations by breaking the loop.

To do this use FUNCTION pg_trigger_depth() , which returns a number that represents the recursion level of the current execution. Thus, your TRIGGER must be conditioned to return 0 of that function, using the WHEN clause:

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();