postgresql - 使用regclass的动态SQL的异常行为

我在使用EXECUTE和regclass时有一些奇怪的行为，我正在尝试调试它，需要一些帮助。
基本上，我试图在一个函数中运行这些SQL语句：

ALTER TABLE mytable_bak RENAME TO mytable_old;
TRUNCATE TABLE mytable_old;
ALTER TABLE mytable RENAME TO mytable_bak;
ALTER TABLE mytable_old RENAME TO mytable;

这是我的功能（没有按预期工作）：

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||'_bak RENAME TO '|| _t || '_old';
   EXECUTE 'TRUNCATE TABLE '|| _t || '_old';
   EXECUTE 'ALTER TABLE '|| _t ||' RENAME TO '|| _t || '_bak';
   EXECUTE 'ALTER TABLE '|| _t ||'_old RENAME TO '|| _t;
END
$func$ LANGUAGE plpgsql;

它不喜欢我执行的最后一行：

EXECUTE 'ALTER TABLE '|| _t ||'_old RENAME TO '|| _t;

例如：

foo_bar_12345=> select foo('mytable');
ERROR:  relation "mytable_bak_old" does not exist
CONTEXT:  SQL statement "ALTER TABLE mytable_bak_old RENAME TO mytable_bak"
PL/pgSQL function foo(regclass) line 6 at EXECUTE statement

就好像缓存了第三个execute，保存表名一样。
有趣的是：如果我删除最后一行并执行它，它将按预期工作，但我仍然需要最后一行（上面的代码）来执行：

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||'_bak RENAME TO '|| _t || '_old';
   EXECUTE 'TRUNCATE TABLE '|| _t || '_old';
   EXECUTE 'ALTER TABLE '|| _t ||' RENAME TO '|| _t || '_bak';
END
$func$ LANGUAGE plpgsql;

我错过了什么？尤其是最后一句话？

最佳答案

对象标识符数据类型内部是系统编目表的oid。作为参数传递的字符串regclass在“便利转换”中立即解析为对象标识符pg_class。如果以后重命名表，'mytable'将在下一次调用中解析为新名称。
regclass在第三个_t中重命名为_t。
错误发生在第4个mytable_bak中，其中EXECUTE被解析为EXECUTE（正确！）最后，您试图重命名一个表_t-正如您在错误消息中看到的那样。
在开始命名字符之前提取一次表名：

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
DECLARE
  _tbl text := _t::text;  -- "early binding"
BEGIN
   EXECUTE format('ALTER TABLE %I_bak RENAME TO %1$s_old', _tbl);
   EXECUTE 'TRUNCATE TABLE ' || _tbl || '_old';
   EXECUTE format('ALTER TABLE %1$s RENAME TO %1$s_bak', _tbl);
   EXECUTE format('ALTER TABLE %1$s_old RENAME TO %1$s', _tbl);
END
$func$ LANGUAGE plpgsql;

我在9.4级的博士后考试和工作。
注意，这只适用于不需要双引号且在mytable_bak中可见的合法小写表名。否则您将收到一条错误消息-您需要做更多工作才能正确连接名称。不过，SQL注入是不可能的。
或者只需传递一个mytable_bak_old字符串并用search_path内部转义它：
text

关于postgresql - 使用regclass的动态SQL的异常行为，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31041220/