Shell Script and SQL results

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

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"

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 2 will get the last 2 line and then processed by head -n 1 which mean, get first 1 line.

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.