How to use text input as column name(s) in a Postgres function?

Question

I'm working with Postgres and PostGIS. Trying to write a function that that selects specific columns according to the given argument.

I'm using a WITH statement to create the result table before converting it to bytea to return.
The part I need help with is the $4 part. I tried it is demonstrated below and $4::text and both give me back the text value of the input and not the column value in the table if cols=name so I get back from the query name and not the actual names in the table. I also try data($4) and got type error.
The code is like this:

CREATE OR REPLACE FUNCTION select_by_txt(z integer,x integer,y integer, cols text)
        RETURNS bytea
        LANGUAGE 'plpgsql'
    
AS $BODY$
declare
res bytea;
begin
    WITH bounds AS (
      SELECT ST_TileEnvelope(z, x, y) AS geom
    ),
    mvtgeom AS (
      SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom, $4
      FROM table1 t, bounds
      WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
    )
    
    SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
    INTO res
    FROM mvtgeom;
    RETURN res;
end;
$BODY$;

Example for calling the function:

select_by_txt(10,32,33,"col1,col2")

The argument cols can be multiple column names from 1 and not limited from above. The names of the columns inside cols will be checked before calling the function that they are valid columns.

Answer 1

Passing multiple column names as concatenated string for dynamic execution urgently requires decontamination. I suggest a VARIADIC function parameter instead, with properly quoted identifiers (using quote_ident() in this case):

CREATE OR REPLACE FUNCTION select_by_txt(z int, x int, y int, VARIADIC cols text[] = NULL, OUT res text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
$$
SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
FROM  (
   SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom%s
   FROM   table1 t
   JOIN  (SELECT ST_TileEnvelope($1, $2, $3)) AS bounds(geom)
          ON ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
   ) mvtgeom
$$, (SELECT ', ' || string_agg(quote_ident (col), ', ') FROM unnest(cols) col)
   )
   INTO  res
   USING z, x, y;
END
$func$;

db<>fiddle here

The format specifier %I for format() deals with a single identifier. You have to put in more work for multiple identifiers, especially for a variable number of 0-n identifiers. This implementation quotes every single column name, and only add a , if any column names have been passed. So it works for every possible input, even no input at all. Note VARIADIC cols text[] = NULL as last input parameter with NULL as default value:

• Optional argument in PL/pgSQL function

Related:

• quote_ident() does not add quotes to column name "first"

Column names are case sensitive in this context!

Call for your example (important!):

SELECT select_by_txt(10,32,33,'col1', 'col2');

Alternative syntax:

SELECT select_by_txt(10,32,33, VARIADIC '{col1,col2}');

More revealing call, with a third column name and malicious (though futile) intent:

SELECT select_by_txt(10,32,33,'col1', 'col2', $$col3'); DROP TABLE table1;--$$);

About that odd third column name and SQL injection:

• https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables

About VAIRADIC parameters:

• Return rows matching elements of input array in plpgsql function
• Pass multiple values in single parameter

Using an OUT parameter for simplicity. That's totally optional. See:

• Returning from a function with OUT parameter

What I would not do

If you really, really trust the input to be a properly formatted list of 1 or more valid column names at all times - and you asserted that ...

the names of the columns inside cols will be checked before calling the function that they are valid columns

You could simplify:

CREATE OR REPLACE FUNCTION select_by_txt(z int, x int, y int, cols text, OUT res text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
$$
SELECT ST_AsMVT(mvtgeom, 'public.select_by_txt')
FROM  (
   SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom, %s
   FROM   table1 t
   JOIN  (SELECT ST_TileEnvelope($1, $2, $3)) AS bounds(geom)
          ON ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
   ) mvtgeom
$$, cols
   )
   INTO  res
   USING z, x, y;
END
$func$;

(How can you be so sure that the input will always be reliable?)

Answer 2

You would need to use a dynamic query:

CREATE OR REPLACE FUNCTION select_by_txt(z integer,x integer,y integer, cols text)
        RETURNS bytea
        LANGUAGE 'plpgsql'
    
AS $BODY$
declare
res bytea;
begin
EXECUTE format('
    WITH bounds AS (
      SELECT ST_TileEnvelope($1, $2, $3) AS geom
    ),
    mvtgeom AS (
      SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.geom) AS geom, %I
      FROM table1 t, bounds
      WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom, 4326))
    )
    
    SELECT ST_AsMVT(mvtgeom, ''public.select_by_txt'')
    FROM mvtgeom', cols)
    INTO res
    USING z,x,y;
    
    RETURN res;
end;
$BODY$;