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

larsks · Accepted Answer · 2022-04-27 21:35:49Z

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

edited Apr 27, 2022 at 21:35

answered Apr 27, 2022 at 19:36

larsks

318k49 gold badges473 silver badges482 bronze badges

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

1 Comment

Lumpy Over a year ago

Noted. But your fix still gives me the exact same output but with an array of 5 elements. Where... echo ${#services[@]} >5 echo ${services[2]} >{Shampoo,Dying,Long-cut} I could then read this element into a new array but I was hoping for a more elegant solution

Collectives™ on Stack Overflow

Return SQL Query as bash array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related