postgresql - 使用 regclass 的动态 SQL 的意外行为

Question

我在使用 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

好像第 3 次执行被缓存了，保存了表名。

有趣的是:如果我删除最后一行并执行它，到那时它会按预期工作，但我仍然需要最后一行(上面的代码)来执行:

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;

我在这里错过了什么？尤其是最后这句话？

Answer 1

对象标识符数据类型regclass内部是系统目录表pg_class的oid。您作为参数传递的字符串 'mytable' 在“方便转换”到 regclass 时立即解析为对象标识符。如果您稍后重命名该表，_t 将在下一次调用中解析为新名称。

• _t 在第 3 个 EXECUTE 中重命名为 mytable_bak。
• 错误发生在您的第 4 个 EXECUTE 中，其中 _t 被解析为 mytable_bak(正确!)并且您最终尝试重命名表mytable_bak_old - 如您在错误消息中所见。

在你开始你的命名游戏之前提取一次表名:

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;

在 Postgres 9.4 中测试并为我工作。

请注意，这仅适用于不需要双引号且在 search_path 中可见的合法小写表名。否则您会收到一条错误消息 - 您需要做更多的工作才能正确连接名称。不过，SQL 注入(inject)是不可能的。

或者只传递一个 text 字符串并在里面用 quote_ident() 转义它:

• SQL injection in Postgres functions vs prepared queries