Taking information from a postgres sql query and putting it into a shell script array

Shell Programming and Scripting
1

I have a postgres sql statement that is the following:

select age from students;

which gives me the entries:

Age
---
10
15
13
12
9
14
10

which is about 7 rows of data.

Now what I would like to do with this is use a shell script to create an array age[]. As a results age[1]=10, age[2]=15, age[3]=13, age [4]=12, age[5]=9, age[6]=14, age[7]=10.

Any ideas on how to create a shell script.

I have been able to do the following:

Unix_Array=$(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select age from students;
END TRANSACTION;
\q
EOFF
)
echo $Unix_Array

Unix_Array gives me all the ages at once like this:

10
15
13
12
9
14
10

but I cannot for example parse Unix_Array into individual variables such Unix_Array[1]=10, Unix[2]=15 and so on like I described above. Any ideas?

Thanks,

Jason

2 
Unix_Array=($(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select age from students;
END TRANSACTION;
\q
EOFF
))

print them using ${Unix_Array[0]}, ${Unix_Array[5]}

3

As I have previously mentioned I have a postgres sql statement:

I have a postgres sql statement that is the following:

select age from students;

which gives me the entries:

Age
---
10
15
13
12
9
14
10

which is about 7 rows of data.

I was able to get a reply as to how to set up an array so that age[1]=10, age[2]=15, age[3]=13, age[4]=12 and so on.

Now based on this array I want to make another query from another database that shares the field age with students.

Select grade level from characteristics where age=age[?].

These ages depend on the ages of the students in the students data base.

How can I set up a query in shell script to use age[1] trough age[7] from with the statement

Select grade level from characteristics where age=age[?]

where age[?] is age[1]-age[7]. I would want to do this query for the case that the age array would have had 50 or more entries as well.

Is there a while loop I could do. I did this attempt:

counter=1
While [counter -lt 7]
grade=$(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
      select grade_level from characteristcs where age=${age[$counter]}'
END TRANSACTION
\q
EOFF
)
counter = `expr $counter + 1`
done

Any ideas?

4

First, the array values start from 0
and, there are several ways of doing this

 
for i in $(seq 0 $((${#age[@]} - 1)))
do
grade_${i}=$(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER << EOFF
BEGIN TRANSACTION;
select grade_level from characteristcs where age=${age[$i]};
END TRANSACTION
\q
EOFF
)
done

The above approach is costly as it required one DB Connection for each value of the array age.

Rather, you can go with the below approach and run all the details into grade array

 
for i in $(seq 0 $((${#age[@]} - 1)))
do
echo "select grade_level from characteristcs where age=${age[$i]}"
done > query.sql
grade=($(psql -h $PSQL_HOST $PSQL_RDB $PSQL_USER -a -f query.sql))