Thread: How to abort psql when backtick (shell) commands return non-zero?

Hello,

I searched all through Google Groups, Google, and the Postgres docs, but
to no avail.  I hope someone can help me out here!

I have a file that contains SQL, and some Postgres-specific slash commands.
This file calls an external shell script via the backtick mechanism, and
saves the value into a psql variable, for latter insertion into a table.  It
looks something like this:

    \set password  '\'' `/dir/genPass user1` '\''

    ...

    INSERT INTO TEST (password) VALUES (:password);

However, in some error scenarios, the /dir/genPass application fails, and
returns non-zero.  However, in these cases, the stderr of the application
just gets stored in the variable, and hence inserted into my table as the
password.  This is bad, and causes trouble latter... :)

BTW, I launch my "sql script" like so:

    psql MY_DB < populatePasswords.sql

Question: how can I cause my populatePasswords.sql script to fail with an
error if the /dir/genPass command returns an error?  I'm okay with any sort
of failure, as long as populatePasswords.sql stops executing and never gets
to the INSERT statement.  It would also be nice if some sort of error would
print to the user ... ideally the stderr from the shell command (genPass).

Thank you for your time,
David Roche