在触发器函数中使用动态表名进行 INSERT

我不确定如何实现类似以下的目标：

CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
    DECLARE
        shadowname varchar := TG_TABLE_NAME || 'shadow';
    BEGIN
        INSERT INTO shadowname VALUES(OLD.*);
        RETURN OLD;
    END;
$$
LANGUAGE plpgsql;

IE。将值插入具有动态生成名称的表中。
执行上面的代码会得到：

ERROR:  relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)

这似乎表明变量没有扩展/允许作为表名。我在 Postgres 手册中没有找到对此的引用。

我已经尝试过EXECUTE像这样：

  EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;

但没有运气：

ERROR:  syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)

The RECORD类型似乎丢失了：OLD.*似乎被转换为字符串并重新解析，导致各种类型问题（例如NULL值）。

有任何想法吗？

现代 PostgreSQL

format() https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-OTHER有一种内置的方法来转义标识符。比以前更简单：

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$;

与一个VALUES https://www.postgresql.org/docs/current/sql-values.html表达也。

db<>fiddle
_{Old sqlfiddle http://sqlfiddle.com/#!17/ff78f/1}

主要观点

• Use format() https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-OTHER or quote_ident() https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-OTHER引用标识符（自动且仅在必要时），从而防御SQL注入 https://en.wikipedia.org/wiki/Sql_injection和简单的语法违规。
  这是必要的，即使有您自己的表名！
• 架构限定表名。取决于当前search_path setting https://stackoverflow.com/a/9067777/939860否则，裸表名称可能会解析为不同模式中同名的另一个表。
• Use EXECUTE用于动态 DDL 语句。
• Pass values安全地与USING clause.
• 查阅详细手册在 plpgsql 中执行动态命令 https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN.
• 注意RETURN OLD;在触发器函数中需要一个触发器BEFORE DELETE. 手册中有详细说明。 https://www.postgresql.org/docs/current/trigger-definition.html

你得到错误信息在你几乎成功的版本中，因为OLD is 不可见 inside EXECUTE。如果您想像您尝试的那样连接分解行的各个值，则必须使用以下方法准备每个列的文本表示quote_literal()以保证语法有效。你还必须know预先列名来处理它们或查询系统目录 - 这违背了您拥有简单的动态触发函数的想法......

我的解决方案避免了所有这些并发症。也简化了一点。

PostgreSQL 9.0 或更早版本

format()尚不可用，所以：

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$;

Related:

• 如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME？ https://stackoverflow.com/questions/7519044/how-to-dynamically-use-tg-table-name-in-postgresql-8-2/7617533#7617533