title: Inserting NEW.* from a generic trigger using EXECUTE in PL/pgsql
body: <p>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.</p>
  
  <p>Something like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But the "NEW" record isn't visible inside the EXECUTE SQL.  How can I make this work as simply as possible?</p>
  
  <p>As an alternative, can I iterate over the fields in the NEW record somehow?</p>
  
  <p>I've thought of using a temp-table:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>But this also doesn't work because of the cached reference to a temp-table: <a href="http://wiki.postgresql.org/wiki/FAQ#Why_do_I_get_.22relation_with_OID_.23.23.23.23.23_does_not_exist.22_errors_when_accessing_temporary_tables_in_PL.2FPgSQL_functions.3F" rel="noreferrer">Why do I get "relation with OID ##### does not exist" errors when accessing temporary tables in PL/PgSQL functions?</a></p>
  
  <p>I'm using Postgres 8.2 and I can't change to any other version.</p>
  
  <p><strong>EDIT:</strong><br>
  As @alvherre pointed out, this can probably be done in Postgres 8.4 with the EXECUTE ... USING syntax.  See an example at <a href="http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers" rel="noreferrer">http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers</a></p>