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

[James McManus]

I did not need "INTO result" in:

SELECT ST_AsMVT((mvtgeom, %s)
       INTO result FROM mvtgeom;', z, x, y, quote_ident(stormtable),
quote_literal('public.region3_sim_storms_pg')

changes it to:

SELECT ST_AsMVT((mvtgeom, %s)
       FROM mvtgeom;', z, x, y, quote_ident(stormtable),
quote_literal('public.region3_sim_storms_pg')

and it worked!

Thanks!
Jim

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

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