postgresql 在 PL/pgsql 中使用 EXECUTE 从通用触发器插入 NEW.*
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1997337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql
提问by Adrian Pronk
I have a number of tables that use the Postgres "Partitioning" feature. I want to define a common BEFORE INSERT OF ROW trigger on each table that will 1) dynamically create the partition should the insert occur against the parent table and 2) re-execute the insert against the partition.
我有许多使用 Postgres“分区”功能的表。我想在每个表上定义一个通用的 BEFORE INSERT OF ROW 触发器，它将 1) 在对父表进行插入时动态创建分区，以及 2) 对分区重新执行插入。
Something like:
就像是：
CREATE OR REPLACE FUNCTION partition_insert_redirect( )
RETURNS trigger AS $BODY$
BEGIN
... create the new partition and set up the redirect Rules ...
/* Redo the INSERT dynamically. The new RULE will redirect it to the child table */
EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
' SELECT NEW.*'
END
But the "NEW" record isn't visible inside the EXECUTE SQL. How can I make this work as simply as possible?
但是“新”记录在 EXECUTE SQL 中不可见。我怎样才能使这项工作尽可能简单？
As an alternative, can I iterate over the fields in the NEW record somehow?
作为替代方案，我可以以某种方式遍历 NEW 记录中的字段吗？
I've thought of using a temp-table:
我想过使用临时表：
EXECUTE 'CREATE TEMPORARY TABLE new_row (LIKE ' ||
quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
') ON COMMIT DROP';
INSERT INTO new_row SELECT NEW.*;
EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME) ||
' SELECT * FROM new_row';
DROP TABLE new_row;
But this also doesn't work because of the cached reference to a temp-table: Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?
但这也不起作用，因为缓存了对临时表的引用：为什么在访问 PL/PgSQL 函数中的临时表时会出现“与 OID ##### 的关系不存在”错误？
I'm using Postgres 8.2 and I can't change to any other version.
我使用的是 Postgres 8.2，我无法更改为任何其他版本。
EDIT:
As @alvherre pointed out, this can probably be done in Postgres 8.4 with the EXECUTE ... USING syntax. See an example at http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
编辑：
正如@alvherre 所指出的，这可能可以在 Postgres 8.4 中使用 EXECUTE ... USING 语法完成。在http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers查看示例
采纳答案by Adrian Pronk
I've managed to get this to work by dynamically compiling a function that accepts the NEW row as a parameter:
我设法通过动态编译一个接受 NEW 行作为参数的函数来使其工作：
EXECUTE 'create or replace function partition_insert(r ' || TG_TABLE_NAME || ') RETURNS void AS $FUNC$' ||
'BEGIN ' ||
'insert into ' || TG_TABLE_NAME || ' SELECT r.*; ' ||
'END $FUNC$ LANGUAGE plpgsql VOLATILE';
PERFORM partition_insert(NEW);
As Postgres functions are polymorphic, this will generate a different function for each table that uses this trigger.
由于 Postgres 函数是多态的，这将为使用此触发器的每个表生成不同的函数。
Despite being an ugly kludge, this seems to do the job.
尽管是一个丑陋的杂种，但这似乎可以完成工作。
Although it looks like I could define each polymorphic variation up front when I build the system, because of caching, I must recompile the function whenever I create or drop a child table so that the function uses the latest insert RULE.
虽然看起来我可以在构建系统时预先定义每个多态变体，但由于缓存的原因，每当我创建或删除子表时，我都必须重新编译该函数，以便该函数使用最新的插入规则。
EDIT:Additional wrinkles
There's a little gotcha with this technique: If this EXECUTE/PERFORM action is rolled-back on the first attempt due to another error (for example, in my case a CHECK constraint failure) then the function containing this code seems to cache a reference to the rolled-back partition_insert() function it created using the EXECUTE and subsequent calls fail due to a cached object not being found.
编辑：额外的皱纹
这种技术有一个小问题：如果这个 EXECUTE/PERFORM 操作在第一次尝试时由于另一个错误（例如，在我的情况下是 CHECK 约束失败）而回滚，那么包含此代码的函数似乎缓存对它使用 EXECUTE 创建的回滚 partition_insert() 函数的引用，随后的调用由于找不到缓存对象而失败。
I resolved this by pre-creating stub versions of the function for each required table-type parameter when I define the database.
我通过在定义数据库时为每个必需的表类型参数预先创建函数的存根版本来解决这个问题。
回答by alvherre
You can use EXECUTE USINGto pass NEW to it. Your example would be
您可以使用EXECUTE USING将 NEW 传递给它。你的例子是
EXECUTE 'INSERT INTO ' || TG_RELID || '::regclass SELECT ' USING NEW;
(Note that I use TG_RELID casted to regclass instead of fiddling with TG_TABLE_SCHEMA and TABLE_NAME because it is easier to use, if nonstandard. But then, plpgsql is nonstandard anyway.)
（请注意，我使用 TG_RELID 转换为 regclass 而不是摆弄 TG_TABLE_SCHEMA 和 TABLE_NAME 因为它更易于使用，如果是非标准的。但是，无论如何，plpgsql 是非标准的。）
回答by Tom Lanyon
Yes, you can use EXECUTE ... USING in 8.4. For example:
是的，您可以在 8.4 中使用 EXECUTE ... USING。例如：
EXECUTE 'INSERT INTO ' || table_name || ' SELECT $1.*' USING NEW;
EXECUTE 'INSERT INTO ' || table_name || ' SELECT $1.*' USING NEW;
In lower versions (I've only tested in 8.3), you can use:
在较低版本中（我只在 8.3 中测试过），您可以使用：
EXECUTE 'INSERT INTO ' || table_name ||
' SELECT (' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').*';
