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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
