Thread: psql: Is it possible to run a query from a *sql file?

[Running PG 7.4.7-5 on GNU-Linux Debian Sarge]

Hi all,
I'm wondering if it is possible to run a query from a .sql file, so as
to automate certain searches from console.
I've been unsuccessfully trying something like this simple one:

------------
\! echo "insert key: ";read key; export $key;
SELECT autore, titolo, editore FROM bibl WHERE autore like '%[$key]%'
------------

Most likely the answer is 'no', as psql is an 'interactive'
application.
There seems to be no communication between the shell (where you digit
the key) and psql: the key vanishes somewhere ... ;(

Regards,
    Ennio

BTW: Somebody knows where does pgaccess store its queries?

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

Ennio-Sr <nasr.laili@tin.it> writes:
> I've been unsuccessfully trying something like this simple one:

> \! echo "insert key: ";read key; export $key;
> SELECT autore, titolo, editore FROM bibl WHERE autore like '%[$key]%'

Perhaps you want something like

regression=# \echo prompt
prompt
regression=# \set foo `read val; echo "'$val'"`
qwerty
regression=# select :foo;
 ?column?
----------
 qwerty
(1 row)

although on the whole I think you'd be better off turning this around:
write it as a shell script and invoke psql for individual steps,
rather than the other way round.  It's hard to envision a script that
needs user interaction and won't shortly thereafter need control
structures etc.

            regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [010505, 13:24]:
> Ennio-Sr <nasr.laili@tin.it> writes:
> > I've been unsuccessfully trying something like this simple one:
> [...]
>
> Perhaps you want something like
> [...]
> regression=# \set foo `read val; echo "'$val'"`
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That as it!
> [...]
> although on the whole I think you'd be better off turning this around:
> write it as a shell script and invoke psql for individual steps,
> rather than the other way round.  It's hard to envision a script that
> needs user interaction and won't shortly thereafter need control
> structures etc.
>
>             regards, tom lane

Hi Tom,
thanks for your quick and helpful answer.
I've already some nice shell scripts that work wonderfully; I was just
trying to learn a litte bit more about the use of the \i file.sql.
Your explanation helped me understand the whereabouts :-)
As an example, this works perfectly now:

# op_srch.sql (called from within psql with command => \i op_srch.sql:
-------------------
\echo Insert search Filed and Key: ;
\set fld `read fld; echo "$fld"`
\set key `read key; echo "'%$key%'"`
\echo :fld
\echo :key
SELECT autore, titolo, editore FROM bibl WHERE unaccent(lower(:fld))
like lower(:key);
-------------------

Thanks again.
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 use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]

* Tom Lane <tgl@sss.pgh.pa.us> [010505, 13:24]:

> rather than the other way round.  It's hard to envision a script that
> needs user interaction and won't shortly thereafter need control
                                                      ^^^^^^^^^^^^
> structures etc.
  ^^^^^^^^^^^^^^^
Sorry! I forgot to ask it in my previous answer:
What does that exactly mean?
Where can I read for a better understanding?
Regards, 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 use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]

On Mon, May 02, 2005 at 02:38:39 +0200,
  Ennio-Sr <nasr.laili@tin.it> wrote:
> * Tom Lane <tgl@sss.pgh.pa.us> [010505, 13:24]:
>
> > rather than the other way round.  It's hard to envision a script that
> > needs user interaction and won't shortly thereafter need control
>                                                       ^^^^^^^^^^^^
> > structures etc.
>   ^^^^^^^^^^^^^^^
> Sorry! I forgot to ask it in my previous answer:
> What does that exactly mean?
> Where can I read for a better understanding?
> Regards, Ennio.

Statements that provide for conditional and repeated execution of other
statements. psql dosn't have that, but your shell almost certainly does.