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?

2 Answers 2

2

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.

2
  • I'm still getting error: ERROR: missing FROM-clause entry for table "new" LINE 1: INSERT INTO public.dev_hist SELECT $1, user, NEW.* Mar 8, 2019 at 16:50
  • @GrannyAching ...USING NEW is accepted in plpgsql. You probably want to use that. Mar 8, 2019 at 18:52
0

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;

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.