Re: SQL from shell script

Schuhmacher, Bret

From:	Schuhmacher, Bret
Subject:	Re: SQL from shell script
Date:	Fri, 14 Jan 2005 11:26:03 -0800

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@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.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 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
> > otions.yahoo.com/new_mail/static/protection.html> - You care
> about security. So do we.
>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)