sql - 在 plpgsql 中，如何使用可变数量的标识符创建和执行命令字符串？

Question

在 postgres 9.1 中，我想创建一个函数，它接受一个索引名称、一个表名称和可变数量的列，构建一个索引，然后做一些其他事情。

我目前的做法是使用plpgsql，构造一个动态命令来执行。但是，当我尝试使用 quote_ident 来保护所有标识符时，我被绊倒了。到目前为止的代码:

CREATE OR REPLACE FUNCTION my_create_index(indexname text, tablename text, VARIADIC arr text[]) RETURNS void AS $$
  DECLARE
    command_string text;
  BEGIN
    command_string := 'CREATE INDEX ' || quote_ident(indexname) || ' ON ' ||
         quote_ident(tablename) || ' (' ||
         format(repeat('%I ', array_length($3, 1)), VARIADIC $3) ||
         ')';

    -- display the string
    RAISE NOTICE '%', command_string;

    -- execute the string
    EXECUTE command_string;

    -- (do other stuff)
  END;
$$ LANGUAGE plpgsql;

代码似乎在传递一个列名时成功，但在传递两个或更多列时出现以下错误:

ERROR:  too few arguments for format

我做错了什么？ (大概是 format 或我对 VARIADIC 的使用。)

谢谢!

Answer 1

您通常不会同时使用 format 和 quote_ident。 format 足够聪明来做标识符引用它自己；您在表达式的一部分中展示了自己，同时在其他地方使用了不必要的连接和 quote_ident 调用。

但是，我发现您在使用 VARIADIC 数组参数调用 format 时遇到了同样的问题，我怀疑您发现了错误。

在我发现发生了什么之前，这里有一个解决方法:

command_string := format('CREATE INDEX %I ON %I (%s)', 
    indexname, tablename, (
      SELECT string_agg(quote_ident(x), ', ') 
      FROM unnest($3) x
    )
);

请注意，在您的原始代码中，'%I ' 应该是 '%I, '。

是的，错误已确认，并且 it's been reported before但看起来它从未被检查过。 concat 和 concat_ws 中存在相同的错误。这些函数无法检查 VARIADIC 参数标志。

观察:

regress=> SELECT format('%I', VARIADIC ARRAY['b','c','d']);
  format   
-----------
 "{b,c,d}"
(1 row)

regress=> SELECT format('%I', 'b','c','d');
 format 
--------
 b
(1 row)

regress=> SELECT format('%I, %I, %I', VARIADIC ARRAY['b','c','d']);
ERROR:  too few arguments for format

我能想到的唯一解决方法是避免它们(如上所示)或使用 EXECUTE 动态构造函数调用。在 format 的情况下，这完全是多余的，所以我只是将上面的子查询与 unnest 一起使用。

我会尝试在 PostgreSQL 邮件列表上追查这个问题，但下周我会非常忙，所以如果你不这样做，请戳我或在 pgsql-bugs 上发帖，并自己链接回这个问题下周中期之前看不到跟进。