如何在触发功能中将NEW.*传递给EXECUTE [英] How to pass NEW.* to EXECUTE in trigger function

问题描述

我有一个简单的任务，就是将巨大的MD5值插入表(分区表)中，并创建了一个触发器以及一个触发器函数来代替INSERT操作.在函数中，我检查了NEW.md5的前两个字符，以确定应该插入哪个表.

I have a simple mission is inserting huge MD5 values into tables (partitioned table), and have created a trigger and also a trigger function to instead of INSERT operation. And in function I checked the first two characters of NEW.md5 to determine which table should be inserted.

DECLARE
  tb text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    tb = 'samples_' || left(NEW.md5, 2);
    EXECUTE(format('INSERT INTO %s VALUES (%s);', tb, NEW.*)); <- WRONG
  END IF;
  RETURN NULL;
END;

问题是如何将NEW.*连接到SQL语句中?

The question is how to concat the NEW.* into the SQL statement?

推荐答案

最好与 EXECUTE不需要括号.
而且您知道，除非在必要的地方加上引号(%I而不是format()中的%s)，否则标识符会折叠为小写.

And EXECUTE does not require parentheses.
And you are aware that identifiers are folded to lower case unless quoted where necessary (%I instead of %s in format()).

更多详细信息:

• 在触发函数中插入具有动态表名的
• 如何动态在PostgreSQL 8.2中使用TG_TABLE_NAME?

• INSERT with dynamic table name in trigger function
• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

这篇关于如何在触发功能中将NEW.*传递给EXECUTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！