postgresql - Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql

Question

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.

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?

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?

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

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

Answer 1

You can use EXECUTE USING to pass NEW to it. Your example would be

EXECUTE 'INSERT INTO ' || TG_RELID || '::regclass SELECT $1' 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.)