在plpgsql函数中将表和列名定义为参数？

It must be simple, but I'm making my first steps into Postgres functions and I can't find anything that works...

它必须简单，但我正在迈出Postgres函数的第一步，我找不到任何有效的东西......

I'd like to create a function that will modify a table and / or column and I can't find the right way of specifying my tables and columns as arguments in my function.

我想创建一个修改表和/或列的函数，我找不到在我的函数中指定我的表和列作为参数的正确方法。

Something like:

就像是：

CREATE OR REPLACE FUNCTION foo(t table)
RETURNS void AS $$
BEGIN
   alter table t add column c1 varchar(20);
   alter table t add column c2 varchar(20);
   alter table t add column c3 varchar(20);
   alter table t add column c4 varchar(20);
END;
$$ LANGUAGE PLPGSQL;

select foo(some_table)

In another case, I'd like to have a function that alters a certain column from a certain table:

在另一种情况下，我想要一个改变特定表中某个列的函数：

CREATE OR REPLACE FUNCTION foo(t table, c column)
RETURNS void AS $$
BEGIN
   UPDATE t SET c = "This is a test";
END;
$$ LANGUAGE PLPGSQL;

Is it possible to do that?

有可能吗？

1 个解决方案

#1

You must defend against SQL injection whenever you turn user input into code. That includes table and column names coming from system catalogs or from direct user input alike. This way you also prevent trivial exceptions with non-standard identifiers. There are basically three built-in methods:

每当您将用户输入转换为代码时，您必须防止SQL注入。这包括来自系统目录或来自直接用户输入的表名和列名。这样，您还可以防止使用非标准标识符的普通异常。基本上有三种内置方法：

1. format()

1st query, sanitized:

第一个查询，已消毒：

CREATE OR REPLACE FUNCTION foo(_t text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('
   ALTER TABLE %I ADD COLUMN c1 varchar(20)
                , ADD COLUMN c2 varchar(20)', _t);
END
$func$  LANGUAGE plpgsql;

format() requires Postgres 9.1 or later. Use it with the %I format specifier.

format（）需要Postgres 9.1或更高版本。将它与％I格式说明符一起使用。

The table name alone may be ambiguous. You may have to provide the schema name to avoid changing the wrong table by accident. Related:

仅表格名称可能不明确。您可能必须提供架构名称以避免意外更改错误的表。有关：

• INSERT with dynamic table name in trigger function
• 在触发器函数中使用动态表名INSERT
• How does the search_path influence identifier resolution and the "current schema"
• search_path如何影响标识符解析和“当前架构”

Aside: adding multiple columns with a single ALTER TABLE command is cheaper.

除此之外：使用单个ALTER TABLE命令添加多个列更便宜。

2. regclass

You can also use a cast to a registered class (regclass) for the special case of existing table names. Optionally schema-qualified. This fails immediately and gracefully for table names that are not be valid and visible to the calling user. The 1st query sanitized with a cast to regclass:

对于现有表名的特殊情况，您还可以使用强制转换为已注册的类（regclass）。可选择模式限定。对于对调用用户无效且可见的表名，这会立即且优雅地失败。第一个查询使用强制类型转换为regclass：

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||' ADD COLUMN c1 varchar(20)
                                 , ADD COLUMN c2 varchar(20)';
END
$func$  LANGUAGE plpgsql;

Call:

呼叫：

SELECT foo('table_name');

Or:

要么：

SELECT foo('my_schema.table_name'::regclass);

Aside: consider using just text instead of varchar(20).

除此之外：考虑使用text而不是varchar（20）。

3. quote_ident()

The 2nd query sanitized:

第二个查询已消毒：

CREATE OR REPLACE FUNCTION foo(_t regclass, _c text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'UPDATE '|| _t ||'   -- sanitized with regclass
            SET '|| quote_ident(_c) ||' = ''This is a test''';
END
$func$  LANGUAGE plpgsql;

For multiple concatenations / interpolations, format() is cleaner ...

对于多个连接/插值，format（）更清晰......

Related answers:

相关答案：

• Table name as a PostgreSQL function parameter
• 表名作为PostgreSQL函数参数
• Postgres functions vs prepared queries
• Postgres函数与准备好的查询

---

Case sensitive!

Be aware that unquoted identifiers are not cast to lower case here. When used as identifier in SQL Postgres casts to lower case automatically. But here we pass strings for dynamic SQL. When escaped as demonstrated, CaMel-case identifiers (like UserS) will be preserved by doublequoting ("UserS"), just like other non-standard names like "name with space" "SELECT"etc. Hence, names are case sensitive in this context.

请注意，此处不会将未加引号的标识符强制转换为小写。在SQL Postgres中用作标识符时自动转换为小写。但是在这里我们传递动态SQL的字符串。当演示转义时，CaMel案例标识符（如UserS）将通过双引号（“UserS”）保存，就像其他非标准名称一样，如“带空格的名称”，“SELECT”等。因此，在这种情况下，名称区分大小写。

My standing advice is to use legal lower case identifiers exclusively and never worry about that.

我的常设建议是专门使用合法的小写标识符，从不担心这一点。

Aside: single quotes for values, double quotes for identifiers.

旁白：值的单引号，标识符的双引号。