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.

cols sounds like you are trying to pass multiple column names. Is that so? How many can it be? Please show example calls. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 2, 2021 at 15:41
yes, I've updated the post with an example as you ask @ErwinBrandstetter the answer below of JGH works for me with just one column — Yoad
– Yoad, Commented May 3, 2021 at 10:21

Erwin Brandstetter · Accepted Answer · 2021-05-03 15:03:49Z

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

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:

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?)

JGH · Accepted Answer · 2021-05-02 14:42:52Z

0

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$;

answered May 2, 2021 at 14:42

JGH

18.2k5 gold badges40 silver badges57 bronze badges

3 Comments

Yoad Over a year ago

Hi, First, thank you for responding! The answer you gave me is partly working for me. It's working when cols is single-column input but when cols='col1,co2' the function fails with this error - ERROR: column \"{col1,col2}\" does not exist (SQLSTATE 42703). there is a way to "tell" the function that cols is not a single column?

JGH Over a year ago

@Yoad This answers your question as it was originally written. If you have a new question feel free to ask a new question in a new post.

Yoad Over a year ago

The original question was for columns so it's still the same question... I've added an example because I saw that maybe this is not so clear. if this is important for you I can re-ask the question in a new post

Collectives™ on Stack Overflow

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

2 Answers 2

What I would not do

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

What I would not do

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related