Query values into a variable in shell script

Question

I have query which gives me result as

 app_no
--------
(0 rows)

I need to get only the rows part and that too just the number. I am saving the result into a variable but I am not able to parse it.

napp=`psql -U postgres appdb -c "select appno from app.apps where properties&2048=1024
cap=$(echo "$napp"|sed -n 's/[0-9][0-9] rows/1円/p')
echo "$cap"

I just need number of rows and that too just number.

• Jonathan Leffler: Your last sentence is not very clear. Do you need just the number of rows which match the condition? And without any extraneous headings and footers, etc? If so, see my answer. If not, please clarify.

Answer 1

If you need the number of appno entries that match, then you should probably use:

SELECT COUNT(*) FROM app.apps WHERE properties & 2048 = 1024

but the answer will always be 0 because the condition is always going to give 0 or false. You need the same bit twice, either both 1024 or both 2048.

SELECT COUNT(*) FROM app.apps WHERE properties & 1024 = 1024
SELECT COUNT(*) FROM app.apps WHERE properties & 2048 = 2048

SQL interfaces that insist on headings and summaries are a nuisance when shell scripting. However, the psql manual suggests that -q and -t may help (with -A too, perhaps):

• -A or --no-align

  Switches to unaligned output mode. (The default output mode is otherwise aligned.)

• -q or --quiet

  Specifies that psql should do its work quietly. By default, it prints welcome messages and various informational output. If this option is used, none of this happens. This is useful with the -c option. Within psql you can also set the QUIET variable to achieve the same effect.

• -t or --tuples-only

  Turn off printing of column names and result row count footers, etc. This is equivalent to the \t command.

Answer 2

If you want to cut the string as-is :

napp=$(psql -U postgres appdb -c "
 select appno frpm app.apps
 where properties&2048=1024;"
)
cap=$(echo "$napp" | sed -nr 's/.*\(([0-9]+) rows.*/1円/p')
echo "$cap"

But a better solution is the Jonathan Leffler's one