I'm trying to develop a plpgsql function that would extract mapbox vector tiles from a postgresql/post gis database. The database has multiple geospatial tables, so I want the function to be able to take a table name as a parameter.
I've gotten the function to work using hard coded table names. Discussion on functions with table name as a parameter say I need to use EXECUTE <https://www.postgresql.org/message-id/20011210134736.D70079-100000%40megazone23.bigpanda.com>. However, when I try and use EXECUTE in the mvtgeom AS section of the script I get a syntax error on EXECUTE: CREATE OR REPLACE FUNCTION public.region3_sim_storms_pg(z integer, x integer, y integer, stormtable text) RETURNS bytea AS $$ DECLARE result bytea; BEGIN WITH bounds AS ( SELECT ST_TileEnvelope(z, x, y) AS geom ), mvtgeom AS ( EXECUTE format( 'SELECT ST_AsMVTGeom(ST_Transform(G.geom, 3857), bounds.geom, 4096, 256, true) AS geom, node, bathymetry FROM %s AS G, bounds WHERE ST_Intersects(G.geom, ST_Transform(bounds.geom, 4326))', quote_ident(stormtable) ) ) SELECT ST_AsMVT(mvtgeom, 'public.region3_sim_storms_pg') INTO result FROM mvtgeom; RETURN result; END $$ LANGUAGE 'plpgsql' STABLE PARALLEL SAFE; COMMENT ON FUNCTION public.region3_sim_storms_pg IS 'Given a tile address, storm name query database.'; Past discussion on this topic says this error occurs because the function is not being recognized as a plpgsql function <https://stackoverflow.com/questions/6861374/postgresql-syntax-error-when-using-execute-in-function>, but I have LANGUAGE 'plpgsql' specified. The only thing I can think of is that things are reverted back to SQL in the "mvtgeom AS (" portion of the script. Is there a way to use EXECUTE in the "mvtgeom AS (" portion of the script? Thanks Jim
