postgresql - USING 部分在 EXECUTE 中被忽略...在 PL/pgSQL 中使用

Question

我尝试通过 EXECUTE..USING 动态生成一个序列，但在将参数传递给它时遇到问题。根据documentation , 我知道正确的形式应该是这样的:

CREATE OR REPLACE FUNCTION create_dyn_seq( /* some params */ ) 
RETURNS void AS $$
DECLARE
  _seq_name text;
  _min integer;
  _max integer;
BEGIN
  /*
    some code assigning the variables
    sample values:
    _seq_name := 'hu01/1/0_seq';
    _min := 101;
    _max := 500;
  */    
  EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE $1 MAXVALUE $2 ' 
  USING _min::INT, _max::INT;
  RETURN;
END;
$$ LANGUAGE plpgsql;

我添加了引号，_seq_name 本身似乎没问题。但是，当我插入这些值时，它会抛出以下错误:

ERROR:  syntax error at or near "$1"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE $1 MAXVALUE $2 

我还按照建议尝试了 EXECUTE 的替代语法 here :

EXECUTE 
  'CREATE SEQUENCE "' || _seq_name || '" MINVALUE ' || $1 || ' MAXVALUE ' || $2  
  USING _min::INT, _max::INT;

现在错误不同了:

ERROR:  syntax error at or near "hu01"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE hu01 MAXVALUE 1

现在可以翻译美元符号，但是 USING 部分中的变量会被部分 _seq_name 变量覆盖。我尝试用下划线替换序列名称中的斜杠，但没有任何改变。

我也按照建议通过 format() 结合 USING 进行了尝试 here ，但没有任何改变，出现相同的错误:

EXECUTE format('CREATE SEQUENCE %I MINVALUE $1 MAXVALUE $2 ', _seq_name) 
USING _min::INT, _max::INT;

EXECUTE format('CREATE SEQUENCE %I MINVALUE ' || $1 || ' MAXVALUE ' || $2, _nazev_seq) 
USING _min::INT, _max::INT;

然后我用 format() 参数完全替换了 USING 部分:

EXECUTE format('CREATE SEQUENCE %I MINVALUE %L MAXVALUE %L ', _seq_name, _min::INT, _max::INT);

现在我几乎到了我想去的地方，错误是不同的:

ERROR:  syntax error at or near "'101'"
LINE 1: CREATE SEQUENCE "hu01/1/0_seq" MINVALUE '101' MAXVALUE '500'...

我在“how to use integer within FORMAT()”上发现了一个问题，其中一个答案建议使用 %s。现在可以了:

EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s ', _seq_name, _min, _max);

然而，the other answer建议在 USING 部分中使用整数，如果它没有被窃听，我会这样做。我的“解决方案”感觉是一个肮脏的解决方法，我想做对，所以我的问题是:

为什么 USING 部分和美元符号转义值对我不起作用？

Answer 1

EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE $1 MAXVALUE $2 ' 
USING _min::INT, _max::INT;

这不会起作用，因为参数替换会起作用 only within SELECT, INSERT, UPDATE, and DELETE commands :

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. Only these statements has execution plan and only these statements should be parametrized.

EXECUTE 'CREATE SEQUENCE "' || _seq_name || '" MINVALUE ' || $1 || ' MAXVALUE ' || $2  
USING _min::INT, _max::INT;

这行不通，因为在那种情况下 $1 指的是 1st argument的功能。

EXECUTE format('CREATE SEQUENCE %I MINVALUE %L MAXVALUE %L ', _seq_name, _min::INT, _max::INT);

这行不通，因为 CREATE SEQUENCE 语句的 MINVALUE 和 MAXVALUE 选项只接受整数，不接受文本(& 没有隐式转换将在 ddl 中完成)。

EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s ', _seq_name, _min, _max);

只要 _min 和 _max 是某种类型的整数，这是绝对安全的。如果不是，请在此处使用显式转换。