How to pass the parameter with the file while executing the postgresql command in the comand line?

Question

I have a file named insert_all.sql which contains the content as below. Here v1 is the parameter to be passed.

 do $$
    begin
    delete from tabledetails where table_name = '$(v1)';
    end;
    $$;

I am trying to execute the query in that file (insert_all.sql) with the command as given below. But it is not working. What's wrong with my command? If it is wrong, advice me on this.

psql.exe -U postgres -p 5454 -h 127.0.0.1 -d desk -f D:\insert_all.sql -v v1='statusTable'

Answer 1

The variable option works for simple queries in the file but doesn't work inside DO blocks. One option is to create a TEMP table storing the variable and then use it like this.

create temp table var_temp as select :'v1'::TEXT as var;
 do $$
    begin
    delete from tabledetails where table_name =(select var from var_temp) ;
    end;
 $$; 

---

psql.exe -U postgres -p 5454 -h 127.0.0.1 -d desk -f D:\insert_all.sql -v v1="statusTable"

If you do not have any other operations that require a DO block, consider running the delete as plain SQL statement instead.