为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

慕娘9325324 2019-09-13 11:06:02

在触发器函数中使用动态表名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值)。

有任何想法吗?


查看完整描述

2 回答

?
偶然的你

PostgreSQL 9.1或更高版本

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

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

   RETURN OLD;END$func$  LANGUAGE plpgsql;

也适用于VALUES表达式。

db <> 在这里摆弄
旧的sqlfiddle。

主要观点

  • 使用format()quote_ident()引用标识符(自动且仅在必要时),从而防止SQL注入和简单的语法违规。
    这是必要的,即使你自己的表名!

  • 模式限定表名。根据当前search_path设置,裸表名可能会解析为另一个模式中的另一个同名表。

  • 使用EXECUTE动态DDL语句。

  • 使用该子句安全地传递USING

  • 请参阅plpgsql中执行动态命令的精细手册。

  • 请注意,RETURN OLD;触发器需要触发功能BEFORE DELETE。手册中的详细信息在这里。

你得到的错误信息在几乎成功的版本,因为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_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;END$func$  LANGUAGE plpgsql;

有关:

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



查看完整回答
反对 回复 2019-09-16
?
123456qqq

我偶然发现了这个因为我正在寻找一个动态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 = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;END;$$ language plpgsql;



查看完整回答
反对 回复 2019-09-16
  • 2 回答
  • 0 关注
  • 384 浏览
我要回答
慕课专栏
更多

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信