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

Craig Ringer · Accepted Answer · 2017-08-06 01:18:02Z

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.

answered Aug 6, 2017 at 1:18

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

Thành Chung Bùi Over a year ago

coprocess seems the best option for me but when i change the connect to coproc PGPASSWORD=postgres psql -U postgres database -t -A -F , -v ON_ERROR_STOP=1 -v AUTOCOMMIT=0 -q. When i run the bash the terminal said coproc not found but coproc work if run directly in terminal. What setup should i do here?

Thành Chung Bùi Over a year ago

Thank you, the reason is my bash version does not support coproc (v3.2), since apple does not update sh so use zsh or ksh should work perfectly. Thank you a lot. It safe me a day

Collectives™ on Stack Overflow

How to add shell commands while executing psql in shellscript

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related