Bash, array of where conditions, postgres select

Question

I am trying to use Bash to construct a query on my postgres db. I have an array of asnums, and I want to query a table (dcan_nodup) in the database (ixmaps), to count how many rows those asnums appear in.

The follow code snippet works, the one below it does not.

WORKS (produces a comma separated csv with the expected values):

declare -a carriers=(6461 17025 33139 17899 7018 5730 4466 577 6549 11489)
echo ""
echo "Generating CrFreqTR..."
for asnum in "${carriers[@]}"
do
    echo $asnum
    count=$(psql -d ixmaps -Atc "select count(distinct traceroute_id) from dcan_nodup where asnum = '$asnum';")
    echo $count
    echo $asnum", "$count >> dcan_crfreq_tr.csv
done

DOES NOT WORK:

declare -a whereConditions=('asnum = 6461 or asnum = 17025' 'asnum = 33139' 'asnum = 17899' 'asnum = 7018 or asnum = 5730 or asnum = 4466' 'asnum = 577 or asnum = 6549 or asnum = 11489')
for w in "${whereConditions[@]}"
do
    echo $w

    echo psql -d ixmaps -Atc "select count(distinct traceroute_id) from dcan_nodup where "$w";"
    psql -d ixmaps -Atc "select count(distinct traceroute_id) from dcan_nodup where "$w";"
    echo "NOPE #1"

    count=$(psql -d ixmaps -Atc "select count(distinct traceroute_id) from dcan_nodup where '$w';")
    echo $count
    echo "NOPE #2"
    echo $w", "$count >> dcan_crfreq_tr.csv
done

NOPE #1 results:

asnum = 6461 or asnum = 17025
psql -d ixmaps -Atc select count(distinct traceroute_id) from dcan_nodup where asnum = 6461 or asnum = 17025;
psql: warning: extra command-line argument "6461" ignored
psql: warning: extra command-line argument "or" ignored
psql: warning: extra command-line argument "asnum" ignored
psql: warning: extra command-line argument "=" ignored
psql: warning: extra command-line argument "17025;" ignored
psql: FATAL:  Ident authentication failed for user "="
NOPE #1
asnum = 33139
psql -d ixmaps -Atc select count(distinct traceroute_id) from dcan_nodup where asnum = 33139;
psql: warning: extra command-line argument "33139;" ignored
psql: FATAL:  Ident authentication failed for user "="
NOPE #1
asnum = 17899
psql -d ixmaps -Atc select count(distinct traceroute_id) from dcan_nodup where asnum = 17899;
psql: warning: extra command-line argument "17899;" ignored
psql: FATAL:  Ident authentication failed for user "="
...

NOPE #2 results:

asnum = 6461 or asnum = 17025
ERROR:  invalid input syntax for type boolean: "asnum = 6461 or asnum = 17025"
NOPE #2
asnum = 33139
ERROR:  invalid input syntax for type boolean: "asnum = 33139"
...

What I want to do is use the OR conditions to combine the counts of 2 or more of the asns. If I do this manually it works just fine, eg:

ixmaps@trgen:~/scripts$ psql -d ixmaps -Atc "select count(distinct traceroute_id) from dcan_nodup where asnum = 6461 or asnum = 17025;"
124

But I can't get it work within a bash loop...

I suspect that I'm either not escaping the variable correctly, not concatenating strings correctly, or something equally trivial - I'm a bash novice...

Answer 1

Your problem is likely that you are dropping out of the double quotes when you expand $w.

Assume w='one two'.

Compare the output from printf %s\\n "foo "$w" bar":

foo one
two bar

to printf %s\\n "foo$wbar":

foo one two bar

So you don't want

psql ... "select ... "$w"...;"

instead you want

psql ... "select ... $w ...;"

Answer 2

like this?

declare -a carriers=(6461 17025 33139 17899 7018 5730 4466 577 6549 11489)

function join { local IFS="$1"; shift; echo "$*"; } 

echo ""
echo "Generating CrFreqTR..."

q= "copy ( select asnum count(distinct traceroute_id) from dcan_nodup where asnum in (" \
    `join , "${carriers[@]"` ") group by asnum ) TO STDOUT WITH CSV";

 psql -d ixmaps -Atc "$q" >> dcan_crfreq_tr.csv