Thread: "echo"ing a psql command in a bash script

Hi all!
I'm writing a script that presents the user with a numbered lines
menu, each line corresponding to a <case n> which executes a psql
command. As the psql-commands are very similar to each other (all of
them have the structure:

1.- psql mydb -x -c "SELECT * FROM tb_nm WHERE col_nm LIKE '%$k_r%'"  )

I thought it was possible to shorten it, initializing a str with said
command at the beginning of the script and limiting the various case
lines to defining the col_nm and the $k_r to be searched, i.e.:

2. -
(once for all):
cmnd=echo psql mydb -x -c "SELECT * FROM tb_nm WHERE $col_nm LIKE '%$k_r%'"

(in each <case n>):
col_nm="....."
echo $cmnd
---------------------------
The point is that:
when I test command "1", the query result appears immediately on the
screen and disappears (leaving room to the script menu), when I press
"q"; i.e., the environment remains that of psql until I press "q", which
seems to be a correct behaviour;

whereas, testing command "2", first appears the menu again (as if the
query had been unsuccessfull) and then (after pressing Enter - which
normally would cause exiting the script) the query result shows.

Could somebody throw some light on this issue?
Thanks for your attention.
    Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.     \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]     (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that.             )=(
 Do something you aren't good at!" (used to say Henry Miller) ]

Ennio-Sr said:

> (once for all):
> cmnd=echo psql mydb -x -c "SELECT * FROM tb_nm WHERE $col_nm LIKE '%$k_r%'"

That will be executed immediately, and give you an error.

> (in each <case n>):
> col_nm="....."
> echo $cmnd

I'm surprised you get anything.  AFAIK this should parse the files in your
directory into column names.  Maybe you are doing something slightly different?

Rather than setting the $cmnd variable, try using something like this:

echo mydb -x -c \"SELECT "*" FROM tb_nm WHERE $col_nm LIKE \'%$k_r%\'\" |
xargs psql

I'm not sure where you are storing your column names,  but if you can pipe it
as a list into the above you won't need to rum a loop in your script.  There
are a thousand ways to do this,  but most important is remember to quote the
"*" so the shell doesn't parse it.  BTW You did not say, so I'm also assuming
you are using "bash" or "sh" since that's what this looks like. :-)

Best,

Jim

* Jim Wilson <jimw@kelcomaine.com> [030904, 14:39]:
> Ennio-Sr said:
>
> > (once for all):
> > cmnd=echo psql mydb -x -c "SELECT * FROM tb_nm WHERE $col_nm LIKE '%$k_r%'"
>
> That will be executed immediately, and give you an error.

In fact, yes, you're right! it gives <ERROR: parser: parse error at or
near "'" > ...but after the second Enter, shows the result.
....
>
> > (in each <case n>):
> > col_nm="....."
> > echo $cmnd
>
> I'm surprised you get anything.  AFAIK this should parse the files in your
> directory into column names.  Maybe you are doing something slightly different?

Oops: the show repetition after exiting (q) psql was due to a 'normal'
psql select left over between the lines of my script. Sorry for that,
althoug it helped me learn something new ... :-)

>
> Rather than setting the $cmnd variable, try using something like this:
>
> echo mydb -x -c \"SELECT "*" FROM tb_nm WHERE $col_nm LIKE \'%$k_r%\'\" |
> xargs psql

This works, but the pager doesn't! Whereas my former cmnd= .... seems to
work better, although I had to re-site it [See my script below ...]
>
> I'm not sure where you are storing your column names,

I'm not sure how to interpret that: if you refer to the various $col_nm
only one per case is involved and chosen for each case [see below]

> but if you can pipe it
> as a list into the above you won't need to rum a loop in your script.  There
> are a thousand ways to do this,  but most important is remember to quote the
> "*" so the shell doesn't parse it.  BTW You did not say, so I'm also assuming
> you are using "bash" or "sh" since that's what this looks like. :-)

This is again my fault! I'm using bash script, under Debian/Woody k
2.2.22 and PostgreSQL 7.2.1

And this is an extract from my script:

-------------------------- quote -------------------------

#!/bin/sh
bold=`tput smso`
offbold=`tput rmso`
clear
while test 1 -gt 0; do

   echo ""
   echo "$bold     SCEGLI IL CAMPO SU CUI EFFETTUARE LA RICERCA $offbold"
## I                      (Choose the field to search on)
   echo ""
   echo "     0 - n_prog        |  14 - car_tec_ed"
   [ ..... ]
   echo "    13 - n_pag_testo       |  27 - dummy"

             echo "                                 "
   echo "$bold        (Per uscire premere Enter)        $offbold"
   echo ""
   echo "  Inserire il numero corrispondente al programma desiderato: "
##
   read key

# ++++
# MY FIRST ATTEMPT :
#--->cmnd=echo psql mydb -x -a -c "select * from bib_lt where $k_c like '%$k_r%'"
# Does not perform well: gives parsing error etc ....

# ++++

     case "$key" in
     "")
        exit 1
    ;;

     0)

        [... cut ...]

 # THIS IS AN EXAMPLE OF WHAT GOES ON IN EACH OF THE 27 CASES:
     4)
        echo "            Search by author   "
    echo "  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
    echo " Input author's name: " ; read k_r
        psql mydb -x -c "select * from bib_lt where autore like\
    upper('%$k_r%')"
        ;;

        [... cut ...]

# HERE I TESTED THE NEW WAY OF DOING IT:

     7)
        echo "              Search by publishers "
    echo "  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
    echo " Inserire l'editore da ricercare: " ; read k_r
###    psql mydb -x -a -c "select * from bib_lt where editore like '%$k_r%'"

k_c="editore"
echo $cmnd    ## <---

        ;;

        [ ... cut ...]
    27)
        echo "              Ricerca per dummy (R/"") "
    echo "  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
    echo " Inserire l'argomento da ricercare: " ; read k_r
        psql mydb -x-a-c "select * from bib_lt where dummy like '%$k_r%'"
        ;;


   qQ)
        exit 1  # esci dal loop
        ;;

   esac

### THIS IS THE CORRECT SITE WHERE TO PUT IT!
---------------------------------------------
#echo mydb -x  -c \"select "*" from bib_lt where $k_c like \'%$k_r%\'\"
#| xargs psql    # suggested by Jim Wilson -> OK, works but scrolls
# ---> endlessly (pager doesn't work!) <---

# This also works (either with "*" or as follows):
cmnd=echo psql mydb -x  -c "select * from bib_lt where $k_c like '%$k_r%'"
# and, what's more, the pager goes fine!

done

------------------------------ unquote ---------------------------------

As you can see, placing the cmnd definition at the end of the loop gives
a correct result: I would never have thought it was possible to do that,
as the token '$cmnd' is called (within the loop) before its definition
has been given. But that's it| ... :-)

BTW, can you suggest any particular reading about psql commands and how
they interact with bash scripts? I was unable to find anything on pg
docs, including B.Momjan book, and on Rute's Advanced Scripting.
One of the issues I could not solve was : How can I put two psql
commands on the same script line?

Anyway, thank you very much for your attention, Jim.
All the best,
    Ennio

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.     \\?//
 Fa' qualche cosa di cui non sei capace!"   (diceva Henry Miller) ]     (°|°)
[Why to use Win$ozz (I say) if ... "even a fool can do that.             )=(
 Do something you aren't good at!" (used to say Henry Miller) ]