Re: Better way to process boolean query result in shell-like situations? - Mailing list pgsql-general

On 10/29/2015 08:27 AM, Adrian Klaver wrote:
> 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@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.
>>
>> 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@dave1:~/development]$ output=$(psql -U readonly -d postgres -h
>> dev_box -p 55433 -Atc "SELECT TRUE FROM pg_roles WHERE rolname =
>> 'readonly'")
>> [dnelson@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)
>
>

Nice way to get either condition Adrian!

The use case that led me to discover this trick was a bit different
than that of the OP. I was automating the testing of SQL statements
that I expected to fail. At first the psql exit code seemed perfect
until I realized that the exit code would be 1 whether the failure
was due to, say the foreign key violation that I was expecting, or
due to a syntax error. That's when I hit upon capturing the output
into a variable and grepping for the sql ERROR code to verify that
the failure was for the expected reason. Right now I just send that
output to the console and visually inspect it, but my next step is
to programmatically perform the comparision.

Dave

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