Re: Better way to process boolean query result in shell-like situations?

On 10/28/2015 09:42 PM, Tim Landscheidt wrote:
> 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(at)passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'tim';"; echo $?
> | FEHLER: ungültige Eingabesyntax für ganze Zahl: »tim«
> | 1
> | [tim(at)passepartout ~]$ psql -c "SELECT usename::INT FROM pg_user WHERE usename = 'does-not-exist';"; echo $?
> | usename
> | ---------
> | (0 rows)
>
> | 0
> | [tim(at)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.

Good morning Tim,

I solved what I think is a similar problem to what you are trying to do
by storing the query output into a shell variable. For instance:

[dnelson(at)dave1:~/development]$ output=$(psql -U readonly -d postgres -h
dev_box -p 55433 -Atc "SELECT TRUE FROM pg_roles WHERE rolname =
'readonly'")
[dnelson(at)dave1:~/development]$ echo $output
t

Obviosly you can manipulate the query to return false when the role
does not exist. Hopefully that helps?

Dave

>
> 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.
>
>
>