ST_AsMVTGeom AND table name as parameter in pl/psql - Mailing list pgsql-hackers

From James McManus
Subject ST_AsMVTGeom AND table name as parameter in pl/psql
Date
Msg-id CAFXM4AzLz7-MPAxCnWZtrDXpYoGfNqQU7rBd+3ziUCz78pjiBA@mail.gmail.com
Whole thread Raw
Responses Re: ST_AsMVTGeom AND table name as parameter in pl/psql  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Re: Proposal of new PostgreSQL Extension - PGSpiderExt
Next
From: Dmitry Dolgov
Date:
Subject: Re: Group by reordering optimization