Trying to create a dynamic trigger function in PostGIS but getting a "max_stack_depth" error message.
I have two tables in PostGRES/GIS that have geometry's testzone.carsurtest and political.localities and I'm trying to update the 'locality' field in testzone.carsurtest from the 'locality_name' field political.localities using ST_Intersects. I've successfully been able to create a trigger function specifically for testzone.carsurtest however I'd like to create a dynamic trigger function to use for multiple tables wanting to update from the locality table.
CREATE OR REPLACE FUNCTION tf_locality_test() RETURNS trigger AS $BODY$ DECLARE tbl text := TG_TABLE_NAME; scm text := TG_TABLE_SCHEMA; BEGIN EXECUTE 'UPDATE '|| quote_ident(cast(scm as text)) ||'.'|| quote_ident(cast(tbl as text)) ||' AS dyntbl SET locality = political.locality.locality_name FROM political.locality WHERE ST_Intersects(dyntbl.wkb_geometry, political.locality.wkb_geometry)'; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION tf_locality_test() OWNER TO postgres;
When I create a new polygon record in QGIS i get the following error message:
Could not commit changes to layer carsurtest > > Errors: ERROR: 1 feature(s) not added. > Provider errors: > PostGIS error while adding features: ERROR: stack depth limit exceeded > HINT: Increase the configuration parameter "max_stack_depth", after ensuring the platform's stack depth limit is adequate. > CONTEXT: PL/pgSQL function "tf_locality_test" line 8 at EXECUTE statement > SQL statement "UPDATE test_zone.carsurtest AS dyntbl > SET locality = political.locality.locality_name > FROM political.locality > WHERE ST_Intersects(dyntbl.wkb_geometry, political.locality.wkb_geometry)"
Any suggestions would be greatly appreciated. Thanks, Chris