Thread

1. Better way to process boolean query result in shell-like situations?

   Tim Landscheidt <tim@tim-landscheidt.de> — 2015-10-29T02:42:00Z

   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.
   
   
   
   

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

   David G. Johnston <david.g.johnston@gmail.com> — 2015-10-29T02:59:15Z

   On Wed, Oct 28, 2015 at 10:42 PM, Tim Landscheidt <tim@tim-landscheidt.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@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?
   
   
   ​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,
   

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

   David Nelson <dnelson77808@gmail.com> — 2015-10-29T13:07:18Z

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

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

   Adrian Klaver <adrian.klaver@aklaver.com> — 2015-10-29T13:27:52Z

   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)
   
   
   > 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.klaver@aklaver.com
   
   
   

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

   David Nelson <dnelson77808@gmail.com> — 2015-10-29T16:17:49Z

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

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

   Martijn van Oosterhout <kleptog@svana.org> — 2015-10-29T21:57:27Z

   On Thu, Oct 29, 2015 at 02:42:00AM +0000, 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:
   
   
   <snip>
   
   What I do is use grep, for example (off the top of my head):
   
   if ! psql -qAt -c "select usename from pg_user" | grep -q USERNAME ;
   then
   ...
   
   If you're looking for true/false you could grep for t/f.
   
   Hope this helps,
   -- 
   Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
   > The combine: one man, one day, wheat for half a million loaves of bread.