表名作为 PostgreSQL 函数参数

Table name as a PostgreSQL function parameter

提问by John Doe

I want to pass a table name as a parameter in a Postgres function. I tried this code:

我想在 Postgres 函数中传递一个表名作为参数。我试过这个代码：

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

select some_f('table_name');

And I got this:

我得到了这个：

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

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

ERROR: syntax error at or near "."

And here is the error I got when changed to this select * from quote_ident($1) tab where tab.id=1:

这是我更改为这个时遇到的错误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() tab where tab.id...

Probably, quote_ident($1)works, because without the where quote_ident($1).id=1part I get 1, which means something is selected. Why may the first quote_ident($1)work and the second one not at the same time? And how could this be solved?

可能是quote_ident($1)有效的，因为没有where quote_ident($1).id=1我得到的部分1，这意味着选择了某些东西。为什么第一个quote_ident($1)和第二个不能同时工作？如何解决这个问题？

回答by Erwin Brandstetter

This can be further simplified and improved:

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

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

Call with schema-qualified name (see below):

使用模式限定名称调用（见下文）：

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

Or:

或者：

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

Major points

要点

• Use an OUTparameterto simplify the function. You can directly select the result of the dynamic SQL into it and be done. No need for additional variables and code.

• EXISTSdoes exactly what you want. You get trueif the row exists or falseotherwise. There are various ways to do this, EXISTSis typically most efficient.

• You seem to want an integerback, so I cast the booleanresult from EXISTSto integer, which yields exactly what you had. I would return booleaninstead.

• I use the object identifier type regclassas input type for _tbl. That does everything quote_ident(_tbl)or format('%I', _tbl)would do, but better, because:

  • .. it prevents SQL injectionjust as well.

  • .. it fails immediately and more gracefully if the table name is invalid / does not exist / is invisible to the current user. (A regclassparameter is only applicable for existingtables.)

  • .. it works with schema-qualified table names, where a plain quote_ident(_tbl)or format(%I)would fail because they cannot resolve the ambiguity. You would have to pass and escape schema and table names separately.

