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'

[link](https://stackoverflow.com/questions/9736085/run-a-postgresql-sql-file-using-command-line-arguments/9736231) In this, I have to send the paramter with the file. — Abimanyu, Dec 20 '18 at 18:28
Possible duplicate of [Pass command line args to sql (Postgres)](https://stackoverflow.com/questions/50103585/pass-command-line-args-to-sql-postgres) — JGH, Dec 20 '18 at 19:56

Kaushik Nayak · Accepted Answer · 2018-12-21T07:16:34.800

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.

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

1 Answers1