Postgresql – How to update a point table when a polygon table is modified

postgispostgresql

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.

Best Answer

Would make a comment, but don't have enough reputation, sorry.

Shouldn't NEW and OLD be B and ? B_new or something like this? Where is the new geometry stored to make the ST_Within check?

If I missed something and you want to compare geometries as in NEW.geom <> OLD.geom, consider ST_Equals or ST_OrderingEquals like ST_OrderingEquals(NEW.geom, OLD.geom) = 0

Related Question