I was creating a script to create DB user in Postgres via psql CLI, the challanges I am facing is that value conversion from Any case to lower case is happeing or not able to find the solution for the same.
x
prompt 'Please input user name (must be lowercase): ' inputName
set lowerVal lower(:inputName) --This is not working
As per my expectation the lowerVal variable should convert the inputValue into lower case value. Did the googles but didn’t got the solution for the same.
Advertisement
Answer
psql
cannot to evaluate expressions, so your example cannot to work. You can use statement gset
, that evaluate query and stores a result to psql
variable:
postgres=# prompt 'Please input user name (must be lowercase): ' inputName
Please input user name (must be lowercase): AHOJ
postgres=# select lower(:'inputName') as "inputName" gset
postgres=# echo :inputName
ahoj
Probably is better to use only small chars for psql
variable names.
Another possibility is using shell expression evaluation with back apostrophes:
postgres=# set str AHOJ
postgres=# set str `echo ":str" | tr "[:upper:]" "[:lower:]"`
postgres=# echo :str
ahoj
This example works on Unix like systems only.