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
