postgresql 在触发器函数中插入动态表名

INSERT with dynamic table name in trigger function

提问by sschober

I'm not sure how to achieve something like the following:

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

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;

I.e. inserting values into a table with a dynamically generated name.
Executing the code above yields:

即使用动态生成的名称将值插入到表中。
执行上面的代码产生：

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

It seems to suggest variables are not expanded/allowed as table names. I've found no reference to this in the Postgres manual.

似乎建议变量不被扩展/允许作为表名。我在 Postgres 手册中没有发现对此的引用。

I've already experimented with EXECUTElike so:

我已经尝试过EXECUTE这样的：

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

But no luck:

但没有运气：

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

The RECORDtype seems to be lost: OLD.*seems to be converted to a string and get's reparsed, leading to all sorts of type problems (e.g. NULLvalues).

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

Any ideas?

有任何想法吗？

回答by Erwin Brandstetter

PostgreSQL 9.1 or later

PostgreSQL 9.1 或更高版本

format()has a built-in way to escape identifiers. Simpler than before:

format()有一种内置的方法来转义标识符。比以前更简单：

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

   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Works with a VALUESexpression as well.

也适用于VALUES表达式。

db<>fiddle here
Old sqlfiddle.

db<>fiddle here
旧的sqlfiddle。

Major points

要点

• Use format()or quote_ident()to quote identifiers (automatically and only where necessary), thereby defending against SQL injectionand simple syntax violations.
  This is necessary, even with your own table names!
• Schema-qualify the table name. Depending on the current search_pathsettinga bare table name might otherwise resolve to another table of the same name in a different schema.
• Use EXECUTEfor dynamic DDL statements.
• Pass valuessafely with the USINGclause.
• Consult the fine manual on Executing Dynamic Commands in plpgsql.
• Note thatRETURN OLD;in the trigger function is required for a trigger BEFORE DELETE. Details in the manual here.

• 使用format()或quote_ident()引用标识符（自动且仅在必要时），从而防止SQL 注入和简单的语法违规。
  这是必要的，即使使用您自己的表名！
• 模式限定表名。根据当前search_path设置，裸表名可能会解析为不同模式中的另一个同名表。
• 使用EXECUTE动态DDL语句。
• 使用子句安全地传递值USING。
• 请参阅有关在 plpgsql 中执行动态命令的精美手册。
• 注意RETURN OLD;在触发器函数中需要一个触发器BEFORE DELETE。此处的手册中的详细信息。

You get the error messagein your almost successful version because OLDis not visibleinside EXECUTE. And if you want to concatenate individual values of the decomposed row like you tried, you have to prepare the text representation of every single column with quote_literal()to guarantee valid syntax. You would also have to knowcolumn names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...

你得到的错误信息在几乎成功的版本，因为OLD是不可见的内部EXECUTE。如果你想像你尝试的那样连接分解行的各个值，你必须准备每一列的文本表示quote_literal()以保证有效的语法。您还必须事先知道列名才能处理它们或查询系统目录 - 这与您拥有简单的动态触发器功能的想法背道而驰......

My solution avoids all these complications. Also simplified a bit.

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

PostgreSQL 9.0 or earlier

PostgreSQL 9.0 或更早版本

format()is not available, yet, so:

format()尚不可用，因此：

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

    RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Related:

有关的：

• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

• 如何在 PostgreSQL 8.2 中动态使用 TG_TABLE_NAME？

回答by robkorv

I just stumbled upon this because I was searching for a dynamic INSTEAD OF DELETEtrigger. As a thank you for the question and answers I'll post my solution for Postgres 9.3.

我只是偶然发现了这一点，因为我正在寻找一个动态INSTEAD OF DELETE触发器。为了感谢您的提问和回答，我将发布我的 Postgres 9.3 解决方案。

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = .id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$$ language plpgsql;