I am trying to create a function that update table A (point) when table B (polygon) is modified. The reference to B must be changed in A. I can't get it to work. There are no overlaps in the polygons. It really is just to update the polygon ID in the points table. I was thinking of ST_Within to do it.
CREATE OR REPLACE FUNCTION maj_A() RETURNS trigger AS $foo$ BEGIN UPDATE public.Points AS A SET A.a = B.a, A.b = B.b FROM public.Polygons AS B WHERE ST_WITHIN(Points.geom, Polygons.geom) AND NEW.geom <> OLD.geom; RETURN NEW; END; $foo$ LANGUAGE plpgsql;
I am using postgre 10 and postgis 2.4. Any working function is welcome. The function is linked to a trigger.