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

[James McManus]

That's what I am currently struggling with. My current code is:

CREATE OR REPLACE
FUNCTION public.region3_sim_storms_pg(z integer, x integer, y integer,
stormtable text)
RETURNS bytea
AS $$
DECLARE
  result bytea;
BEGIN
  EXECUTE format(
      'WITH
       bounds AS (
         SELECT ST_TileEnvelope(%s, %s, %s) AS geom
       ),
       mvtgeom AS (
         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))
       )
       SELECT ST_AsMVT(mvtgeom, %s)
       INTO result FROM mvtgeom;', z, x, y, quote_ident(stormtable),
quote_literal('public.region3_sim_storms_pg')
  )
  INTO result;
  RETURN result;
END
$$
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL SAFE;

COMMENT ON FUNCTION public.region3_sim_storms_pg IS 'Given a tile address,
storm name query database.';

When I run it I get:

=# SELECT * FROM region3_sim_storms_pg(13,2361,3138,'r3sim_fort_geom');
ERROR:  EXECUTE of SELECT ... INTO is not implemented
HINT:  You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ...
AS instead.
CONTEXT:  PL/pgSQL function
region3_sim_storms_pg(integer,integer,integer,text) line 5 at EXECUTE




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

> (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
>>
>> _______________________________________________
> 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/5348d8db/attachment.html>