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

On Wed, Oct 28, 2015 at 10:42 PM, Tim Landscheidt <tim(at)tim-landscheidt(dot)de>
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.

​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(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?

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

SELECT $$this is a valid query$$;

David J,