Re: SQL from shell script

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.
>