I need help with writing a trigger to automatically update and insert into a column the area of a polygon being created using PostgreSQL/PostGIS and QGIS.
This is what I have but it's not updating when I add new features.
CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
NEW.area_ha := ROUND((st_area(NEW.geom::geography)/10000)::numeric,2);
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER area_calculate AFTER INSERT OR UPDATE ON public."Guyana_Coastal_Zone"
FOR EACH ROW EXECUTE PROCEDURE calc_area();
Best Answer
This is not a very good idea, and there is no need to do this. Just create a functional index.
I would create the index on the unrounded version.
Then a query like this:
will use the index. Alternatively, you can create a
MATERIALIZED VIEW
. Generally, it's not a good idea to cache the result of a calculation on the table.Shy of that, if you really want to store the computed column on the row your trigger needs to be
BEFORE INSERT OR UPDATE
. I really wish returning non-null inAFTER
would generate a warning, but it's in the docs (emphasis added):