INSERT with dynamic table name in trigger function

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;

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?