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?

Solution 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?

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

Comments

[sschober]

Works like a charm. I guess i'll have some further reading to do :) There's a typo in your manual link, though. Can't correct it, becaus stackoverflow thinks one character edits are too minor :/

[sschober]

@Johan: Ah, didn't know that. So, i'll have to keep asking such great questions g!

[sschober]

Could you explain, what new concept or idea your solution is bringing to the table?

[robkorv]

Reading my post again, I see that I wasn't clear about the context of it. My post is for the one that is searching for a dynamically on delete trigger function. I was searching for this when Google showed this question as a hit.

[robkorv]

Originally i wanted to post it as a comment under the answer, but the code highlighting didn't work

[Siddharth Trikha]

@ErwinBrandstetter : my schema and table name are in upper case. So while executing this query i get error due to misplaced double-quotes EXECUTE format ('INSERT INTO %I SELECT $1.*', 'SCHEMA_NAME' || '.' || TG_TABLE_NAME || '_SHADOW') USING (row). Error is: ERROR: relation "SCHEMA_NAME.TABLE_NAME" does not exist

[Erwin Brandstetter]

@Siddharth: The schema is contained in a separate variable TG_TABLE_SCHEMA. It seems like you concatenated both before escaping properly. Consider: stackoverflow.com/a/10711349/939860. If the problem is still unclear, start a new question. You can always reference this answer for context and/or add a comment her linking to the related question.

[Erwin Brandstetter]

Important to note that this is only meant for INSTEAD OF DELETE triggers. I took the liberty to clarify accordingly.

[jian]

@ErwinBrandstetter Hi there. all the things I get it except USING OLD;. You said: "Pass values safely with the USING clause." can you further elaborate? I checked insert clause, there is no USING

[Erwin Brandstetter]

@Jian: USING is part of the EXECUTE syntax.