表名作为PostgreSQL函数参数

小编典典

表名作为PostgreSQL函数参数

sql

我想在Postgres函数中将表名作为参数传递。我尝试了这段代码：

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

我得到了这个：

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

这是我更改为此时遇到的错误select * from quote_ident($1) tab where tab.id=1：

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

可能quote_ident($1)有效，因为如果没有where quote_ident($1).id=1得到的部分1，则表示已选定某项。为什么第一个quote_ident($1)工作和第二个工作不能同时进行？以及如何解决呢？

阅读 494

2021-04-14

共1个答案

小编典典

这可以进一步简化和改进：

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

使用架构限定名称进行调用（请参见下文）：

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

或者：

SELECT some_f('"my very uncommon table name"');

要点
使用OUT参数简化功能。您可以直接选择动态SQL的结果并完成。无需其他变量和代码。

EXISTS正是您想要的。您会得到true该行是否存在false。有多种方法可以做到这一点，EXISTS通常是最有效的。

您似乎想要返回一个整数，因此我将boolean结果从转换EXISTS为integer，得到的正是您所拥有的。我会返回布尔值。

我将对象标识符类型regclass用作的输入类型_tbl。那可以做所有quote_ident(_tbl)或format('%I', _tbl)可以做的事，但是更好，因为：

..它也可以防止SQL注入。

..如果表名无效/不存在/对当前用户不可见，它将立即失败，并且更正常地失败。（regclass参数仅适用于现有表。）

..它可与模式限定的表名配合使用，在这些表名中，纯格式quote_ident(_tbl)或format(%I)由于无法解决歧义性而可能会失败。您将必须分别传递和转义模式名称和表名称。

我仍然使用format()，因为它简化了语法（并演示了如何使用），但是使用%s代替%I。通常，查询更为复杂，因此format()可以提供更多帮助。对于简单的示例，我们还可以串联：

  EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'

列表中id只有一个表时，无需对列进行表限定FROM。在此示例中，没有任何歧义。（动态）内部的SQL命令EXECUTE具有单独的作用域，函数变量或参数在那里不可见-与函数主体中的普通SQL命令相反。

2021-04-14