2

I'm using PostGIS and I would like to use trigger to: when there is a change in the geometry of a polygon, that its surface is also updated.

I have tested several syntaxes but I always get an error "ERROR: syntax error at the end of the entry" or nothing happens.

2
  • Please Edit the Question to specify the error you receive. Sample input data is also necessary, and making it a reproducible example by providing the full CREATE TABLE, CREATE FUNCTION, and CREATE TRIGGER syntax would increase the likelihood of generating a response.
    – Vince
    Nov 2, 2022 at 16:24
  • I think you are a END IF short
    – Ian Turton
    Nov 2, 2022 at 16:39

2 Answers 2

4

Your trigger set-up is bound to fail for a multitude of reasons, most prominently:

  • a [BEFORE|AFTER] INSERT trigger never resolves to TG_OP = 'UPDATE'
  • an unbounded UPDATE inside the function will update the whole table

Your attempt can be heavily simplified to

CREATE OR REPLACE FUNCTION schema.burn_area()
  RETURNS TRIGGER
  LANGUAGE 'plpgsql'
  AS
  $FUNCTION$
    BEGIN
      NEW.area = ST_Area(NEW.geom)/10000;
      RETURN NEW;
    END;
  $FUNCTION$
;

CREATE TRIGGER auto_burn_area
  BEFORE UPDATE OF geom ON schema."burn"      -- trigger only if geom is part of the UPDATE targets
  FOR EACH ROW
  WHEN ( NOT ST_Equals(OLD.geom, NEW.geom) )  -- likely no performance gain even with an equal geometry as UPDATE value, but here for reference 
  EXECUTE PROCEDURE schema.burn_area()
;

As of PostgreSQL 12 you can use the GENERATED column definitions for an auto-updated read-only field (with some restrictions):

ALTER TABLE table
  ADD COLUMN area_calc FLOAT GENERATED ALWAYS AS ( ST_Area(geom)/10000 ) STORED
;

Values generated from simple intra-row calculations which will never participate in filter conditions or joins are a good fit for dynamic (and lazy) evaluation, e.g. this approach or as part of a View.

2
  • Thank you for your answers. So if I understand correctly, in order to make updates in my function, my trigger must have a "before update"? I don't want the update trigger is activated on the whole table. Once it works, I would like to do insertions of values and not just updates in my trigger function. So my trigger will have to be a "before insert or update" and not just a "before update" so the solution proposed would not work anymore.
    – Demonshine
    Nov 3, 2022 at 10:54
  • @Demonshine you can simply add a second trigger on BEFORE INSERT executing the same function. I suggest to use the GENERATED approach, though.
    – geozelot
    Nov 3, 2022 at 11:04
1

As @Ian Turton pointed out, you haven't ended both your if statements:

CREATE OR REPLACE FUNCTION schema.burn_area()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$

BEGIN

IF TG_OP = 'UPDATE' THEN
  IF (ST_Equals(NEW.geom, OLD.geom)=FALSE) THEN
    UPDATE "burn" SET area = st_area(NEW.geom)/10000;
  END IF;
END IF;

RETURN NEW; 
END
$BODY$;
1
  • Thank you, you are right @jbalk. I don't have any more errors but nothing happens when I execute my trigger. If I change the geom of my polygon, I still have the old value of my area and not the new.
    – Demonshine
    Nov 3, 2022 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.