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

Re: SQL from shell script

From: "Schuhmacher, Bret" <Bret(dot)Schuhmacher(at)Aspect(dot)com>
To: "sarlav kumar" <sarlavk(at)yahoo(dot)com>,"Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>
Cc: "pgsqlnovice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: SQL from shell script
Date: 2005-01-14 19:26:03
Message-ID: 98E4F4D46DACD0479C96D7356D5C37356B0333@sac1exch3.aspect.com (view raw or flat)
Thread:
Lists: pgsql-novice
Pardon me if I'm arriving late to the thread and this has been shot down
already, but it appears psql has cmd line args for accepting files:
$ psql --help
This is psql 7.4.3, the PostgreSQL interactive terminal.

Usage:
  psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
  -d DBNAME       specify database name to connect to (default:
"xxxxxxxx")
  -c COMMAND      run only single command (SQL or internal) and exit
  -f FILENAME     execute commands from file, then exit


Does this feature not work?

Rgds,

Bret
 

> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org 
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of sarlav kumar
> Sent: Friday, January 14, 2005 2:13 PM
> To: Sean Davis
> Cc: pgsqlnovice
> Subject: Re: [NOVICE] 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 
> <http://us.rd.yahoo.com/mail_us/taglines/security/*http://prom
> otions.yahoo.com/new_mail/static/protection.html>  - You care 
> about security. So do we.
> 

pgsql-novice by date

Next:From: GeoffreyDate: 2005-01-14 19:32:48
Subject: Re: SQL from shell script
Previous:From: sarlav kumarDate: 2005-01-14 19:12:30
Subject: Re: SQL from shell script

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