如何在 Postgres 函数中使用文本输入作为列名? [英] How to use text input as column name(s) in a Postgres function?

问题描述

我正在使用 Postgres 和 PostGIS.尝试编写一个根据给定参数选择特定列的函数.

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

我正在使用 WITH 语句创建结果表，然后将其转换为 bytea 返回.
我需要帮助的部分是 $4 部分.我尝试在下面演示和 $4::text 并且都给我返回输入的文本值而不是表中的列值如果 cols=name 所以我从查询名称而不是表中的实际名称返回.我也尝试了 data($4) 并得到了类型错误.
代码是这样的:

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

函数调用示例:

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

参数 cols 可以是来自 1 的多个列名，并且不受以上限制.在调用函数之前，将检查 cols 中列的名称，以确定它们是有效列.

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.

推荐答案

将多个列名作为串联字符串传递以进行动态执行迫切需要去污.我建议使用 VARIADIC 函数参数，使用正确引用的标识符(在这种情况下使用 quote_ident()):

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 这里

format() 的格式说明符 %I 处理单个标识符.您必须为多个标识符投入更多工作，尤其是对于可变数量的 0-n 标识符.此实现引用每个列名，并且仅在传递了任何列名时添加 ,.所以它适用于所有可能的输入，甚至根本没有输入.注意 VARIADIC cols text[] = NULL 作为最后一个输入参数，NULL 作为默认值:

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:

• PL/pgSQL 函数中的可选参数立>

相关:

• quote_ident() 没有为列名first"添加引号

在这种情况下，列名区分大小写！

Column names are case sensitive in this context!

举个例子(很重要！):

Call for your example (important!):

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

替代语法:

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

关于奇怪的第三列名称和 SQL 注入:

About that odd third column name and SQL injection:

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

关于VAIRADIC参数:

• 返回匹配元素的行plpgsql 函数中的输入数组
• 在单个参数中传递多个值

为简单起见，使用 OUT 参数.那完全是可选的.见:

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

• 从带有 OUT 参数的函数返回

如果您真的，真的相信输入始终是一个格式正确的 1 个或多个有效列名的列表 - 并且您断言 ...

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 ...

在调用函数之前会检查 cols 中的列的名称，它们是有效的列

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

您可以简化:

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

这篇关于如何在 Postgres 函数中使用文本输入作为列名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！