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

发布时间：2020-12-12 08:46:10 所属栏目：MsSql教程来源：网络整理

导读：它必须很简单,但我正在将Postgres函数中的第一个步骤,我找不到任何有用的东西… 我想创建一个修改表和/或列的函数,我找不到正确的方式将我的表和列指定为我的函数中的参数. 就像是： CREATE OR REPLACE FUNCTION foo(t table)RETURNS void AS $$BEGIN alter t

它必须很简单,但我正在将Postgres函数中的第一个步骤,我找不到任何有用的东西…

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

就像是：

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)

在另一种情况下,我想要一个功能,从某个表改变某列：

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 injection.这包括表和列名称,无论它们是来自系统目录还是来自用户输入.这样你也可以避免使用非法标识符的琐碎异常.基本上有三种内置方法：

第一个查询,消毒为format()：

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()需要Postgres 9.1或更高版本.与％I格式说明符一起使用.

另外,添加multiple columns with a single ALTER TABLE command更便宜.

第一次查询,消毒,投放到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;

呼叫：

SELECT foo('table_name');

要么：

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

另外：考虑使用text instead of varchar(20).

第二个查询,消毒为quote_ident()

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;

还有：single quotes for values,double quotes for identifiers.

区分大小写！

请注意,此处不会将标识符转换为小写.在SQL中用作标识符Postgres casts all unquoted identifiers to lower case automatically.但是在这里我们传递动态SQL的字符串.如上所示,如同上述所示,CaMel-case标识符(如UserS)将被双引号(“UserS”)保留,就像其他非法形式(“name with space”,“SELECT”,…)一样.因此,这些字符串是有区分大小写的.

我的立场建议是专门使用合法的小写标识符,而不用担心.