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.