I've two geometry table inside my DB:
- buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON
- polygons: geom MULTIPOLYGON
I need have the intersection between buffer and polygons and I will do this using this TRIGGER:
CREATE OR REPLACE FUNCTION intersection_function()
RETURNS TRIGGER AS
$$
BEGIN
SELECT
a.id as idb,
ST_Intersection(NEW.a.geom, NEW.b.geom) as geom
FROM buffer as a, polygons as b
WHERE ST_Intersects(a.geom, b.geom);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
CREATE TRIGGER intersection_trigger
AFTER INSERT OR UPDATE ON test_intersection
FOR EACH ROW EXECUTE PROCEDURE intersection_function();
Table test_intersection has: id INTEGER SERIAL PRIMARY KEY, idb INTEGER and geom MULTIPOLYGON.
When I draw a polygon inside buffer table I expect to have the result of intersection inside test_intersection but that table is always empty.
It is my first trigger, what I've wrong?