I regularly run into the problem that I want to query a PostgreSQL database in a script/program and depending on a boolean result do one thing or the other. A typical example would be a Puppet Exec that creates a user only if it does not exist yet.
But unfortunately psql always returns with the exit code 0 if the query was run without errors.
I don't consider this to be unfortunate...
In a shell script I can use a query that returns an empty string for failure and something else for success and then test that à la:
| if [ -n "$(psql -Atc "[…]") ]; then echo Success.; fi
but for example in Puppet this requires putting around '/bin/bash -c "[…]"' with yet another level of quoting.
Instead of saying "/bin/bash -c" can you not just say "psql -c"?
The best idea I had so far was to cause a runtime error (here with the logic reversed: If the user exists, psql re- turns failure, otherwise success):
So write a function/script the encapsulates that logic and gives it a friendly name...
| [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'tim';"; echo $? | FEHLER: ungültige Eingabesyntax für ganze Zahl: »tim« | 1 | [tim@passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'does-not-exist';"; echo $? | usename | --------- | (0 rows)
| 0 | [tim@passepartout ~]$
But this (in theory) could fail if usename could be con- verted to a number, and for example 'a'::INT will fail al- ways.
Are there better ways?
You never actually show any Puppet code that you are trying to write better. That limits the audience that is going to be able to provide help. If you provide a fully-working example of the code you have now someone with general knowledge might be able to suggest a solution just from looking at the code.
Ultimately I'd say the best solution is to write a script that performs the desired logic and executes queries using psql as necessary but likely not exposing the SQL to the using (i.e., Puppet) layer.
If you are looking for mechanics you do have the "--file" and dollar-quoting capabilities to aid with nested quoting issues.
