4

I want to execute a psql statement within a bash script and output the results to a file. The code I have below works as desired:

#!/bin/bash

query="select * from mytable;"

psql <<EOF > output.txt
\timing
$query
EOF

I want to run that psql command block 5 times and have the results appended to output.txt. It works fine if I just copy and paste it an additional 4 times, but when I try to put it inside of a for-loop, I get errors. Is there any way to do this?

This is the loop I tired:

#!/bin/bash

query="select * from mytable;"

for (( i=0; i<5; i++ ))
do
   psql <<EOF > output.txt
   \timing
   $query
   EOF
done

If I move the final EOF all the way over to the left, it only executes once, as if the loop wasn't there.

codeforester
  • 39,467
  • 16
  • 112
  • 140
mariahm24
  • 145
  • 2
  • 7

2 Answers2

5

You are overwriting the file each time with > inside the loop. You need >> inside or have > outside the loop:

#!/bin/bash

query="select * from mytable;"
for (( i=0; i<5; i++ ))
do
   psql <<EOF
   \timing
   $query
EOF
done > output.txt

Putting > after done is a little more efficient than >> inside the loop.

Similar post:

Community
  • 1
  • 1
codeforester
  • 39,467
  • 16
  • 112
  • 140
0

It is usually better to not run Postgres in a loop. Just generate the commands you want to execute, then run the sequence of generated commands once.

#!/bin/bash

query="select * from mytable;"

for (( i=0; i<5; i++ ))
do
   cat <<___EOF
   \timing
   $query
___EOF
done |
psql > output.txt

which of course in this case can be simplified to just

#!/bin/bash
printf '-- %s\n\\timing\nselect * from mytable;\n' {1..5} |
psql >output.txt

The brace expansion {1..5} is Bash-specific, so you can't use sh for this particular snippet. (There is a difference.)

tripleee
  • 175,061
  • 34
  • 275
  • 318