Preparing query in postgreSQL with variable table name?

Question

I'm trying to do an INSERT INTO a table whose name is in the variable myTable.

In MySQL, I'd do it like this:

SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I checked into postgreSQL documentation for prepared statements, and I couldn't find any similar examples.

I can assemble query into a string:

sql_query := 'INSERT INTO ' || mySchema || '.' || myTable || ' SELECT action_name, user, ';

But how do I execute it?

Answer 1

To write procedural code and use variables with PostgreSQL, the most common way is to use the plpgsql language, in a function or in a DO block. Executing Dynamic Commands in the documentation has all the details you need.

Note that a dynamic SQL statement does not require a PREPARE like in your MySQL example. It's possible to EXECUTE a dynamic string directly containing the SQL query, and in most cases that's how it's done.

What might be confusing to a beginner in Postgres is that it has two distinct forms of EXECUTE:

• EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] in a plpgsql code block is a plpgsql instruction.

• EXECUTE name [ ( parameter [, ...] ) ] is an SQL query, where name is the name of prepared statement previously declared with PREPARE.

Answer 2

When combining table name as variable with use of NEW and/or OLD for each row to make a simple archiving I combined other answer from Daniel Vérité and his comment into a following simplified example:

-- Create archiving table
create table schema_name.table_name_hist as table schema_name.table_name

-- Create trigger
create trigger hist_manager_triger 
after insert or update or delete on schema_name.table_name
for each row execute procedure hist_manager('schema_name.table_name');


-- Create general archiving function
create or replace function hist_manager() returns trigger as $$
declare
    hist_table text := tg_argv[0] || '_hist';
begin
    if (tg_op in ('INSERT', 'UPDATE')) then
    execute format('insert into %1$s select $1.*', hist_table) using NEW;
    return NEW;
    elsif (tg_op = 'DELETE') then
        OLD.deleted_date = now();
        OLD.deleted_by = current_user;
        execute format('insert into %1$s select $1.*', hist_table) using OLD;
        return OLD;
    end if;
    return null;
end;
$$ language plpgsql;