Execute String Postgres

Question

I am having trouble getting this code to work. This is executed as a result of a insert, update or delete trigger. The same code is going to be called from several table triggers. Therefore, I don't know pre-hand what the table names are or what their columns are. Any ideas?

EXECUTE format('INSERT INTO %s VALUES %s', _tbl, (OLD).*);

Error is about the strings not being surrounded with quotes:

ERROR:  column "bangor" does not exist
LINE 1: INSERT INTO sample_test_table_deletes VALUES (3,bangor,D,"20...
                                                          ^

On the other hand, the following works:

INSERT INTO my_table VALUES((OLD).*);

That is, when the query is not through an execute block.

Update (Trigger Code):

CREATE OR REPLACE FUNCTION sample_trigger_func() RETURNS TRIGGER AS $$
DECLARE
    operation_code char;
    table_name varchar(50);
    delete_table_name varchar(50);
    old_id integer; 

BEGIN
    table_name = TG_TABLE_NAME;
    delete_table_name = TG_TABLE_NAME || '_deletes';

    SELECT SUBSTR(TG_OP, 1, 1)::CHAR INTO operation_code;

    IF TG_OP = 'DELETE' THEN
        OLD.mod_op = operation_code;
        OLD.mod_date = now();

        RAISE INFO 'OLD: %', (OLD).name;

        EXECUTE format('INSERT INTO %s VALUES %s', delete_table_name, (OLD).*);

    ELSE
        EXECUTE format('UPDATE TABLE %s SET mod_op = %s AND mod_date = %s'
                      , TG_TABLE_NAME, operation_code, now());
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Answer 1

The special variable OLD is not visible inside EXECUTE. (Nor is any other variable in the scope of the function.) Pass in values with the USING construct:

EXECUTE format('INSERT INTO %I VALUES (($1).*)', _tbl)
USING OLD;

Also, use %I for the identifier, not %s.

Details:

• INSERT with dynamic table name in trigger function