How to get variable out to shell script

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

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 loading the 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 of loading 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 assigns the value returned by the psql command to the variable called COUNT. The -Atc tells psql to return only the unaligned value, 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 servers and 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