How to pass parameters to .sql file using psql command line?

Question

I have a bat file which have below command

"C:\Program Files (x86)\pgAdmin III\1.22\psql" -U postgres -h hostname -p 5432 -d TestDB -w -f 1.1.sql -v num_var=1.2

this command calls file 1.1.sql which have below script

DO $$
Begin 

If Exists (Select * from pg_statio_user_tables where relname = 'applicationsetting' ) then
update applicationsetting set value = :num_var
where applicationsettingid = '32bdba3b-39f6-4e43-947e-0bfdd4f5d561';
End IF;

End $$;

while clicking on bat file, I am getting this error

psql:1.1.sql:9: ERROR:  syntax error at or near ":"
LINE 4: update demo set keyvalue = :num_var

However, the code works fine if I don't pass any variable and use static value in the update query.

EDIT

Code also works fine if I use the update query only without DO block and if clause.

you get error on 9th line, please put first 10 lines of your 1.1. sql?.. — Vao Tsun, Mar 20 '17 at 10:46

score 1 · Answer 1 · answered Jun 20 '18 at 19:56

No direct way, look into hacks here and here, eg:

prepare:

db=# create table t (c int);
CREATE TABLE
db=# insert into t values (0);
INSERT 0 1

your working update:

db=# \set num_var 1
db=# update t set c = :num_var;
UPDATE 1
db=# table t;
 c
---
 1
(1 row)

now hack for "nested quoting":

db=# \set num_var 2
db=# \set hack 'begin update t set c = ':num_var'; end';
db=# do :'hack';
DO
db=# table t;
 c
---
 2
(1 row)

How to pass parameters to .sql file using psql command line?

1 Answers1