|
|
 | | From: | sarlav kumar | | Subject: | SQL from shell script | | Date: | Fri, 14 Jan 2005 10:36:22 -0800 (PST) |
|
|
 | --0-850309612-1105727782=:26312
Content-Type: text/plain; charset=us-ascii
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
--0-850309612-1105727782=:26312
Content-Type: text/html; charset=us-ascii
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
--0-850309612-1105727782=:26312--
|
|
| | From: | Sean Davis | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 13:46:06 -0500 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | sarlav kumar | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 11:12:30 -0800 (PST) |
|
|
| --0-516013604-1105729950=:12413
Content-Type: text/plain; charset=us-ascii
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 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.
--0-516013604-1105729950=:12413
Content-Type: text/html; charset=us-ascii
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 - You care about security. So do we.
--0-516013604-1105729950=:12413--
|
|
| | From: | Geoffrey | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 14:32:48 -0500 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | Michael Fuhr | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 12:57:03 -0700 |
|
|
| 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 <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/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Sean Davis | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 15:05:08 -0500 |
|
|
|
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | sarlav kumar | | Subject: | Re: SQL from shell script | | Date: | Fri, 14 Jan 2005 12:32:30 -0800 (PST) |
|
|
| --0-214583442-1105734750=:67391
Content-Type: text/plain; charset=us-ascii
> 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.
--0-214583442-1105734750=:67391
Content-Type: text/html; charset=us-ascii
> 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.
--0-214583442-1105734750=:67391--
|
|
|
