3 ms·
> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql
by yellowapple 7y ago
> We'd spawn psql, but that needs the query, as a complete, pre-templated string — it does not support any sort of positional parameters. [...] So, really, psql needs an interface to supply those values as arguments to the CLI.
I haven't tried this before, so caveat emptor, but per psql's manpage this should be supported:
QUERY="SELECT * FROM sometable WHERE name = :'foo'"
echo $QUERY | psql -v foo=asdf # ... other args ...
Basically: you can use colon-prefixed variables in the query text (with the variable name optionally quoted to indicate how the result should be quoted, apparently?), and set them with the -v option (which is equivalent to using the \set command within the query string itself).
This also happens to work for table names, at least per the example in the manpage:
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";
Except in this case it'd be more
echo 'SELECT * FROM :"foo";' | psql -v foo=my_table # ... other args ...
Still no option there for positional parameters, but it's a start, right?