在触发函数中使用动态表名称进行插入 [英] 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:

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

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<>小提琴此处
旧的 sqlfiddle.

db<>fiddle here
Old 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屋！