postgresql – 在触发器函数中使用动态表名的INSERT

发布时间：2020-12-13 16:43:09 所属栏目：百科来源：网络整理

导读：我不知道如何实现类似以下内容： 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; 也

我不知道如何实现类似以下内容：

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;

也就是说将值插入具有动态生成的名称的表。
执行上面的代码得到：

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,)

RECORD类型似乎丢失了：OLD。*似乎被转换为字符串，get被重新解析，导致各种类型的问题(例如NULL值)。

有任何想法吗？

PostgreSQL 9.1或更高版本

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

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

    RETURN OLD;
END
$func$ LANGUAGE plpgsql;

SQL Fiddle.
也适用于一个VALUES表达式。

要点

>使用format()或quote_ident()转义其他非法表名，并防止SQL injection。
是的，这是必要的，即使有你自己的表名！
>对动态DDL语句使用EXECUTE。
>使用USING子句安全传递值。
>请参阅Executing Dynamic Commands in plpgsql上的精细手册。
>注意：在示例中，触发器函数仅对触发器BEFORE DELETE有意义。 Details in the manual here.

您在几乎成功的版本中收到错误消息，因为OLD在EXECUTE内不可见。如果你想连接分解行的个别值，就像你试过的，你必须使用quote_literal()来准备每一个列的文本表示，以保证有效的语法。您还必须事先知道列名称来处理它们或查询系统目录 – 这违反了您的想法：拥有一个简单的动态触发器函数…

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

PostgreSQL 9.0或更早版本

format()不可用，但是：

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

    RETURN OLD;
END
$func$ LANGUAGE plpgsql;

有关：

> How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!