Variable as table name in pgsql AND using prepared statements

22 views Asked by At

Is there any way to get this working. My remit from the DBA is to use prepared statements so I'm having to migrate off of my current as I am told it's causing performance issues

    EXECUTE format('INSERT INTO %I SELECT %L, %L, $1.*', TG_TABLE_NAME || '_audit', TG_OP, now(), NEW.*)

I have tried this

    declare
            target_table varchar(50) := TG_TABLE_NAME || '_audit';
        begin
        case
            when TG_OP = 'INSERT' then
                PREPARE stmt as INSERT INTO target_table VALUES (TG_OP, now(), NEW.*);
                EXECUTE stmt;
                DEALLOCATE stmt;
                RETURN NEW;

which results in 'relation target_table does not exist' and this, which does not compile

                PREPARE stmt as INSERT INTO $1 VALUES (TG_OP, now(), NEW.*);
                EXECUTE stmt WITH target_table;

and this which does not compile:

                PREPARE stmt as INSERT INTO TG_TABLE_NAME || '_audit' VALUES (TG_OP, now(), NEW.*);
                EXECUTE stmt;

and a few other variations..

0

There are 0 answers