0

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?

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.

| improve this answer | |
  • I'm still getting error: ERROR: missing FROM-clause entry for table "new" LINE 1: INSERT INTO public.dev_hist SELECT $1, user, NEW.* – Granny Aching Mar 8 '19 at 16:50
  • @GrannyAching ...USING NEW is accepted in plpgsql. You probably want to use that. – Daniel Vérité Mar 8 '19 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.