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

前端之家收集整理的这篇文章主要介绍了postgresql – 在触发器函数中使用动态表名的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;

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

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?

猜你在找的Postgre SQL相关文章