CODEWITHSUNDEEP
postgresqlbashselectpsql
kittygirl
kittygirl

Reputation: 2443

How to `read` postgresql `select` output to bash array,one match is one element?

test tables as below:

create table test(col1 int, col2 varchar,col3 date);
insert into test values (1,'abc','2015-09-10');
insert into test values (1,'abd2','2015-09-11');
insert into test values (21,'xaz','2015-09-12');
insert into test values (2,'xyz','2015-09-13');
insert into test values (3,'tcs','2015-01-15');
insert into test values (3,'tcs','2016-01-18');

Use bash script to get array res of postgresql select.

#!/bin/bash
res_temp=$(psql -tAq postgresql://"$db_user":"$db_password"@localhost:"$db_port"/"$db_name" << EOF
SELECT "col1","col2" FROM "test" WHERE "col2" LIKE '%a%';
EOF
)

read  res <<< $res_temp
#should be 3,but output 1
echo ${#res[@]}
for i in "${!res[@]}"; do
  printf "%s\t%s\n" "$i" "${res[$i]}"
done

Output as below:

1
0   1|abc

Expect output is:

3
0   1|abc
1   1|abd2
2   21|xaz

Where is the problem?

Upvotes: 1

Views: 798

Answers (1)

Bayou
Bayou

Reputation: 3461

This is going wrong because of the read res <<< $res_temp. What do you expect to retrieve from that?

I've fixed your script and put an example how to directly create an array (which I think you're trying). I don't have Postgresql running atm, but SQLite does the same.

How I created the data:

$ sqlite ./a.test
sqlite> create table test(col1 int, col2 varchar(100),col3 varchar(100));
sqlite>  insert into test values (1,'abc','2015-09-10');
sqlite>  insert into test values (1,'abd2','2015-09-11');
sqlite>  insert into test values (21,'xaz','2015-09-12');
sqlite>  insert into test values (2,'xyz','2015-09-13');
sqlite>  insert into test values (3,'tcs','2015-01-15');
sqlite>  insert into test values (3,'tcs','2016-01-18');
sqlite> SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';

Your solution

#! /bin/bash
res_tmp=$(sqlite ./a.test "SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';")
read -a res <<< ${res_tmp[@]}

echo ${#res[@]}
for i in "${!res[@]}"; do
  printf "%s\t%s\n" "$i" "${res[$i]}"
done

exit 0

Directly an array

#! /bin/bash
res=($(sqlite ./a.test "SELECT col1,col2 FROM test WHERE col2 LIKE '%a%';"))

echo ${#res[@]}
for i in "${!res[@]}"; do
        printf "%s\t%s\n" "$i" "${res[$i]}"
done

exit 0

Oh, and output of both:

3
0       1|abc
1       1|abd2
2       21|xaz

Upvotes: 1

Related Questions