Return SQL Query as bash array

Question

First Post. Bash novice. Couldn't find an effective solution.

Looking for an efficient parsing method / alternative method

My initial attempt: (+ edit thanks to @larks)

services=($($PSQL "SELECT array(select name from services);"))

echo ${services[@]}
>array -------------------------------- {Shampoo,Dying,Long-cut} (1 row)
echo ${#services[@]}
>5
echo ${services[2]}
>{Shampoo,Dying,Long-cut}

I'm looking to end up with an array identical to the ones below but without creating a csv in the process.

echo $($PSQL "\copy (select name from services) to 'services.csv';")

readarray arr -t a < services.csv
echo ${arr[@]}
>Shampoo Dying Long-cut
echo ${#services[@]}
>3

Answer 1

Your services variable is not an array; to create an array you need to surround the value with (...). For example, compare this:

$ example=$(echo one two three)
$ echo ${example[0]}
one two three

With this:

$ example=( $(echo one two three) )
$ echo ${example[0]}
one

So assuming that your $PSQL command generates output in an appropriate format, you want:

services=( $($PSQL "SELECT array(select name from services);") )

---

For what you're trying to do in your question, I don't see any reason to use the array function. Given a table like this:

CREATE TABLE services (
    id serial primary key,
    name text
);

INSERT INTO services (name) VALUES ('foo');
INSERT INTO services (name) VALUES ('bar');
INSERT INTO services (name) VALUES ('qux');

A query like this will produce results amendable for turning into a bash array:

$ psql -t --csv  -U postgres -d arraytest -c 'select name from services'
foo
bar
qux

In a bash script:

services=( $(psql -t --csv  -U postgres -d arraytest -c 'select name from services') )

for service in "${services[@]}"; do
  echo "SERVICE: $service"
done

Which produces:

SERVICE: foo
SERVICE: bar
SERVICE: qux