将表名和列名定义为 plpgsql 函数中的参数? [英] Define table and column names as arguments in a plpgsql function?

问题描述

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

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

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

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.

类似于:

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;

能做到吗?

推荐答案

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

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:

第一个查询，已清理:

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

format() 需要 Postgres 9.1 或更高版本.将它与 %I 格式说明符一起使用.

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

单独的表名可能不明确.您可能必须提供架构名称以避免意外更改错误的表.相关:

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

• 在触发器函数中插入动态表名
• 如何search_path 是否影响标识符解析和当前模式"

旁白:添加多列ALTER TABLE 命令 更便宜.

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

对于现有表名的特殊情况，您还可以使用到注册类 (regclass) 的强制转换.可选模式限定.对于无效且对调用用户不可见的表名，这会立即且优雅地失败.第一个查询被转换为 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:

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

调用:

SELECT foo('table_name');

或者:

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

旁白:考虑仅使用 text 而不是varchar(20).

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

已清理的第二个查询:

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

对于多个连接/插值，format() 更干净......

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

相关答案:

• 作为 PostgreSQL 函数参数的表名
• Postgres 函数与准备好的查询

请注意，此处未加引号的标识符不会转换为小写.在 SQL 中用作标识符时 [Postgres 自动转换为小写][7].但是这里我们为动态 SQL 传递字符串.当按照演示进行转义时，CaMel-case 标识符(如 UserS)将通过双引号("UserS")保留，就像其他非标准名称如 带空格的名称" SELECT" 等.因此，在这种情况下，名称区分大小写.

Be aware that unquoted identifiers are not cast to lower case here. When used as identifier in SQL [Postgres casts to lower case automatically][7]. 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.

我的长期建议是只使用合法的小写标识符，不要担心.

另外:单引号用于值，双引号用于标识符.见:

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

• https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

这篇关于将表名和列名定义为 plpgsql 函数中的参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！