unable to loop through array in bash for PostgreSQL query export

Question

I have a PostgreSQL query that I'd like to run for multiple geographic areas via a loop. I want to use the elements in the array to modify the query and the name of the csv file where I'm exporting the data to. So in essence, I want the query to run on ...cwa = 'MFR'... and export to hourly_MFR.csv, then run on ...cwa = 'PQR'... and export to hourly_PQR.csv, and so on.

Here's what I have so far. I thought maybe the EOF in the script might be causing problems, but I couldn't figure out how to get the loop to work while maintaining the general format of the script.

Also, the query/script, without the looping (excluding declare, for, do, done statements) works fine.

dbname="XXX"

username="XXXXX"

psql $dbname $username << EOF

declare -a arr=('MFR', 'PQR', 'REV')

for i in "${arr[@]}"

do

\COPY
(SELECT d.woyhh,
      COALESCE(ct.ct, 0) AS total_count
FROM
 (SELECT f_woyhh(d::TIMESTAMP) AS woyhh
  FROM generate_series(TIMESTAMP '2018-01-01', TIMESTAMP '2018-12-31', interval '1 hour') d) d
LEFT JOIN
 (SELECT f_woyhh((TIME)::TIMESTAMP) AS woyhh,
         count(*) AS ct
  FROM counties c
  JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
  WHERE cwa = $i
  GROUP BY 1) ct USING (whh)
ORDER BY 1) TO /var/www/html/GIS/ltg_db/bigquery/hourly_$i.csv CSV HEADER;

done

EOF

Thanks for any help!

You can only have SQL commands between << EOF and EOF. The loop has to be outside. — Laurenz Albe
– Laurenz Albe, Commented May 19, 2018 at 18:09

Lienhart Woitok · Accepted Answer · 2018-05-19 18:12:12Z

2

I think you are nearly there, you just have to reorder some lines. Try this:

dbname="XXX"

username="XXXXX"

declare -a arr=('MFR', 'PQR', 'REV')

for i in "${arr[@]}"
do

psql $dbname $username << EOF

\COPY
(SELECT d.woyhh,
      COALESCE(ct.ct, 0) AS total_count
FROM
 (SELECT f_woyhh(d::TIMESTAMP) AS woyhh
  FROM generate_series(TIMESTAMP '2018-01-01', TIMESTAMP '2018-12-31', interval '1 hour') d) d
LEFT JOIN
 (SELECT f_woyhh((TIME)::TIMESTAMP) AS woyhh,
         count(*) AS ct
  FROM counties c
  JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
  WHERE cwa = $i
  GROUP BY 1) ct USING (whh)
ORDER BY 1) TO /var/www/html/GIS/ltg_db/bigquery/hourly_$i.csv CSV HEADER;

EOF

done

The declare and the for loop are part of the bash script while everything between <<EOF and EOF are part of your Postgresql query.

answered May 19, 2018 at 18:12

Lienhart Woitok

4263 silver badges10 bronze badges

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

1 Comment

user1610717 Over a year ago

Works! Thanks very much for the help and distinction on what EOF is for.

sygibson · Accepted Answer · 2018-05-19 19:06:15Z

1

In @Lienhart Woitok's answer above, the solution will definitely work. However - note that this has the side effect of executing a new 'psql' call, database connection setup, authentication, and subsequent response returned; followed by closing the connection - for each iteration of the loop.

In this case you are only running 3 iterations of the loop, so it may not be a significant issue. However, if you expand the usage to run more iterations, you may want to optimize this to only run a single DB connection and batch query it.

To do that, use of a temporary working file to build the SQL commands may be necessary. There are other ways, but this is relatively simple to use and debug:

QUERY_FILE=$(mktemp /tmp/query.XXXXXXX)

# note the use of an array isn't really necessary in this use
# case - and a simple set of values can be used equally as well
CWA="MFR PQR REV"

for i in $CWA
do
 cat <<EOF >> $QUERY_FILE
     <ADD_YOUR_QUERY_STATEMENTS_HERE>
EOF
done

psql --file=$QUERY_FILE $dbname $username

if (( $? )) 
then
  echo "query failed (QUERY_FILE: ($QUERY_FILE')"
  exit 1 
else
  echo "query succeeded"
  rm -f $QUERY_FILE
  exit 0
fi

answered May 19, 2018 at 19:06

sygibson

3892 silver badges7 bronze badges

5 Comments

chepner Over a year ago

There's no real reason not to use an array, though, either. Don't reinforce a bad habit of treating a space-separated string of words as a "list" just because it happens to work for one particular example.

chepner Over a year ago

If you are really set against using an array (perhaps for POSIX compliance), and because this is a short sequence, I would define a function that takes the CWA value as an argument, and call it explicitly: foo () { ...; }; foo MFR; foo PQR; foo REV.

sygibson Over a year ago

Arrays surely can be used, but I often find that many people use complex structures just because they feel that as a purist they must. BASH scripting is not pure functional programming. Passing three values to a loop construct doesn't require an Array. As you state, an array certainly works fine too. I tire of reading code that is needlessly complex for complexity sake. Readability for the next person is far more important IMHO ... I'm often the "next person" reading someone's (sometimes my own!) too complex constructs, and shaking my head ...

chepner Over a year ago

I fail to see how CWA=(MFR PQR REV) and "${CWA[@]}" are prohibitively complex, compared to CWA="MFR PQR REV" and $CWA.

sygibson Over a year ago

That's the beauty of options with code. We an all express our own style and views on how code should look. I have expressed mine. You have expressed yours. Neither is right nor wrong - they are simply our own beliefs on style of syntax and usage of the language. As I have stated - both options will work.

Collectives™ on Stack Overflow

unable to loop through array in bash for PostgreSQL query export

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related