Thread: sql files in psql ?

Hello everyone,

I'm building a small newspaper informations aggregator around postgresql
8.3 and Ubuntu 9.10.
Starting from a previous experience on Oracle DB, I've built
and tested a bunch of functions, triggers, SQL scripts etc...
that work flawlessly (almost !) when I submit them by hand
in psql; now I want to pack everything in a nice shell script,
with proper error control, automated file shuffling,
predefined queries in text files and so on, but I cannot
convince psql that the SQL commands (select, insert or
copy...) it has to handle come from a file.
I can submit  ("\i filename" or "psql --file filename")
files that contains commands, eg starting with "\" but
psql does not understand the same sintax if tne file
contains a simple "SELECT * FROM tablex".

Am I uber-newbie or is this a known limitation of psql?
And, in this case, there is something to overcome this
limit and afford my non-techie user to launch a simple
command to print her data or to bulk update tables ?

P.S. Initially I discarded the use of procedural
languages (C or ...) for a quick and simpler solution !

Thanks in advance

Fabio, from North Italy

Mariateresa L <mariateresa.ll@bresciaonline.it> writes:
> ... I want to pack everything in a nice shell script,
> with proper error control, automated file shuffling,
> predefined queries in text files and so on, but I cannot
> convince psql that the SQL commands (select, insert or
> copy...) it has to handle come from a file.
> I can submit  ("\i filename" or "psql --file filename")
> files that contains commands, eg starting with "\" but
> psql does not understand the same sintax if tne file
> contains a simple "SELECT * FROM tablex".

Really?  Works for most people.

> Am I uber-newbie or is this a known limitation of psql?

Well, you need to learn something about submitting trouble reports.
Show us exactly what you did (including what's in the file) and
exactly what error messages you got, rather than telling us what your
interpretation of the problem is.

            regards, tom lane

Mariateresa L <mariateresa.ll@bresciaonline.it> wrote:

> [...] I cannot
> convince psql that the SQL commands (select, insert or
> copy...) it has to handle come from a file.
> I can submit  ("\i filename" or "psql --file filename")
> files that contains commands, eg starting with "\" but
> psql does not understand the same sintax if tne file
> contains a simple "SELECT * FROM tablex".

If I understand what you're saying, I can't reproduce the problem.

With a "tmp.sql" containing:

  create table nameo (nameo text);
  insert into nameo (nameo) VALUES ('bingo'), ('bonzo'), ('bang');
  select * from nameo;

I see this behavior:

psql --file tmp.sql
CREATE TABLE
INSERT 0 3

-------
(3 rows)
 bang
 bingo
 bonzo
 nameo


I'm on ununtu jaunty, using bash and PostgreSQL 8.3.7.

> P.S. Initially I discarded the use of procedural
> languages (C or ...) for a quick and simpler solution !

Myself, I wouldn't try to put together a db-backed app without
a glue language (e.g. perl).

On 05/31/2010 05:14 PM, Tom Lane wrote:
> Mariateresa L<mariateresa.ll@bresciaonline.it>  writes:
>
>> ... I want to pack everything in a nice shell script,
>> with proper error control, automated file shuffling,
>> predefined queries in text files and so on, but I cannot
>> convince psql that the SQL commands (select, insert or
>> copy...) it has to handle come from a file.
>> I can submit  ("\i filename" or "psql --file filename")
>> files that contains commands, eg starting with "\" but
>> psql does not understand the same sintax if tne file
>> contains a simple "SELECT * FROM tablex".
>>
> Really?  Works for most people.
>

The problem is probably in an un-escaped "*" character in the script.
Shell will translate that into the list of files and really mess up the
underlying SQL command.  I must admit, though, that the problem
description is a bit vague and that without a much more precise script
template and the resulting error message, I am unable to provide any
more useful information. The OP talks both about psql and \i command, as
well as the shell script. I suspect that therein lies the reason for the
fatal combination.


--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
The Leader in integrated Media Intelligence Solutions

Well, I thank everyone for the quick answers, and my apologies for the
duplicate message (my mistake).

I've found the culprit ! I wrote some text files with simple commands
for psql (\dt, \l etc...) then, having so checked my first script, I
replaced the test ones with other files containing SQL queries; these
gave me "syntax error at or near SELECT", with the error caret pointing
left of S of SELECT.
After your skeptical remarks (thanks !) I checked both kind of files
with an hex editor, finding that some of the "SQL" ones had a couple of
non printable characters before the correct string, and gedit ignores
them without warning.
These files where not been written afresh; have suffered some migration
from a disk to another a couple of times in the last months and probably
have been corrupted.

Sorry for having bothered this list and thank again to everyone.

Fabio

Il 31/05/2010 23.37, Joe Brenner ha scritto:
.
.
>
> If I understand what you're saying, I can't reproduce the problem.
>
> With a "tmp.sql" containing:
>
>    create table nameo (nameo text);
>    insert into nameo (nameo) VALUES ('bingo'), ('bonzo'), ('bang');
>    select * from nameo;
>
> I see this behavior:
>
> psql --file tmp.sql
> CREATE TABLE
> INSERT 0 3
>
> -------
> (3 rows)
>   bang
>   bingo
>   bonzo
>   nameo
>
>
> I'm on ununtu jaunty, using bash and PostgreSQL 8.3.7.
>
>> P.S. Initially I discarded the use of procedural
>> languages (C or ...) for a quick and simpler solution !
>
> Myself, I wouldn't try to put together a db-backed app without
> a glue language (e.g. perl).
>