Re: 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(at)mail(dot)nih(dot)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.