Hi,
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. 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.
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):
| [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? The environment I am most interested
in is 9.3 on Ubuntu Trusty.
Tim
P. S.: I /can/ write providers or inline templates for Pup-
pet in Ruby to deal with these questions; but here I
am only looking for a solution that is more "univer-
sal" and relies solely on psql or another utility
that is already installed.
