INSERT with dynamic table name in trigger function

Question

I'm not sure how to achieve something like the following:

CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
    DECLARE
        shadowname varchar := TG_TABLE_NAME || 'shadow';
    BEGIN
        INSERT INTO shadowname VALUES(OLD.*);
        RETURN OLD;
    END;
$$
LANGUAGE plpgsql;

I.e. inserting values into a table with a dynamically generated name.
Executing the code above yields:

ERROR:  relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)

It seems to suggest variables are not expanded/allowed as table names. I've found no reference to this in the Postgres manual.

I've already experimented with EXECUTE like so:

  EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;

But no luck:

ERROR:  syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)

The RECORD type seems to be lost: OLD.* seems to be converted to a string and get's reparsed, leading to all sorts of type problems (e.g. NULL values).

Any ideas?

Answer 1

Modern PostgreSQL

format() has a built-in way to escape identifiers. Simpler than before:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$;

Works with a VALUES expression as well.

db<>fiddle here
_{Old sqlfiddle}

Major points

• Use format() or quote_ident() to quote identifiers (automatically and only where necessary), thereby defending against SQL injection and simple syntax violations.
  This is necessary, even with your own table names!
• Schema-qualify the table name. Depending on the current search_path setting a bare table name might otherwise resolve to another table of the same name in a different schema.
• Use EXECUTE for dynamic DDL statements.
• Pass values safely with the USING clause.
• Consult the fine manual on Executing Dynamic Commands in plpgsql.
• Note thatRETURN OLD; in the trigger function is required for a trigger BEFORE DELETE. Details in the manual.

You get the error message in your almost successful version because OLD is not visible inside EXECUTE. And if you want to concatenate individual values of the decomposed row like you tried, you have to prepare the text representation of every single column with quote_literal() to guarantee valid syntax. You would also have to know column names beforehand to handle them or query the system catalogs - which stands against your idea of having a simple, dynamic trigger function ...

My solution avoids all these complications. Also simplified a bit.

PostgreSQL 9.0 or earlier

format() is not available, yet, so:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$;

Related:

• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

Answer 2

I just stumbled upon this because I was searching for a dynamic INSTEAD OF DELETE trigger. As a thank you for the question and answers I'll post my solution for Postgres 9.3.

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$$ language plpgsql;