1

I have a bash script that calls another script containing a psql command, i want to stop executing when the psql command fails. The below psql call is to execute a postgres function. In below script the echo $exitcode always returns 0 even if script calling postgres function returns a error. I want psql command to return right error code, so i can handle it properly in below script. FirstScript:

list=`sh /opt/dbb_promo_list.sh -60 -30`
ID_LIST=`echo $list |sed 's/[a-z _-]*//g'|sed 's/(.*//g'`

exitcode=$? 
echo $exitcode //always returns 0 both in successful and error case, i want it to return corresponding exit code for error, so i can handle it and stop the script.
if [ $exitcode -ne 0 ] ; then
  echo "There seems to have errors while running script "
  echo "Exit code is : "$exitcode
  exit 1
fi

dbb_promo_list.sh : This script contains the following command

psql -U $DB_USERNAME -h $DB_HOST -p $DB_PORT -d $DATABASE -c "SELECT schema.${PROCEDURE}($day_begin,$day_end)"

Error thrown below when calling the above command. Since this error is thrown the psql command shouldnt return exit code 0 but its not the case, it returns 0 even if the postgres function works fine or returns a error like below. I am not sure if there a way to tweak in bash script to recognize a error is thrown

ERROR:  function string_agg(numeric, unknown) does not exist
LINE 1: SELECT string_agg(pmotn_id, ',')               FROM ppcapr.p...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

When the sql function fails. It then reports the error on the console, but continues to proceed with executing remainder of the script. But i want the script execution to terminate once the error occurs.

Any suggestions?

Kevin William
  • 11
  • 3
  • `ON_ERROR_STOP` is only going to cause `psql` to exit per "In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 ...". It has nothing to do with the `Bash` script. – Adrian Klaver Jul 15 '22 at 19:46
  • @AdrianKlaver as per this response, https://stackoverflow.com/questions/58944395/reading-errors-returned-from-psql-in-a-bash-script it should work with bash script also. I just want to do some exception handling to stop script execution, when there is a error thrown from the sql file that is being called – Kevin William Jul 18 '22 at 14:46
  • See the follow up answer [here](https://stackoverflow.com/questions/58965726/psql-return-value-error-killing-the-shell-script-that-called-it). In particular "ON_ERROR_STOP will not work with the -c option. ...". – Adrian Klaver Jul 18 '22 at 15:08
  • @AdrianKlaver thanks for that, i didnt see that. I also tried to capture the response of psql into a variable, as per this example https://stackoverflow.com/questions/15242752/store-postgresql-result-in-bash-variable , but it doesnt reach my echo variable statement , after psql command is run – Kevin William Jul 18 '22 at 15:30
  • Update your question with the revised code. – Adrian Klaver Jul 18 '22 at 15:34
  • @AdrianKlaver I have made some edits, i hope it explains my scenario properly. thanks – Kevin William Jul 18 '22 at 17:17

1 Answers1

1

The $? variable holds the last executed command exit code.

In your code, the last command executed before the exitcode=$? line is sed 's/(.*//g', not the psql command. Just moving up this line may solve the issue.

P.S.: I see it's an old question, but someone may benefit from the answer.

Vinicius
  • 1,060
  • 1
  • 12
  • 21