• I still use format(), because it simplifies the syntax (and to demonstrate how it's used), but with %sinstead of %I. Typically, queries are more complex so format()helps more. For the simple example we could as well just concatenate:

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

• No need to table-qualify the idcolumn while there is only a single table in the FROMlist. No ambiguity possible in this example. (Dynamic) SQL commands inside EXECUTEhave a separate scope, function variables or parameters are not visible there - as opposed to plain SQL commands in the function body.

• 使用OUT参数来简化函数。可以直接将动态SQL的结果选进去就大功告成了。不需要额外的变量和代码。

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

• 您似乎想要返回一个整数，因此我将boolean结果从EXISTSto 转换为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 命令相反。

Tested with PostgreSQL 9.1. format()requires at least that version.

使用 PostgreSQL 9.1 测试。format()至少需要那个版本。

Here's why you alwaysescape user input for dynamic SQL properly:

这就是为什么您总是正确转义动态 SQL 的用户输入的原因：

db<>fiddle heredemonstrating SQL injection.
Old sqlfiddle.

db<>fiddle这里演示 SQL 注入。
旧的sqlfiddle。

回答by ErikE

If at all possible, don't do this.

如果可能，请不要这样做。

That's the answer—it's an anti-pattern. If the client knows the table it wants data from, then SELECT FROM ThatTable. If a database is designed in a way that this is required, it seems to be designed sub-optimally. If a data access layer needs to know whether a value exists in a table, it is easy to compose SQL in that code, and pushing this code into the database is not good.

这就是答案——这是一种反模式。如果客户端知道它想要从中获取数据的表，那么SELECT FROM ThatTable. 如果数据库的设计方式是必需的，那么它的设计似乎是次优的。如果数据访问层需要知道某个值是否存在于某个表中，那么在该代码中编写 SQL 很容易，而将这些代码推送到数据库中是不好的。

To me this seems like installing a device inside an elevator where one can type in the number of the desired floor. After the Go button is pressed, it moves a mechanical hand over to the correct button for the desired floor and presses it. This introduces many potential issues.

对我来说，这就像在电梯内安装一个设备，可以输入所需楼层的号码。按下 Go 按钮后，它将机械手移到所需楼层的正确按钮上并按下它。这引入了许多潜在的问题。

Please note: there is no intention of mockery, here. My silly elevator example was *the very best device I could imagine* for succinctly pointing out issues with this technique. It adds a useless layer of indirection, moving table name choice from a caller space (using a robust and well-understood DSL, SQL) into a hybrid using obscure/bizarre server-side SQL code.

请注意：这里没有嘲讽的意思。我的愚蠢电梯示例是*我能想象到的最好的设备*，用于简洁地指出这种技术的问题。它添加了一个无用的间接层，将表名选择从调用者空间（使用健壮且易于理解的 DSL，SQL）移动到使用晦涩/奇怪的服务器端 SQL 代码的混合中。

Such responsibility-splitting through movement of query construction logic into dynamic SQL makes the code harder to understand. It violates a standard and reliable convention (how a SQL query chooses what to select) in the name of custom code fraught with potential for error.

这种通过将查询构造逻辑移动到动态 SQL 中的责任拆分使代码更难理解。它以充满潜在错误的自定义代码的名义违反了标准和可靠的约定（SQL 查询如何选择要选择的内容）。

Here are detailed points on some of the potential problems with this approach:

以下是有关此方法的一些潜在问题的详细说明：

• Dynamic SQL offers the possibility of SQL injection that is hard to recognize in the front end code or the back end code alone (one must inspect them together to see this).

• Stored procedures and functions can access resources that the SP/function owner has rights to but the caller doesn't. As far as I understand, without special care, then by default when you use code that produces dynamic SQL and runs it, the database executes the dynamic SQL under the rights of the caller. This means you either won't be able to use privileged objects at all, or you have to open them up to all clients, increasing the surface area of potential attack to privileged data. Setting the SP/function at creation time to always run as a particular user (in SQL Server, EXECUTE AS) may solve that problem, but makes things more complicated. This exacerbates the risk of SQL injection mentioned in the previous point, by making the dynamic SQL a very enticing attack vector.

• When a developer must understand what the application code is doing in order to modify it or fix a bug, he'll find it very difficult to get the exact SQL query being executed. SQL profiler can be used, but this takes special privileges and can have negative performance effects on production systems. The executed query can be logged by the SP but this increases complexity for questionable benefit (requiring accommodating new tables, purging old data, etc.) and is quite non-obvious. In fact, some applications are architected such that the developer does not have database credentials, so it becomes almost impossible for him to actually see the query being submitted.

• When an error occurs, such as when you try to select a table that doesn't exist, you'll get a message along the lines of "invalid object name" from the database. That will happen exactly the same whether you're composing the SQL in the back end or the database, but the difference is, some poor developer who's trying to troubleshoot the system has to spelunk one level deeper into yet another cave below the one where the problem exists, to dig into the wonder-procedure that Does It All to try to figure out what the problem is. Logs won't show "Error in GetWidget", it will show "Error in OneProcedureToRuleThemAllRunner". This abstraction will generally make a system worse.

• 动态 SQL 提供了在前端代码或后端代码中难以识别的 SQL 注入的可能性（必须一起检查它们才能看到这一点）。

• 存储过程和函数可以访问 SP/函数所有者有权访问但调用者没有权限的资源。据我了解，如果没有特别注意，那么默认情况下，当您使用生成动态 SQL 的代码并运行它时，数据库会在调用者的权限下执行动态 SQL。这意味着您要么根本无法使用特权对象，要么必须向所有客户端开放它们，从而增加了对特权数据的潜在攻击的表面积。在创建时将 SP/函数设置为始终以特定用户身份运行（在 SQL Server 中EXECUTE AS）可能会解决该问题，但会使事情变得更加复杂。这使得动态 SQL 成为一个非常诱人的攻击向量，从而加剧了前一点提到的 SQL 注入的风险。

• 当开发人员必须了解应用程序代码正在做什么以修改它或修复错误时，他会发现很难获得正在执行的确切 SQL 查询。可以使用 SQL 分析器，但这需要特殊权限，并且会对生产系统产生负面性能影响。执行的查询可以由 SP 记录，但这会增加复杂性，从而带来可疑的好处（需要容纳新表、清除旧数据等），并且非常不明显。事实上，某些应用程序的架构使得开发人员没有数据库凭据，因此他几乎不可能实际看到正在提交的查询。

• 当发生错误时，例如当您尝试选择一个不存在的表时，您会从数据库中收到一条类似“无效对象名称”的消息。无论您是在后端还是在数据库中编写 SQL，这都会发生完全相同的情况，但不同之处在于，一些试图对系统进行故障排除的可怜的开发人员必须深入挖掘一层更深的洞穴问题存在，深入研究“尽其所能”的奇迹程序，试图找出问题所在。日志不会显示“GetWidget 中的错误”，它会显示“OneProcedureToRuleThemAllRunner 中的错误”。这种抽象通常会使系统变得更糟。

An example in pseudo-C# of switching table names based on a parameter:

基于参数切换表名的伪C#示例：

string sql = $"SELECT * FROM {EscapeSqlIdentifier(tableName)};"
results = connection.Execute(sql);

While this does not eliminate every possible issue imaginable, the flaws I outlined with the other technique are absent from this example.

虽然这并不能消除可以想象的所有可能的问题，但我用其他技术概述的缺陷在这个例子中是不存在的。

回答by Daniel Vérité

Inside plpgsql code, The EXECUTEstatement must be used for queries in which table names or columns come from variables. Also the IF EXISTS (<query>)construct is not allowed when queryis dynamically generated.

在 plpgsql 代码中，EXECUTE语句必须用于表名或列来自变量的查询。另外，IF EXISTS (<query>)当构建体是不允许的query是动态生成的。

Here's your function with both problems fixed:

这是您修复了两个问题的函数：

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

回答by Matt

The first doesn't actually "work" in the sense that you mean, it works only in so far as it does not generate an error.

第一个实际上并不像您所说的那样“起作用”，它仅在不产生错误的情况下起作用。

Try SELECT * FROM quote_ident('table_that_does_not_exist');, and you will see why your function returns 1: the select is returning a table with one column (named quote_ident) with one row (the variable $1or in this particular case table_that_does_not_exist).

尝试SELECT * FROM quote_ident('table_that_does_not_exist');，您将看到为什么您的函数返回 1：选择返回一个表，其中包含一列（名为quote_ident）和一行（变量$1或在这种特殊情况下table_that_does_not_exist）。

What you want to do will require dynamic SQL, which is actually the place that the quote_*functions are meant to be used.

您想要做的将需要动态 SQL，这实际上quote_*是要使用这些函数的地方。

回答by Julien Feniou

If the question was to test if the table is empty or not (id=1), here is a simplified version of Erwin's stored proc :

如果问题是测试表是否为空 (id=1)，这里是 Erwin 存储过程的简化版本：

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

回答by Nathan Meyers

I know this is an old thread, but I ran across it recently when trying to solve the same problem - in my case, for some fairly complex scripts.

我知道这是一个旧线程，但我最近在尝试解决同样的问题时遇到了它 - 在我的情况下，对于一些相当复杂的脚本。

Turning the entire script into dynamic SQL is not ideal. It's tedious and error-prone work, and you lose the ability to parameterize: parameters must be interpolated into constants in the SQL, with bad consequences for performance and security.

将整个脚本变成动态 SQL 并不理想。这是一项乏味且容易出错的工作，而且您失去了参数化的能力：必须将参数插入到 SQL 中的常量中，这会对性能和安全性产生不良影响。

Here's a simple trick that lets you keep the SQL intact if you only need to select from your table - use dynamic SQL to create a temporary view:

这是一个简单的技巧，如果您只需要从表中进行选择，可以让您保持 SQL 不变 - 使用动态 SQL 创建一个临时视图：

CREATE OR REPLACE FUNCTION some_f(_tbl varchar) returns integer
AS $$
BEGIN
    drop view if exists myview;
    execute format('create temporary view myview as select * from %s', _tbl);
    -- now you can reference myview in the SQL
    IF EXISTS (select * from myview where myview.id=1) THEN
     return 1;
    END IF;
    return 0;
END;
$$ language plpgsql;

回答by Sandip Debnath

If you want table name, column name and value to be dynamically passed to function as parameter

如果您希望将表名、列名和值作为参数动态传递给函数

use this code

使用此代码

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value

回答by dm3

I have 9.4 version of PostgreSQL and I always use this code:

我有 9.4 版本的 PostgreSQL，我总是使用以下代码：

CREATE FUNCTION add_new_table(text) RETURNS void AS
$BODY$
begin
    execute
        'CREATE TABLE ' ||  || '(
        item_1      type,
        item_2      type
        )';
end;
$BODY$
LANGUAGE plpgsql

And then:

进而：

SELECT add_new_table('my_table_name');

It works good for me.

它对我很有用。

Attention!Above example is one of those which shows "How do not if we want to keep safety during querying the database" :P

注意力！上面的示例是其中一个显示“如果我们想在查询数据库期间保持安全，如何不这样做”：P