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

On 10/29/2015 06:07 AM, David wrote:
> 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

A variation of the above:

test=> select * from users;

id | name
----+---------------
1 | Adrian Klaver
3 | Yogi Berra
2 | Mickey Mouse

test=> select case when count(*) = 0 then 'f' else 't' end AS user from
users where name = 'Dog';
user
------
f
(1 row)

test=> select case when count(*) = 0 then 'f' else 't' end AS user from
users where name = 'Adrian Klaver';
user

------

t

(1 row)

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com