INSERT with a dynamic table name in a 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; 

those. Insert values ​​into a table with a dynamically generated name.
Executing the above code gives:

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

Variables do not seem to expand / resolve, like table names. I did not find a link to this in the Postgres manual.

I have already experimented with EXECUTE as follows:

  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 gets repaired, which leads to different types of problems (for example, NULL values).

Any ideas?

Answer 1

PostgreSQL 9.1 or later

format() has a built-in way of escaping identifiers. Easier than before:

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

Works with a VALUES expression.

db <> fiddle here
Old sqlfiddle.

Highlights

• Use format() or quote_ident() for quote_ident() in identifier quotes (automatically and only if necessary), thereby protecting against SQL injection and simple syntax violations.
  This is necessary, even with your table names!
• Schema-qualify table name. Depending on the current search_path parameter, search_path empty table name may otherwise search_path into another table with the same name in a different schema.
• Use EXECUTE for dynamic DDL statements.
• Pass values ​​securely with the USING .
• See the excellent dynamic command execution guide in plpgsql .
• Please note that RETURN OLD; The trigger requires a function for the BEFORE DELETE trigger. Details in the manual here.

You get an error message in your almost successful version because the OLD not visible inside EXECUTE . And if you want to combine the individual values ​​of the expanded row as you tried, you must prepare a textual representation of each individual column with quote_literal() to guarantee the correct syntax. You would also need to know the column names in advance in order to process them or query system directories - which contradicts your idea of ​​having a simple, dynamic trigger function ...

My decision avoids all these complications. Also a little simplified.

PostgreSQL 9.0 or earlier

format() is not yet available, therefore:

 CREATE OR REPLACE FUNCTION foo_before() RETURNS trigger 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$ LANGUAGE plpgsql; 

Connected with:

• How to dynamically use TG_TABLE_NAME in PostgreSQL 8.2?

Answer 2

I just stumbled upon this because I was looking for a dynamic INSTEAD OF DELETE trigger. As a thank you for the question and answers, I will 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;