Passing table name as an argument in PostGIS function

Question

How can I pass my table name as an argument of a function?

 CREATE OR REPLACE
 FUNCTION public.countries_name1(
    z integer, x integer , y integer,
    name_prefix text default 'B' )
 RETURNS bytea
 AS $$
-- Convert tile coordinates to a bounding box 
 WITH
 bounds AS (
SELECT ST_TileEnvelope(z, x, y) AS geom,
(CASE 
when z >= 16 then 5
when z = 15 then 50
when z = 14 then 100
when z = 13 then 150
when z = 12 then 200
when z <= 11 then 300
--when z <= 10 then 500
--when z = 9 then 700
--when z = 8 then 800
--when z = 7 then 900
--when z <= 6 then 1000
 
ELSE 1 END
) as simplify_tolerance


),
 -- Convert raw geometry into MVT geometry
 -- Pull just the name in addition to the geometry
 -- Apply the name_prefix parameter to the WHERE clause
 mvtgeom AS (
 SELECT ST_AsMVTGeom(ST_Transform(ST_simplify(t.geom,simplify_tolerance), 3857), bounds.geom) 
  AS geom,
 t.fclass,z,x,y
 FROM table_name t, bounds
  WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom, 25833))
  AND upper(t.fclass) LIKE (upper(name_prefix) || '%') AND z>=10
   )
  -- Serialize the result set into an MVT object
  SELECT ST_AsMVT(mvtgeom, 'public.countries_name1') FROM mvtgeom;
   $$
   LANGUAGE 'sql'
   STABLE
    PARALLEL SAFE;

In this code I want to pass my table_name from function parameter.I want to use this function in pg_tileserv .I tried to pass table name from argument but it did not work.

Answer 1

OK, how do you rewrite your statement to dynamic SQL.
Well the first thing you do is read the Dynamic SQL documentation. Also helpful would be Lexical Structure section 4.1.2.4, and the Format function. I advise you first write a few simple test cases to become familiar with it. Dynamic SQL is not simple.
Below I do a direct translation of your function into dynamic SQL. I make **no guarantees on the correctness of the query; it just shows how to convert it and it is not tested. It will fail. You said you passed the table name as a parameter, but your function does not have a parameter table_name, the rewrite just shows how you would use it. Note the lines marked --<<<.

create or replace
 function public.countries_name(z           integer
                              , x           integer
                              , y           integer
                              , name_prefix text default 'B' )
    returns bytea           
    language plpgsql                                              --<<<                                                               
 as $$
declare 
    k_sql_base constant text =                                     --<<< Convert Query to a STRING
        -- Convert tile coordinates to a bounding box 
        $STMT$                                                     --<<< 
        with bounds as 
            (
               select
                ST_TileEnvelope(z
                , x
                , y) as geom
                ,
                (case
                    when z >= 16 then 5
                    when z = 15 then 50
                    when z = 14 then 100
                    when z = 13 then 150
                    when z = 12 then 200
                    when z <= 11 then 300
                    else 1
                end
               ) as simplify_tolerance
            )
        
        -- Convert raw geometry into MVT geometry
        -- Pull just the name in addition to the geometry
        -- Apply the name_prefix parameter to the WHERE clause
          ,  mvtgeom as 
            (
               select
                ST_AsMVTGeom(ST_Transform(ST_simplify(t.geom
                    , simplify_tolerance)
                    , 3857)
                    , bounds.geom) as geom
                    , t.fclass
                    , z
                    , x
                    , y
               from
                  %I  t                                          --<<<  place holder for TABLE_NAME
                , bounds
               where
                ST_Intersects(t.geom
                    , ST_Transform(bounds.geom
                    , 25833))
                and upper(t.fclass) like (upper(name_prefix) || '%')            
                and z >= 10
           )
        -- Serialize the result set into an MVT object
           select
                ST_AsMVT(mvtgeom, 'public.countries_name1')
             from
                mvtgeom;
        $STMT$;          
        --<<< 
    l_sql_statement text; 
   
    l_result bytea; 

begin    
    l_sql_statement = format(k_sql_base, table_name);        -- <<< fails as table_name NOT defined as parameter
    raise notice E'Executing SQL Statement:\n' || l_sql_statement;     -- show the statement to be executed
    execute l_sql_statement into l_result bytea;                       -- execute statement
    return l_result bytea;                                             -- return result
end ;        
$$