psql \o weirdness - Mailing list pgsql-general

From Ron St-Pierre
Subject psql \o weirdness
Date
Msg-id 41375127.40008@syscor.com
Whole thread Raw
Responses Re: psql \o weirdness
List pgsql-general
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



pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: Display of text fields
Next
From: Ron St-Pierre
Date:
Subject: Re: [NOVICE] pgAccess via ssh?