Thread: psql \o weirdness

I'm having a problem suppressing output from some of my cron scripts and
java code. One file of sql scripts (eod-misc.sql)
is called by a shell script (update.sh). Within eod-misc, various sql
commands and home rolled functions are called eg
  SELECT * FROM myFunction();
which generates a lot of output (77000 lines for one of them), which
gets logged and mailed to the cron user. If I change
the line in the sql script to
  SELECT * FROM myFunction() \o /dev/null;
output from this is suppressed. HOWEVER, I get an error when it tries to
process the next line;
  psql:/usr/local/pgsql/quiet.sql:2: ERROR:  syntax error at or near
"SELECT" at character 26


Here's a simple test case:
-------------------------------------

(1) I create and populate a table
CREATE TABLE testTable (id int, name text);
INSERT INTO testTable (id, name) VALUES (1, 'One');
INSERT INTO testTable (id, name) VALUES (2, 'Two');


(2) I create shell and sql scripts
quiet.sh
-----------
#!/bin/sh
psql -d testdb -f '/usr/local/pgsql/quiet.sql'

quiet.sql
-----------
SELECT * FROM testTable \o /dev/null;

verbose.sh
-----------
#!/bin/sh
psql -d testdb -f '/usr/local/pgsql/verbose.sql'

verbose.sql
-----------
SELECT * FROM testTable;


(3) I run the scripts
postgres@smiley:~$ sh quiet.sh
postgres@smiley:~$

postgres@smiley:~$ sh verbose.sh
 id | name
----+------
  1 | One
  2 | Two
(2 rows)

(4) * I modify the sql script adding another command
quiet.sql
-----------
SELECT * FROM testTable \o /dev/null;
SELECT name FROM testTable WHERE id = 1;

(5) I run the shell script again, and it breaks.
postgres@smiley:~$ sh quiet.sh
psql:/usr/local/pgsql/quiet.sql:2: ERROR:  syntax error at or near
"SELECT" at character 26
postgres@smiley:~$

Anyone have any ideas on how to get this working?

Thanks
Ron

Ron St-Pierre wrote:
> the line in the sql script to
>   SELECT * FROM myFunction() \o /dev/null;
> output from this is suppressed. HOWEVER, I get an error when it tries
> to process the next line;
>   psql:/usr/local/pgsql/quiet.sql:2: ERROR:  syntax error at or near
> "SELECT" at character 26

No, what actually happens is that the first SELECT is never executed,
because there is no terminating semicolon.  The semicolon at the end of
the line belongs to the \o command.  So when it processes the next
line, it appends the text to the previous command and tries to execute
that invalid concatenation.  What you really want to use instead is the
\g command.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

>Ron St-Pierre wrote:
>
>
>>the line in the sql script to
>>  SELECT * FROM myFunction() \o /dev/null;
>>output from this is suppressed. HOWEVER, I get an error when it tries
>>to process the next line;
>>  psql:/usr/local/pgsql/quiet.sql:2: ERROR:  syntax error at or near
>>"SELECT" at character 26
>>
>>
>
>No, what actually happens is that the first SELECT is never executed,
>because there is no terminating semicolon.  The semicolon at the end of
>the line belongs to the \o command.  So when it processes the next
>line, it appends the text to the previous command and tries to execute
>that invalid concatenation.  What you really want to use instead is the
>\g command.
>
>
You're right Peter, the \g works. Thanks for the explanation, I can now
see why using \o wouldn't work.

Ron