CODEWITHSUNDEEP
sqlpostgresqlshell
JumpOffBox
JumpOffBox

Reputation: 783

Query values into a variable in shell script

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.

Upvotes: 0

Views: 2413

Answers (2)

Gilles Quénot
Gilles Quénot

Reputation: 185151

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

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 753845

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.

Upvotes: 1

Related Questions