Variable value conversion from any case to smaller case in psql CLI

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.

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.

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.