在PL/pgsql中使用EXECUTE从通用触发器插入NEW.* [英] Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql

问题描述

我有许多使用Postgres分区"功能的表.我想在每个表上定义一个通用的BEFORE INSERT OF ROW触发器，该触发器将1)在对父表进行插入时动态创建分区，以及2)对分区重新执行插入操作.

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.

类似的东西:

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

但是在EXECUTE SQL中看不到"NEW"记录.如何使这项工作尽可能简单?

But the "NEW" record isn't visible inside the EXECUTE SQL. How can I make this work as simply as possible?

或者，我可以以某种方式遍历NEW记录中的字段吗?

As an alternative, can I iterate over the fields in the NEW record somehow?

我已经考虑过使用临时表:

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?

我正在使用Postgres 8.2，并且不能更改为任何其他版本.

I'm using Postgres 8.2 and I can't change to any other version.

正如@alvherre指出的那样，这可能可以在Postgres 8.4中使用EXECUTE ... USING语法完成.在 http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers 上查看示例.

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

推荐答案

我设法通过动态编译一个接受NEW行作为参数的函数来使其工作:

I've managed to get this to work by dynamically compiling a function that accepts the NEW row as a parameter:

    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);

由于Postgres函数是多态的，因此将为每个使用此触发器的表生成一个不同的函数.

As Postgres functions are polymorphic, this will generate a different function for each table that uses this trigger.

尽管丑陋，但似乎可以胜任.

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.

编辑:其他皱纹
这项技术有一点陷阱:如果由于另一个错误(例如，在我的情况下为CHECK约束失败)而在第一次尝试时将EXECUTE/PERFORM操作回滚，则包含此代码的函数似乎会缓存对由于找不到缓存对象，因此使用EXECUTE创建的回滚partition_insert()函数和后续调用均失败.

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.

我通过在定义数据库时为每个必需的表类型参数创建函数的存根版本来解决此问题.

I resolved this by pre-creating stub versions of the function for each required table-type parameter when I define the database.

这篇关于在PL/pgsql中使用EXECUTE从通用触发器插入NEW.*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！