Thread: How to get variable out to shell script
I am using psql to call an external sql file that executes a simple select count(*):
ie: select into ct count(*) from table;
I can't figure out how to make the ct variable available to the shell script once the external sql file completes execution. What I am trying to do is run the count command against a table in two different databases on two different servers and then compare the variables.
Any ideas?
Thanks in advance.
Alex
ie: select into ct count(*) from table;
I can't figure out how to make the ct variable available to the shell script once the external sql file completes execution. What I am trying to do is run the count command against a table in two different databases on two different servers and then compare the variables.
Any ideas?
Thanks in advance.
Alex
On Sun, Sep 20, 2009 at 3:49 PM, Alex Gadea <alex.gadea@apptik.com> wrote: > I am using psql to call an external sql file that executes a simple select > count(*): > > ie: select into ct count(*) from table; > > I can't figure out how to make the ct variable available to the shell script > once the external sql file completes execution. What I am trying to do is > run the count command against a table in two different databases on two > different servers and then compare the variables. > > Any ideas? $x=`psql -At -c "select b from a limit 1;"` $ echo $x test
On Sun, 2009-09-20 at 16:49 -0500, Alex Gadea wrote: > I am using psql to call an external sql file that executes a simple > select count(*): > > ie: select into ct count(*) from table; > > I can't figure out how to make the ct variable available to the shell > script once the external sql file completes execution. Try psql -At -c "SELECT ..." -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Attachment
On Sun, Sep 20, 2009 at 04:49:03PM -0500, Alex Gadea wrote:
> ie: select into ct count(*) from table;
>
> I can't figure out how to make the ct variable available to the shell
> script once the external sql file completes execution.
Just tell psql not to output any surrounding stuff and then just
redirect as normal:
ct="`psql -tc 'select count(*) from table;'`"
echo $ct
I expect it'll probably be easier to use a "real" scripting language
though; Python and Perl both have reasonable libraries for talking to
Postgres with. Python would be something like:
import psycopg2;
conn = psycopg2.connect("dbname='db1'");
cur = conn.cursor();
cur.execute ("select count(*) from table;");
[[n]] = cur.fetchall();
It's a bit of a fiddle to change over, but having a something more
expressive than a bourne shell can help.
--
Sam http://samason.me.uk/
Yes, I'd like to do it via Perl, but I don't have control over the server and the admins who do may balk at the idea of
loadingthe necessary db modules.
This will work though. Thanks!
Alex
----- Original Message -----
From: "Sam Mason" <sam@samason.me.uk>
To: pgsql-general@postgresql.org
Sent: Sunday, September 20, 2009 6:21:05 PM GMT -05:00 US/Canada Eastern
Subject: Re: [GENERAL] How to get variable out to shell script
On Sun, Sep 20, 2009 at 04:49:03PM -0500, Alex Gadea wrote:
> ie: select into ct count(*) from table;
>
> I can't figure out how to make the ct variable available to the shell
> script once the external sql file completes execution.
Just tell psql not to output any surrounding stuff and then just
redirect as normal:
ct="`psql -tc 'select count(*) from table;'`"
echo $ct
I expect it'll probably be easier to use a "real" scripting language
though; Python and Perl both have reasonable libraries for talking to
Postgres with. Python would be something like:
import psycopg2;
conn = psycopg2.connect("dbname='db1'");
cur = conn.cursor();
cur.execute ("select count(*) from table;");
[[n]] = cur.fetchall();
It's a bit of a fiddle to change over, but having a something more
expressive than a bourne shell can help.
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Sep 20, 2009 at 4:42 PM, Alex Gadea <alex.gadea@apptik.com> wrote: > Yes, I'd like to do it via Perl, but I don't have control over the server and the admins who do may balk at the idea ofloading the necessary db modules. Was in the same position as you. Only language allowed on our production dbs was bash. So... I learned a lot of bash. Now all I have to do is pay down those psychiatric bills. Or become a network engineer I guess.
On Sun, 2009-09-20 at 16:49 -0500, Alex Gadea wrote: > I am using psql to call an external sql file that executes a simple > select count(*): > > ie: select into ct count(*) from table; > > I can't figure out how to make the ct variable available to the shell > script once the external sql file completes execution. Hi Alex, If you are using bash, for example: COUNT=`psql -d <database> -Atc "select count(*) from table;"` in a shell script, any string enclosed in back-quotes (`) is executed & the result is returned. So the above expression assignsthe value returned by the psql command to the variable called COUNT. The -Atc tells psql to return only the unalignedvalue, no formatting or column names, etc. If you store your SQL command outside the script, then you could use: COUNT=`psql -d <database> -Atf <SQL_file>` HTH, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.
On Sun, Sep 20, 2009 at 04:49:03PM -0500, Alex Gadea wrote:
> I am using psql to call an external sql file that executes a simple select count(*):
>
> ie: select into ct count(*) from table;
>
> I can't figure out how to make the ct variable available to the shell script once the external sql file completes
execution.What I am trying to do is run the count command against a table in two different databases on two different
serversand then compare the variables.
>
> Any ideas?
>
> Thanks in advance.
>
> Alex
>
>
I tend to use a lot of shell scripts in my production code, i would do
something like this:
#!/bin/sh
dbname=
user=
password=
tables="t1 t2"
for i in $tables;
do
psql -c "\copy (select count(*) from $i) to pstdout csv" \
"dbname=$dbname user=$user password=$password"
done
#END#######################
that would print two lines...
1234134
4565
that are the count(*) of each table.
Use your imagination.
Saludos.
--
DISCLAIMER: http://goldmark.org/jeff/stupid-disclaimers/
This message will self-destruct in 3 seconds.
On Sun, 20 Sep 2009, Abel Camarillo wrote: > #!/bin/sh > dbname= > user= > password= In general it's better to use the .pgpass/PGPASSFILE mechanism: http://www.postgresql.org/docs/current/static/libpq-pgpass.html to cache passwords like this, if you can't eliminate the need for them altogether through pg_hba.conf adjustments. That way, when you do change the database user's password, there's only one place to update for all of your scripts that talk to the database as that user. It's better still to parameterize all of these connection things into a global configuration file, but now you're talking an extra bit of coding; pgpass support you basically get for free in your app if it's talking to the database with psql. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD