在触发器函数中插入动态表名 [英] INSERT with dynamic table name in trigger function

问题描述

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

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.*)

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

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

我已经像这样尝试过 EXECUTE:

I've already experimented with EXECUTE like so:

  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 值).

The RECORD type 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. NULL values).

有什么想法吗?

推荐答案

PostgreSQL 9.1 或更高版本

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

PostgreSQL 9.1 or later

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

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 表达式

Works with a VALUES expression as well.

db<>fiddle 这里
旧的 sqlfiddle.

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

• Use format() or quote_ident() to quote identifiers (automatically and only where necessary), thereby defending against SQL injection and simple syntax violations.
  This is necessary, even with your own table names!
• Schema-qualify the table name. Depending on the current search_path setting a bare table name might otherwise resolve to another table of the same name in a different schema.
• Use EXECUTE for dynamic DDL statements.
• Pass values safely with the USING clause.
• 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.

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

You get the error message in your almost successful version because OLD is not visible inside 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 know column names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...

我的解决方案避免了所有这些复杂情况.也简化了一点.

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

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 吗?

这篇关于在触发器函数中插入动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！