猿问
下载APP

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

在触发器函数中使用动态表名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.*似乎被转换为字符串,并得到的重新解析,导致各种各样的类型的问题(例如NULL值)。

有任何想法吗?


慕娘9325324
浏览 52回答 2
2回答

偶然的你

PostgreSQL 9.1或更高版本format()有一个内置的方法来转义标识符。比以前简单:CREATE&nbsp;OR&nbsp;REPLACE&nbsp;FUNCTION&nbsp;foo_before() &nbsp;&nbsp;RETURNS&nbsp;trigger&nbsp;AS$func$BEGIN &nbsp;&nbsp;&nbsp;EXECUTE&nbsp;format('INSERT&nbsp;INTO&nbsp;%I.%I&nbsp;SELECT&nbsp;$1.*' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;TG_TABLE_SCHEMA,&nbsp;TG_TABLE_NAME&nbsp;||&nbsp;'shadow') &nbsp;&nbsp;&nbsp;USING&nbsp;OLD; &nbsp;&nbsp;&nbsp;RETURN&nbsp;OLD;END$func$&nbsp;&nbsp;LANGUAGE&nbsp;plpgsql;也适用于VALUES表达式。db <>&nbsp;在这里摆弄旧的sqlfiddle。主要观点使用format()或quote_ident()引用标识符(自动且仅在必要时),从而防止SQL注入和简单的语法违规。这是必要的,即使你自己的表名!模式限定表名。根据当前search_path设置,裸表名可能会解析为另一个模式中的另一个同名表。使用EXECUTE动态DDL语句。使用该子句安全地传递值USING。请参阅plpgsql中执行动态命令的精细手册。请注意,RETURN OLD;触发器需要触发功能BEFORE DELETE。手册中的详细信息在这里。你得到的错误信息在几乎成功的版本,因为OLD是不可见的内部EXECUTE。如果您想要像您尝试的那样连接分解行的各个值,则必须准备每个列的文本表示quote_literal()以保证有效的语法。您还必须事先知道列名称来处理它们或查询系统目录 - 这与您想要拥有简单的动态触发器功能相违背......我的解决方案避免了所有这些并发症&nbsp;也简化了一下。PostgreSQL 9.0或更早版本format()&nbsp;尚不可用,所以:CREATE&nbsp;OR&nbsp;REPLACE&nbsp;FUNCTION&nbsp;foo_before() &nbsp;&nbsp;RETURNS&nbsp;trigger&nbsp;AS$func$BEGIN &nbsp;&nbsp;&nbsp;&nbsp;EXECUTE&nbsp;'INSERT&nbsp;INTO&nbsp;'&nbsp;||&nbsp;quote_ident(TG_TABLE_SCHEMA) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;||&nbsp;'.'&nbsp;||&nbsp;quote_ident(TG_TABLE_NAME&nbsp;||&nbsp;'shadow') &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;||&nbsp;'&nbsp;SELECT&nbsp;$1.*' &nbsp;&nbsp;&nbsp;&nbsp;USING&nbsp;OLD; &nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;OLD;END$func$&nbsp;&nbsp;LANGUAGE&nbsp;plpgsql;有关:如何在PostgreSQL 8.2中动态使用TG_TABLE_NAME?

123456qqq

我偶然发现了这个因为我正在寻找一个动态INSTEAD OF DELETE触发器。感谢您提出问题和答案,我将发布Postgres 9.3的解决方案。CREATE&nbsp;OR&nbsp;REPLACE&nbsp;FUNCTION&nbsp;set_deleted_instead_of_delete()RETURNS&nbsp;TRIGGER&nbsp;AS&nbsp;$$BEGIN &nbsp;&nbsp;&nbsp;&nbsp;EXECUTE&nbsp;format('UPDATE&nbsp;%I&nbsp;set&nbsp;deleted&nbsp;=&nbsp;now()&nbsp;WHERE&nbsp;id&nbsp;=&nbsp;$1.id',&nbsp;TG_TABLE_NAME) &nbsp;&nbsp;&nbsp;&nbsp;USING&nbsp;OLD; &nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;NULL;END;$$&nbsp;language&nbsp;plpgsql;
打开App,查看更多内容
随时随地看视频慕课网APP
我要回答