Insert NEW. * From a common trigger using EXECUTE in PL / pgsql

Question

I have several tables that use the Postgres layout function. I want to define a common trigger BEFORE THE ROW IS BUILT for each table, which will be: 1) dynamically create the partition if the insert is against the parent table, and 2) re-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 record "NEW" is not displayed inside EXECUTE SQL. How can I make this work as simple as possible?

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

I thought about using a temporary 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 does not work due to the cached reference to the temporary table: Why do I get a "relation with OID ##### does not exist" when accessing temporary tables in PL / PgSQL functions?

I am using Postgres 8.2 and I cannot upgrade to another version.

EDIT:
As @alvherre noted, this can probably be done in Postgres 8.4 with EXECUTE ... USING syntax. Example: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers

Answer 1

I managed to get this to work by dynamically compiling a function that takes a new line 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); 

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

Although this is an ugly shred, it seems to do the job.

Although it looks like I can define every polymorphic variation in the front when I build the system, due to caching, I have to recompile this function whenever I create or delete a child table so that the function uses the latest version of RULE. p>

EDIT: Extra wrinkles
It worked a little with this technique: if this EXECUTE / PERFORM action was canceled on the first try due to another error (for example, in my case, the CHECK restriction failed), then the function containing this code seems to cache the rollback reference for partition_insert () created using EXECUTE and subsequent calls fail because there is no cached object.

I solved this by first creating stub versions of the function for each required table type parameter when I define the database.

Answer 2

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

 EXECUTE 'INSERT INTO ' || TG_RELID || '::regclass SELECT $1' USING NEW; 

(Note that I'm using TG_RELID cast from regclass, instead of messing around with TG_TABLE_SCHEMA and TABLE_NAME, because it is easier to use if it's non-standard. But then plpgsql is non-standard.)

Answer 3

Yes, you can use EXECUTE ... USE in 8.4. For example:

EXECUTE 'INSERT INTO ' || table_name || ' SELECT $1.*' USING NEW;

In lower versions (I only tested in 8.3) you can use:

 EXECUTE 'INSERT INTO ' || table_name || ' SELECT (' || quote_literal(NEW) || '::' || TG_RELID::regclass || ').*';