Thread: SQL from Linux command line

create table2 as select *, case when _cnt_ < 50 then 'low' when _cnt_ >= 50 'high' end as cnt from (select id, count(*) as _cnt_ from table1 where id = 1234 group by id) a;

Scott Geller wrote:
>
> Hello
>
> Hoping for some help on this one....
>
> I need to query postgres, run a count of _specific _records, and write
> to a different table based on the results all from the linux command line.
>
> This feels like it should be simple, but I'm new to postgres and could
> use some help.
>
> I'm good with the SQL part - for the SQL, I would write:
>
>     create table2 as select *, case when _cnt_ < 50 then 'low' when
>     _cnt_ >= 50 'high' end as cnt from (select id, count(*) as _cnt_
>     from table1 where id = 1234 group by id) a;
>
> My questions are:
>
>    1. Would I use psql for this?
>    2. If so, how do I structure the script to run psql from Linux?
>    3. how do I pass the value for the where clause from the command
>       line into the code?
>
> Thanks!
>
> Scott
>
My advice would be simple: use your favorite scripting language for
that. The  ad hoc query tools like psql are not meant for reporting and
batch jobs. I've seen such tools abused  so many times that I lost
count. What people end up with is a lousy report with an unintelligible
"script" that produces it. The script is hard to maintain, depends on
the version and is usually longer than a comparable scripting language
script.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Scott Geller wrote:
> I'm good with the SQL part - for the SQL, I would write:
>
>     create table2 as select *, case when _cnt_ < 50 then 'low' when
>     _cnt_ >= 50 'high' end as cnt from (select id, count(*) as _cnt_
>     from table1 where id = 1234 group by id) a;
>
> My questions are:
>
>    1. Would I use psql for this?
>    2. If so, how do I structure the script to run psql from Linux?
>    3. how do I pass the value for the where clause from the command line
>       into the code?


This might do what you want...

$ cat ./myscript
psql -c "create table2 as select *, \
case when _cnt_ < 50 then 'low' when _cnt_ >= 50 'high' end as cnt \
from (select id, count(*) as _cnt_ from table1 \
where id = $1 group by id) a;"

$ sh ./myscript 1234

Scott Geller wrote:
>
> Thanks Mladen
>
> I'm a novice - if psgl is not a scripting language, can you please
> give me an example of one?  maybe perl?
>
> Thanks

No, psql is not a scripting language, psql is an ad-hoc query utility.
Scripting languages are numerous, among the most popular are Python, PHP
and Perl, but there are others, like Ruby.  I prefer Perl for the CLI
but, on the other hand, there are many people that do not share my
preferences.  The only reason for my preference is the fact that I've
been using Perl since 1994.  All of the above scripting languages, with
the possible exception of Ruby, support PostgreSQL.  I know nothing of
Ruby so I have no idea whether it supports PgSQL or not.
Python is becoming increasingly popular these days, because of its full
blown OO model, which Perl doesn't have. There is some mess with the
versions, like version 3 being slow to gain acceptance, but that should
get resolved soon.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Scott Geller <geller.subscribe@gmail.com> wrote:

>
> Hello
>
> Hoping for some help on this one....
>
> I need to query postgres, run a count of specific records, and write to a
> different table based on the results all from the linux command line.
>
> My questions are:
>
>  1. Would I use psql for this?
>  2. If so, how do I structure the script to run psql from Linux?
>  3. how do I pass the value for the where clause from the command line into the
>     code?

Sure, you can use psql for that, here a simple example:


,----[  my script  ]
| kretschmer@tux:~$ cat test.sh
| #!/bin/bash
|
| d=`date`
|
| psql test -c "select 'today is ' || '$d' || ', and the database says ' || now()"
`----

,----[  running the script  ]
| kretschmer@tux:~$ ./test.sh
|                                           ?column?
| ---------------------------------------------------------------------------------------------
|  today is Sa 10. Jul 08:40:25 CEST 2010, and the database says 2010-07-10 08:40:25.248636+02
| (1 Zeile)
|
| kretschmer@tux:~$
`----


You got it?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> Scott Geller <geller.subscribe@gmail.com> wrote:
> > Hello
> >
> > Hoping for some help on this one....
> >
> > I need to query postgres, run a count of specific records, and write to
> > a different table based on the results all from the linux command
> > line.
> >
> > My questions are:
> >  1. Would I use psql for this?
> >  2. If so, how do I structure the script to run psql from Linux?
> >  3. how do I pass the value for the where clause from the command line
> >  into the
> >
> >     code?

Using Perl would be a lot easier.

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."