public inbox for [email protected]
help / color / mirror / Atom feedHaving trouble passing a shell variable to a query from psql command line
7+ messages / 4 participants
[nested] [flat]
* Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 17:52 Murthy Nunna <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Murthy Nunna @ 2025-08-29 17:52 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Hello,
Below works:
psql -d mydb -t -A -c "SELECT relkind FROM pg_class WHERE relname = 'pg_trigger' ;"
r
I am getting syntax error from following:
echo $SHELL_VAR
pg_trigger
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' ;
^
Is psql script necessary to pass shell variable?
I appreciate any help you can provide.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 17:58 David G. Johnston <[email protected]>
parent: Murthy Nunna <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2025-08-29 17:58 UTC (permalink / raw)
To: Murthy Nunna <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Fri, Aug 29, 2025 at 10:52 AM Murthy Nunna <[email protected]> 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' ;
>
> ^
>
> Is psql script necessary to pass shell variable?
>
>
> I appreciate any help you can provide.
>
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)"
Variable-substitution is a psql-specific feature.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 18:16 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-08-29 18:16 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Murthy Nunna <[email protected]>; Pgsql-admin <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Fri, Aug 29, 2025 at 10:52 AM Murthy Nunna <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 18:27 Ron Johnson <[email protected]>
parent: Murthy Nunna <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-08-29 18:27 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Fri, Aug 29, 2025 at 1:52 PM Murthy Nunna <[email protected]> wrote:
> Hello,
>
>
>
> Below works:
>
>
>
> psql -d mydb -t -A -c "SELECT relkind FROM pg_class WHERE relname =
> 'pg_trigger' ;"
>
> r
>
>
>
> I am getting syntax error from following:
>
>
>
> echo $SHELL_VAR
>
> pg_trigger
>
>
>
> 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' ;
>
> ^
>
> Is psql script necessary to pass shell variable?
>
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} ;"
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 18:31 Murthy Nunna <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Murthy Nunna @ 2025-08-29 18:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Thanks, Tom. That worked!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 19:15 Tom Lane <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-08-29 19:15 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Ron Johnson <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Having trouble passing a shell variable to a query from psql command line
@ 2025-08-29 21:15 Ron Johnson <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2025-08-29 21:15 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
On Fri, Aug 29, 2025 at 3:15 PM Tom Lane <[email protected]> wrote:
> Ron Johnson <[email protected]> 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.
Well, yeah, if your shell script interacts with the outside world you've
got to be a bit more robust than if the script only does db maintenance
operations on the db server.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-08-29 21:15 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-29 17:52 Having trouble passing a shell variable to a query from psql command line Murthy Nunna <[email protected]>
2025-08-29 17:58 ` David G. Johnston <[email protected]>
2025-08-29 18:16 ` Tom Lane <[email protected]>
2025-08-29 18:31 ` Murthy Nunna <[email protected]>
2025-08-29 18:27 ` Ron Johnson <[email protected]>
2025-08-29 19:15 ` Tom Lane <[email protected]>
2025-08-29 21:15 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox