Обсуждение: SQL from shell script
Hi All,
I have the following statements in my shell script:
echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from affiliate_batch where tx_dt < ' "$DATE" ' order by id; " > $1$DATE.1.txt
echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from affiliate_event where dt< ' "$DATE " ' order by id; " > $1$DATE.2.txt
I will be adding more statements in this file. I would like to know if there is a way to avoid specifying the connection everytime. I would like to make the connection once and then execute the set of SQL statements and disconnect from the database.
It would be great if someone could help me.
Thanks!
Saranya
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Is there really NO WAY you could do this in perl or java? Database interfaces in these languages are easy to use and designed to avoid the types of stuff you are doing below. They are generally quite flexible and mainstream. For perl, see http://cpan.uwinnipeg.ca/htdocs/DBI/DBI.html as an example. Another way to go about the problem is to direct all your SQL queries into a temporary table and then use SQL COPY from that temporary table to a file. You could then generate one large SQL command and push that through psql. Sean On Jan 14, 2005, at 1:36 PM, sarlav kumar wrote: > Hi All, > > I have the following statements in my shell script: > > echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from > affiliate_batch where tx_dt < ' "$DATE" ' order by id; " > > $1$DATE.1.txt > > echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from > affiliate_event where dt< ' "$DATE " ' order by id; " > $1$DATE.2.txt > > I will be adding more statements in this file. I would like to know if > there is a way to avoid specifying the connection everytime. I would > like to make the connection once and then execute the set of SQL > statements and disconnect from the database. > > It would be great if someone could help me. > > Thanks! > Saranya > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com
Hi,
I haven't used PERL or JAVA to do this before. I will look at the documentation and try out using perl DBI.
But is there a way to proceed with the way I started?
Actually what I am trying to do is to create temporary tables with the select statements i.e each select statement will create a temporary table and I would like to dump these temporary tables to files which can be written off to tapes.
The Sequence of statements would be something like
1) create table temp1 as select * from table1 where criteria1;
2) pg_dump.. --table=temp1
3) drop table temp1;
4) delete from table1 where criteria1;
Note: criteria will at the least depend on a date variable.
These set of statements have to be done for a set of tables.
Any help would be appreciated.
Thanks again!
Saranya
Sean Davis <sdavis2@mail.nih.gov> wrote:
Sean Davis <sdavis2@mail.nih.gov> wrote:
Is there really NO WAY you could do this in perl or java? Database
interfaces in these languages are easy to use and designed to avoid the
types of stuff you are doing below. They are generally quite flexible
and mainstream. For perl, see
http://cpan.uwinnipeg.ca/htdocs/DBI/DBI.html as an example.
Another way to go about the problem is to direct all your SQL queries
into a temporary table and then use SQL COPY from that temporary table
to a file. You could then generate one large SQL command and push that
through psql.
Sean
On Jan 14, 2005, at 1:36 PM, sarlav kumar wrote:
> Hi All,
>
> I have the following statements in my shell script:
>
> echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from
> affiliate_batch where tx_dt < ' "$DATE" ' order by id; " >
> $1$DATE.1.txt
>
> echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME -c "select * from
> affiliate_event where dt< ' "$DATE " ' order by id; " > $1$DATE.2.txt
>
> I will be adding more statements in this file. I would like to know if
> there is a way to avoid specifying the connection everytime. I would
> like to make the connection once and then execute the set of SQL
> statements and disconnect from the database.
>
> It would be great if someone could help me.
>
> Thanks!
> Saranya
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
Pardon me if I'm arriving late to the thread and this has been shot down already, but it appears psql has cmd line args for accepting files: $ psql --help This is psql 7.4.3, the PostgreSQL interactive terminal. Usage: psql [OPTIONS]... [DBNAME [USERNAME]] General options: -d DBNAME specify database name to connect to (default: "xxxxxxxx") -c COMMAND run only single command (SQL or internal) and exit -f FILENAME execute commands from file, then exit Does this feature not work? Rgds, Bret > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of sarlav kumar > Sent: Friday, January 14, 2005 2:13 PM > To: Sean Davis > Cc: pgsqlnovice > Subject: Re: [NOVICE] SQL from shell script > > Hi, > > I haven't used PERL or JAVA to do this before. I will look at > the documentation and try out using perl DBI. > > But is there a way to proceed with the way I started? > > Actually what I am trying to do is to create temporary tables > with the select statements i.e each select statement will > create a temporary table and I would like to dump these > temporary tables to files which can be written off to tapes. > > The Sequence of statements would be something like > > 1) create table temp1 as select * from table1 where criteria1; > > 2) pg_dump.. --table=temp1 > > 3) drop table temp1; > > 4) delete from table1 where criteria1; > > Note: criteria will at the least depend on a date variable. > > These set of statements have to be done for a set of tables. > Any help would be appreciated. > > Thanks again! > Saranya > > Sean Davis <sdavis2@mail.nih.gov> wrote: > > Is there really NO WAY you could do this in perl or > java? Database > interfaces in these languages are easy to use and > designed to avoid the > types of stuff you are doing below. They are generally > quite flexible > and mainstream. For perl, see > http://cpan.uwinnipeg.ca/htdocs/DBI/DBI.html as an example. > > Another way to go about the problem is to direct all > your SQL queries > into a temporary table and then use SQL COPY from that > temporary table > to a file. You could then generate one large SQL > command and push that > through psql. > > Sean > > On Jan 14, 2005, at 1:36 PM, sarlav kumar wrote: > > > Hi All, > > > > I have the following statements in my shell script: > > > > echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME > -c "select * from > > affiliate_batch where tx_dt < ' "$DATE" ' order by id; " > > > $1$DATE.1.txt > > > > echo $PGPASSWORD | psql -U $USER -h $HOST -d $DBNAME > -c "select * from > > affiliate_event where dt< ' "$DATE " ' order by id; " > > $1$DATE.2.txt > > > > I will be adding more statements in this file. I > would like to know if > > there is a way to avoid specifying the connection > everytime. I would > > like to make the connection once and then execute the > set of SQL > > statements and disconnect from the database. > > > > It would be great if someone could help me. > > > > Thanks! > > Saranya > > > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam protection around > > http://mail.yahoo.com > > > > ________________________________ > > Do you Yahoo!? > Yahoo! Mail > <http://us.rd.yahoo.com/mail_us/taglines/security/*http://prom > otions.yahoo.com/new_mail/static/protection.html> - You care > about security. So do we. >
sarlav kumar wrote:
> Hi,
>
> I haven't used PERL or JAVA to do this before. I will look at the
> documentation and try out using perl DBI.
>
> But is there a way to proceed with the way I started?
You'll have to figure out another way to handle the password, but you
can pass multiple commands to psql as follows:
echo echo "select * from table1; select * from table2;"|psql rnd
I don't know if there is a limit to the # of commands you can send in
this manner, but if it's a good many you can make more readable as follows:
echo "select * from table1;
select * from table2;
select * from table3;
"|psql rnd
Still, you'll have to deal with the password a different way likely.
--
Until later, Geoffrey
On Fri, Jan 14, 2005 at 02:32:48PM -0500, Geoffrey wrote: > echo "select * from table1; > select * from table2; > select * from table3; > "|psql rnd You could also use a here-document if your shell supports it (most do): psql rnd <<END_OF_SQL select * from table1; select * from table2; select * from table3; END_OF_SQL > Still, you'll have to deal with the password a different way likely. You could use the PGPASSWORD environment variable, although doing so is deprecated due to the security risk (many operating systems make environment variables visible to other processes). Another possibility would be to use ~/.pgpass; see "The Password File" in the libpq chapter of the documentation. Or, if the security model allows it, tweak pg_hba.conf so it doesn't require password authentication (some security can be maintaned by using ident authentication if the system supports it). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Jan 14, 2005, at 2:12 PM, sarlav kumar wrote:
> Hi,
>
> I haven't used PERL or JAVA to do this before. I will look at the
> documentation and try out using perl DBI.
>
> But is there a way to proceed with the way I started?
>
> Actually what I am trying to do is to create temporary tables with the
> select statements i.e each select statement will create a temporary
> table and I would like to dump these temporary tables to files which
> can be written off to tapes.
>
> The Sequence of statements would be something like
>
> 1) create table temp1 as select * from table1 where criteria1;
Yep.
> 2) pg_dump.. --table=temp1
>
Instead of pg_dump, you could do a SQL COPY here.
> 3) drop table temp1;
Yep.
> 4) delete from table1 where criteria1;
>
You want to delete these entries, I assume?
> Note: criteria will at the least depend on a date variable.
>
> These set of statements have to be done for a set of tables.
> Any help would be appreciated.
>
You could write a simple perl script that looks like:
#!/usr/bin/perl
use strict;
my $date = shift; #get from command line
my @tables = (qw/ table1 table2 table3 /); #put in your tablenames here
foreach my $tablename (@tables) {
print "create table temp1 as select * from $tablename where
date='$date';\n";
print "COPY temp1 TO '$tablename.$date.txt';\n";
print "DROP table temp1;\n";
print "DELETE FROM $tablename WHERE date='$date';\n";
}
If you call the program sqlprep.pl then doing:
perl sqlprep.pl '09-27-04' > dumpcode.sql
will produce the SQL that looks like:
create table temp1 as select * from table1 where date='09-27-04';
COPY temp1 TO 'table1.09-27-04.txt';
DROP table temp1;
DELETE FROM table1 WHERE date='09-27-04';
create table temp1 as select * from table2 where date='09-27-04';
COPY temp1 TO 'table2.09-27-04.txt';
DROP table temp1;
DELETE FROM table2 WHERE date='09-27-04';
create table temp1 as select * from table3 where date='09-27-04';
COPY temp1 TO 'table3.09-27-04.txt';
DROP table temp1;
DELETE FROM table3 WHERE date='09-27-04';
You could then go into psql and do:
\i dumpcode.sql
I couldn't test this, but you get the idea. You could change the
@tables above to be whatever you need.
Sean
> 4) delete from table1 where criteria1;
>
You want to delete these entries, I assume?Yes, I want to delete the entries.
You could write a simple perl script that looks like:
#!/usr/bin/perl
use strict;
my $date = shift; #get from command line
my @tables = (qw/ table1 table2 table3 /); #put in your tablenames here
foreach my $tablename (@tables) {
print "create table temp1 as select * from $tablename where
date='$date';\n";
print "COPY temp1 TO '$tablename.$date.txt';\n";
print "DROP table temp1;\n";
print "DELETE FROM $tablename WHERE date='$date';\n";
}This is kind of what I want to do. The only problem here is that first statement where I create temporary tables, will be selecting data from different tables based on different where clauses. Some of them even use join on tables. So I guess I can't use the foreach statement.
Thanks a lot for the help, this definitely has given me a lead on how to proceed.
Thank you very much,
Saranya
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.