Обсуждение: Better way to process boolean query result in shell-like situations?
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.
Re: Better way to process boolean query result in shell-like situations?
От
"David G. Johnston"
Дата:
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,
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. > > >
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
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. >>> >>> >>> >> >> >> > >
Re: Better way to process boolean query result in shell-like situations?
От
Martijn van Oosterhout
Дата:
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.