postgresql 将表名和列名定义为 plpgsql 函数中的参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18384159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Define table and column names as arguments in a plpgsql function?
提问by fgcartographix
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?
有可能这样做吗？
回答by Erwin Brandstetter
You must defend against SQL injectionwhenever 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 threebuilt-in methods:
每当您将用户输入转换为代码时，您都必须防范SQL 注入。这包括来自系统目录或直接用户输入的表名和列名。通过这种方式，您还可以防止使用非标准标识符的微不足道的异常。基本上有三种内置方法：
1. format()
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 %Iformat 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
- How does the search_path influence identifier resolution and the "current schema"
Aside: adding multiple columnswith a single ALTER TABLEcommandis cheaper.
另外：使用单个命令添加多个列ALTER TABLE更便宜。
2. regclass
2. regclass
You can also use a cast to a registered class (regclass) for the special case of existingtable 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 textinstead of varchar(20).
旁白：考虑使用 justtext代替varchar(20).
3. quote_ident()
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:
相关回答：
Case sensitive!
区分大小写！
Be aware that unquoted identifiers are notcast to lower case here. When used as identifier in SQL Postgres casts to lower case automatically. But here we pass stringsfor 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"）保留，就像其他非标准名称（如"name with space""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.
旁白：值的单引号，标识符的双引号。
