Passing Variable in to psql file via command line. One variable passes other doesnt

Question

I am trying to pass two variables in to a .psql file that I am running via psql from the command line. One of my variables converts to the value the other does not. var1 (the id value near the end of the statement in near the where clause is converted fine but var2 the variable that is part of the json check of id is not converted. I assume it is something related to how I am escaping things but after a good hour playing with this I am stuck. Thanks

Command Line

psql -h 127.0.0.1 -f "delete.psql" -v var1="$var1" -v var2="$var2"

delete.sql

update data
   set value = jsonb_set(value, '{my_items}',
   jsonb_path_query_array(value->'item', '$ ? (@."id" <> (:'var2') )')) where id=(:'var1') ;

Error that I get back because of the lack of conversion of var2

psql:delete.psql:3: ERROR:  syntax error at or near "var2"
LINE 3: ...th_query_array(value->'my_items', '$ ? (@."id" <> (:'var2')...
                                                                ^

In that case: use a HERE-document. Solves all of your quoting pains, too.https://stackoverflow.com/questions/11250590/cron-job-to-remove-old-data-from-postgres-on-debian/11251168#11251168 — wildplasser, Jul 31 '20 at 14:20
My suspicion would be the variable names shouldn't be quoted. Have to tested removing the quotes from var1 and var2? So only :var1 :var2 — kutschkem, Jul 31 '20 at 14:22
Using a prepared query could also solve some of your quoting problems. — wildplasser, Jul 31 '20 at 14:56

Erwin Brandstetter · Accepted Answer · 2020-08-10T19:35:00.730

The issue is that variable interpolation is not performed within quoted string literals. So this piece cannot work:

'$ ? (@."id" <> (:'var2') )'

The manual:

Variable interpolation will not be performed within quoted SQL literals and identifiers.

One way to fix simple cases would be to concatenate strings. But your case isn't so simple, as you really want an argument of type jsonpath, and you can easily introduce room for SQL injection ...

jsonb_path_query_array() has its own way of passing variables in a 3rd parameter called "vars" of type jsonb. That's the safe way to proceed.

Either pass $var2 as legal jsonb literal like: '{"var2" : "foo"}'. Then your UPDATE query can be:

update data set value = jsonb_set(value, '{my_items}', jsonb_path_query_array(value->'item', '$ ? (@."id" <> $var2)', :'var2')) where id = :'var1';

Or keep passing the bare string value and have Postgres construct the "vars" parameter on the fly with:jsonb_build_object('var2', :'var2'))

Then your UPDATE query can be:

update data set value = jsonb_set(value, '{my_items}', jsonb_path_query_array(value->'item', '$ ? (@."id" <> $var2)', jsonb_build_object('var2', :'var2'))) where id = :'var1';

Also removed some noise parentheses.

use \set variable inside plpgsql declare block

You quoted the variables correctly. See:

How can I quote a named argument passed in to psql?

There are various alternative approaches: a prepared statement, a (temporary) function ... See:

Use variable set by psql meta-command inside of DO block

Passing Variable in to psql file via command line. One variable passes other doesnt

1 Answers1