<div dir="ltr">That's what I am currently struggling with. My current code is:<div><br></div><div>CREATE OR REPLACE<br>FUNCTION public.region3_sim_storms_pg(z integer, x integer, y integer, stormtable text)<br>RETURNS bytea<br>AS $$<br>DECLARE<br> result bytea;<br>BEGIN<br> EXECUTE format(<br> 'WITH<br> bounds AS (<br> SELECT ST_TileEnvelope(%s, %s, %s) AS geom <br> ),<br> mvtgeom AS (<br> SELECT ST_AsMVTGeom(ST_Transform(G.geom, 3857), bounds.geom, 4096, 256, true) AS geom, node, bathymetry<br> FROM %s AS G, bounds <br> WHERE ST_Intersects(G.geom, ST_Transform(bounds.geom, 4326))<br> )<br> SELECT ST_AsMVT(mvtgeom, %s)<br> INTO result FROM mvtgeom;', z, x, y, quote_ident(stormtable), quote_literal('public.region3_sim_storms_pg') <br> )<br> INTO result;<br> RETURN result;<br>END<br>$$<br>LANGUAGE 'plpgsql'<br>VOLATILE<br>PARALLEL SAFE;<br><br>COMMENT ON FUNCTION public.region3_sim_storms_pg IS 'Given a tile address, storm name query database.';<br></div><div><br></div><div>When I run it I get:</div><div><br></div><div>=# SELECT * FROM region3_sim_storms_pg(13,2361,3138,'r3sim_fort_geom');<br>ERROR: EXECUTE of SELECT ... INTO is not implemented<br>HINT: You might want to use EXECUTE ... INTO or EXECUTE CREATE TABLE ... AS instead.<br>CONTEXT: PL/pgSQL function region3_sim_storms_pg(integer,integer,integer,text) line 5 at EXECUTE<br></div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Sep 16, 2020 at 12:16 PM Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>(and you can still store the result in a variable with the construct EXECUTE 'your query here' into result;)</div><div><br></div><div>nicolas<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 16 Sep 2020 at 16:40, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Hi,</div><div><br></div><div>You have to enclose the whole query into a string and execute it, not just only the second CTE (mvtgeom).</div><div>It should be:</div><div>BEGIN</div><div>EXECUTE format('WITH bounds as ( ....', ...)</div><div><br></div><div>Nicolas<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 16 Sep 2020 at 16:32, James McManus <<a href="mailto:jmpmcmanus@gmail.com" target="_blank">jmpmcmanus@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">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.<br><br>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: <br><br>CREATE OR REPLACE<br>FUNCTION public.region3_sim_storms_pg(z integer, x integer, y integer, stormtable text)<br>RETURNS bytea<br>AS $$<br>DECLARE<br> result bytea;<br>BEGIN<br> WITH<br> bounds AS (<br> SELECT ST_TileEnvelope(z, x, y) AS geom<br> ),<br> mvtgeom AS (<br> EXECUTE format(<br> 'SELECT ST_AsMVTGeom(ST_Transform(G.geom, 3857), bounds.geom, 4096, 256, true) AS geom, node, bathymetry<br> FROM %s AS G, bounds<br> WHERE ST_Intersects(G.geom, ST_Transform(bounds.geom, 4326))', quote_ident(stormtable)<br> ) <br> )<br> SELECT ST_AsMVT(mvtgeom, 'public.region3_sim_storms_pg')<br> INTO result FROM mvtgeom;<br> RETURN result;<br>END<br>$$<br>LANGUAGE 'plpgsql'<br>STABLE<br>PARALLEL SAFE;<br><br>COMMENT ON FUNCTION public.region3_sim_storms_pg IS 'Given a tile address, storm name query database.';<br><br>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?<br><br>Thanks<br>Jim<br></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
</blockquote></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>