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 autoupdate 'longitude' and 'latitude' columns to get XY of the polygone 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 thigger:

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 erro 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?

Thanks in advance for your help!