Trigger function to update column

Question

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?

Answer 1

need to update a column every time a new record is inserted

Your description contradicts the trigger definition with BEFORE INSERT OR UPDATE. Either just BEFORE INSERT or the description is wrong.

Next, your whole trigger function is a misunderstanding. Would just be:

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

Just assign the desired value before the row is inserted. (No SET here.)

But really, I would use the VIEW Evan suggested.
As for your comment, if QGIS somehow messes with the automatic feature of updatable views (like you state in the related question on gis.SE), just keep writing to the table directly. The VIEW is for representation only in this case, to display the additional, functionally dependent column.

Answer 2

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;

Answer 3

Your trigger code runs every time any row in table tbl_cap is updated.

Your trigger updates all rows in tbl_cap (or maybe just the inserted or updated rows - I don't know the innards of PostgreSQL as well as some other DBMSes).

So, when your trigger runs, it changes rows in the table. Which makes your trigger run. Which changes rows in the table. Which makes your trigger run. Which changes rows in the table. Which makes your trigger run. Which changes rows in the table.... This repeats until the stack (which tracks the current values of the environment before each dive into a new iteration of the trigger) fills up. That's the error you saw; the result of this infinite recursion, with the trigger triggering itself.

As noted in Evan Carroll's answer, you can use the new and old "tables" to change what the original update or insert is writing into the table, avoiding the second update, and thus the infinite loop.