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

Related later question with solution for Postgres 8.2: http://stackoverflow.com/q/7519044/939860 — Erwin Brandstetter, Nov 15 '15 at 22:58
@ErwinBrandstetter: Your solution in the related question is similar to how I solved this problem in my answer below but in this case, the function must be recompiled each time a new partition is added to the table or else the function won't be aware of the updated partition rules. — Adrian Pronk, Nov 16 '15 at 01:03

score 22 · Answer 1 · answered Jan 04 '10 at 03:49

22

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

answered Jan 04 '10 at 03:49

alvherre

2,499
20
22

Argh -- EXECUTE USING is new in Postgres 8.4. I didn't notice you specified 8.2. – alvherre Jan 04 '10 at 03:51
2

if using table name instead of relid, 'select $1' should be 'select $1.*' – Noah Yetter Sep 04 '12 at 18:07

score 3 · Answer 2 · edited Oct 02 '15 at 13:59

3

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

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

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

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

edited Oct 02 '15 at 13:59

botchniaque

4,698
3
35
63

answered Aug 09 '10 at 04:30

Tom Lanyon

31
1

Adrian Pronk · Accepted Answer · 2010-01-05T22:07:40.410

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

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.

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.

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

Interesting trick. Never heard of it. I suggest you schema-qualify the table name; failing to do it would cause this to fail in interesting ways when you have two tables with the same name on different schemas. Also you probably want to avoid the `CREATE OR REPLACE` when the function already exists, to keep catalog thrashing low. — alvherre, Jan 07 '10 at 01:28
@alvherre: You never heard of it because I just made it up :) I'm forced to recompile the function each time because I've altered the CREATE RULE ... ON INSERT DO INSTEAD rules and the old rules are cached with the function. Is it true that the only way to recompile the function is to CREATE OR REPLACE it? — Adrian Pronk, Jan 07 '10 at 11:37
...and yes, I do schema-qualify the name. I just left it out of the example to reduce noise. — Adrian Pronk, Jan 07 '10 at 11:39

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

3 Answers3

Linked