Обсуждение: Having trouble passing a shell variable to a query from psql command line

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Aug 29, 2025 at 10:52 AM Murthy Nunna <mnunna@fnal.gov> wrote:
>> psql -d mydb -t -A -c "SELECT relkind FROM pg_class WHERE relname =
>> :'SHELL_VAR' ;" -v SHELL_VAR="$SHELL_VAR"
>>
>> ERROR:  syntax error at or near ":"
>>
>> LINE 1: SELECT relkind FROM pg_class WHERE relname = :'SHELL_VAR' ;

> I provide the documentation.  Under psql -c:
> "command must be either a command string that is completely parsable by the
> server (i.e., it contains no psql-specific features)"

Yeah.  The argument of a -c switch is just sent to the server as-is.

However, you don't need a script file to fix this.  You can do
something like

echo "SELECT relkind FROM pg_class WHERE relname = :'SHELL_VAR' ;" | psql -d mydb -t -A -v SHELL_VAR="$SHELL_VAR"

            regards, tom lane

Thanks, Tom. That worked!

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> Yeah.  From the cli KISS and do regular bash variable string expansion.

> psql -d mydb -tAc "SELECT relkind FROM pg_class WHERE relname =
> ${SHELL_VAR} ;"

This isn't a great recommendation because bash is not aware of
SQL's quoting rules.  It'll work in simple cases, but there's
a risk of SQL injection if the value of SHELL_VAR comes from
an untrustworthy source.

            regards, tom lane