Re: Better way to process boolean query result in shell-like situations?
От | Adrian Klaver |
---|---|
Тема | Re: Better way to process boolean query result in shell-like situations? |
Дата | |
Msg-id | 56321ED8.9010007@aklaver.com обсуждение исходный текст |
Ответ на | Re: Better way to process boolean query result in shell-like situations? (David <dnelson77808@gmail.com>) |
Ответы |
Re: Better way to process boolean query result in
shell-like situations?
(David <dnelson77808@gmail.com>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: DavidДата:
Сообщение: Re: Better way to process boolean query result in shell-like situations?