Re: SQL from shell script - Mailing list 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@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 <sdavis2@mail.nih.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.
>