Polygon centroid coordinates update error

Question

After a week of unsuccessful fighting I'm forced to ask for your help.

I have created a table 't_excavations' with fields 'id_excavation', 'geom' (MULTIPOLYGONE), 'longitude', 'latitude'

CREATE TABLE public.t_excavations ( id_excavation serial NOT NULL PRIMARY KEY, longitude numeric(9,6), latitude numeric(9,6), geom public.geometry ) WITH ( OIDS = FALSE );

Then I created a rule to auto update 'longitude' and 'latitude' columns to get XY of the polygon centroid that I add through QGIS

CREATE OR REPLACE RULE "insert_longlat" AS ON INSERT TO "public"."t_excavations" DO (UPDATE t_excavations SET longitude = st_x(st_centroid(t_excavations.geom)); UPDATE t_excavations SET latitude = st_y(st_centroid(t_excavations.geom)));

This rule works when I save changes in QGIS and coordinates are calculated fine

Now I want to update 'longitude' and 'latitude' columns if 'geom' field is changed (for example if I move the entire polygon or just one vertice)

I created a function:

CREATE OR REPLACE FUNCTION update_longlat() RETURNS trigger AS $BODY$ BEGIN NEW.longitude = ST_X(ST_Centroid(geom)); NEW.latitude = ST_Y(ST_Centroid(geom)); RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION update_longitude() OWNER TO postgres;

and a trigger:

 CREATE TRIGGER tr_update_longlat BEFORE UPDATE ON t_excavations FOR EACH ROW EXECUTE PROCEDURE update_longlat();

And now when I try to add new or edit existing polygon in QGIS I get this error message from postgres:

Can't change layer t_excavations

Errors: ERROR: not added 1 object.

Errors of the source:

Error PostGIS while adding objects: ERROR: column "geom" doesn't exist LINE 1: SELECT ST_X(ST_Centroid(geom)) ^ QUERY: SELECT ST_X(ST_Centroid(geom)) CONTEXT: function PL/pgSQL update_longlat(), row 3, assignment operator

What I do wrong with the function/trigger?

Answer 1

I think your trigger function should look something like this:

$BODY$
DECLARE
 lat DOUBLE PRECISION;
 lon DOUBLE PRECISION;
 id INTEGER;
BEGIN
 lat := ST_Y(ST_Centroid(NEW.geom));
 lon := ST_X(ST_Centroid(NEW.geom));
 id := NEW.id_excavation;
 UPDATE t_excavations SET longitude=lon, latitude=lat WHERE id_excavation=id;
 RETURN NULL;
END;
$BODY$

and you'll want the trigger defined as AFTER UPDATE. The "RETURN NULL" should prevent an endless update loop.