I want to catch a PostgreSQL error in bash.
For example:
function create_database:
sudo -u postgres psql -c "CREATE DATABASE $1 WITH OWNER $2;"
I want something that can catch any type of postgres errors (not only for create) and echo the error
also in case of error return 1
If I use:
$RESULT=$(sudo -u postgres psql -c "CREATE DATABASE $1 WITH OWNER $2;")
I get the answer from psql but is specific to the operation, so I have to do string matching for each SQL command.
It is fairly trivial to see if the statement was successful or not: just check the return code.
$ sudo -u postgres psql -c 'melect 32'
ERROR: syntax error at or near "melect"
LINE 1: melect 32
^
$ echo $?
1
$ sudo -u postgres psql -c 'DROP TABLE not_exists'
ERROR: table "not_exists" does not exist
$ echo $?
1
$ sudo -u postgres psql -c 'SELECT 42'
?column?
----------
42
(1 row)
$ echo $?
0
So your code could do something like this:
sudo -u postgres psql -c "..." >/tmp/result 2>&1
if [ $? -ne 0 ]; then
echo /tmp/result >>logfile
rm -f /tmp/result
exit 1
else
rm -f /tmp/result
fi
