Re: SQL from shell script

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