Skip site navigation (1) Skip section navigation (2)

Re: SQL from shell script

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: sarlav kumar <sarlavk(at)yahoo(dot)com>
Cc: pgsqlnovice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL from shell script
Date: 2005-01-14 20:05:08
Message-ID: 96EAF7DA-6667-11D9-A6EF-000D933565E8@mail.nih.gov (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: sarlav kumarDate: 2005-01-14 20:32:30
Subject: Re: SQL from shell script
Previous:From: Michael FuhrDate: 2005-01-14 19:57:03
Subject: Re: SQL from shell script

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group