[postgis-users] ST_AsMVTGeom AND table name as parameter in pl/psql

[Nicolas Ribot]

Hi,

You have to enclose the whole query into a string and execute it, not just
only the second CTE (mvtgeom).
It should be:
BEGIN
EXECUTE format('WITH bounds as ( ....', ...)

Nicolas

On Wed, 16 Sep 2020 at 16:32, James McManus <jmpmcmanus at gmail.com> wrote:

> 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.
> 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, 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200916/1b26b20a/attachment.html>