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...

$ used outside of quote marks is almost always a mistake in bash. — Jasen
– Jasen, Commented Jan 7, 2015 at 23:13

Etan Reisner · Accepted Answer · 2015-01-07 22:41:26Z

2

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 ...;"

answered Jan 7, 2015 at 22:41

Etan Reisner

81.6k8 gold badges119 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cdmccann Over a year ago

Sigh, yup. I thought that I tried this variant. So I guess bash does this differently than other languages that I know...

Etan Reisner Over a year ago

@colinsnotin Yes, word splitting isn't something most programming languages deal with. that being said in many "foo"$var"bar" isn't legal anyway.

Jasen · Accepted Answer · 2015-01-07 23:09:17Z

1

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

answered Jan 7, 2015 at 23:09

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

1 Comment

cdmccann Over a year ago

Jasen, I can't seem to get this to work - and can't really troubleshoot it since I fully can't parse it either. Bash claims to be looking for a closing quote, but they look to all be matching. Error is: unexpected EOF while looking for matching `"'. It's a shame, as it looks to be a lot more efficient than the nonsense that I was writing

Collectives™ on Stack Overflow

Bash, array of where conditions, postgres select

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related