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

[Nicolas Ribot]

(and you can still store the result in a variable with the construct
EXECUTE 'your query here' into result;)

nicolas

On Wed, 16 Sep 2020 at 16:40, Nicolas Ribot <nicolas.ribot at gmail.com> wrote:

> 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/db80bd9b/attachment.html>