POSTGRES pgplsql psql command line argument

Question

I have a sql block(file.sql) as :

DO $$
DECLARE 

 var1 varchar;
BEGIN

    var1 := :key;

END $$;

When I invoke this .sql with psql,

psql -h localhost -v key="value" -U user -f file.sql  postgredb

I get an error :

ERROR: syntax error at or near ":"

Would like to know why the command line arg is not getting resolved

Answer 1

I don't think you can do it like that. I'd create a psql function that takes one argument of varchar (e.g. key in your example) and then I'd invoke it from the terminal with:

psql -h localhost -U user postgredb -c "select updateValue('someValue');"

You can then wrap all that in some bash script of function so that you can invoke it faster?.

Hope that helps, cheers.