How to add shell commands while executing psql in shellscript

Question

Currently I'm writing a shell script that do batch job on the database and I want to put shell script action like write to log file or rollback in some condition while executing psql. Like this

ConnectDb() {
  PGPASSWORD=postgres psql -U postgres database -t -A -F , -v ON_ERROR_STOP=1 -v AUTOCOMMIT=0
}

printMsg() {
 echo "$PROGRAM/$SUBMODULE $(date "+ %Y%H%s")" $1 | tee -a ~/Desktop/shell/log/test.log
}

ConnectDb <<EOF
  start transaction;
  select * from ...;
  # do some database stubs here
  
  # i want to add somthing like this
  printMsg "Querying ..."
  # many shell script command go here
  if [ some accepted condition ] commit;
  if [ some bad conditions ] rollback;
  if [ should do more database query ] do insert, update, delete to database
  
  
  commit;
EOF

Is there any way to retrieve that?

UPDATE use coprocess should work perfectly. For those who met the same problem UNIX Co process

Answer 1

psql doesn't really have any sort of flow control, so no. You can use the \! command to run shell, but since you can't make flow control decisions based on it it won't help you.

Instead, invert control. Have your shell script control psql and make decisions based on psql output. Run psql -qAtX so you only get tuple output.

Usually just running psql a bunch times is enough, but for more complex cases you can run it as a coprocess. I find that by that point it starts getting more useful to script from a language where you can integrate queries better, like python3's psycopg2.