I currently want to extract a value from a SQL command responde
somehting like this:
psql db -c "SELECT COUNT(test) FROM tbTest;"
the result is:
count
------
33176
(1 row)
I want to extract the 33176 value... is there any simple way to do this?
Why mucking with the unwanted stuff? Simply use some psql options...
> psql -At -c "SELECT COUNT(test) FROM tbTest;" db
115899
-X to suppress execution of an psqlrc file (till 9.5 -c did imply that).If it is always return in that format (expected result on line 3), you can use this:
psql db -c "SELECT COUNT(test) FROM tbTest;" | tail -n 2 | head -n 1
The explanation:
tail -n 2will get the last 2 line and then processed byhead -n 1which mean, get first 1 line.
By enclosing the complete shell command in backticks, you can retrieve its result into a shell variable:
#/bin/sh
THECOUNT=`psql -A -t -c 'SELECT COUNT(*) FROM tbTest;' db`
echo "the count = $THECOUNT"
Edit: actually, this does not work, sorry. But bellow works.
If the result is always 4 lines, and without invoking any other commands that create a process:
(read; read; read count; read ) < <(psql db -c "SELECT COUNT(test) FROM tbTest;")
echo "$count"
This also work:
End edit
psql db -c "SELECT COUNT(test) FROM tbTest;" | (read; read; read count; read; echo "$count")
Warning: the count variable will not be available out of the parentheses because the pipe (|) launch a new process. So this does not work:
psql db -c "SELECT COUNT(test) FROM tbTest;" | (read; read; read count; read)
echo "$count"
Edit:
If you want count in a variable, you can:
count=$(psql db -c "SELECT COUNT(test) FROM tbTest;" | (read; read; read count_tmp; read; echo "$count_tmp"))
If you can accept to launch a process, but not two (head and tail), you could:
psql db -c "SELECT COUNT(test) FROM tbTest;" | sed -n -e '3p'
This always assume that the output of psql will be 4 lines and you need the 3rd one.