INSERT with dynamic table name in trigger function

by

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:

More Related Contents:

  1. Truncating all tables in a Postgres database
  2. Execute a dynamic crosstab query
  3. Dynamic SQL (EXECUTE) as condition for IF statement
  4. Test for null in function with varying parameters
  5. Return SETOF rows from PostgreSQL function
  6. Error when setting n_distinct using a plpgsql variable
  7. Dynamically generate columns for crosstab in PostgreSQL
  8. Function to loop through and select data from multiple tables
  9. ERROR: unterminated quoted string at or near
  10. Format specifier for integer variables in format() for EXECUTE?
  11. Table name as a PostgreSQL function parameter
  12. Define table and column names as arguments in a plpgsql function?
  13. dynamic sql query in postgres
  14. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  15. Is there a way to disable function overloading in Postgres
  16. Refresh a materialized view automatically using a rule or notify
  17. Can I make a plpgsql function return an integer without using a variable?
  18. PostgreSQL function returning multiple result sets
  19. SELECT raises exception in PL/pgSQL function
  20. DROP FUNCTION without knowing the number/type of parameters?
  21. Postgres FOR LOOP
  22. Postgresql insert trigger to set value
  23. SQL Sub queries in check constraint
  24. Cursor based records in PostgreSQL
  25. How can I send some http request from postgresql function or trigger
  26. Is there a way to disable updates/deletes but still allow triggers to perform them?
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. How do I temporarily disable triggers in PostgreSQL?

Leave a Comment