psql \o weirdness

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: psql \o weirdness
Date: 2004-09-02 16:58:15
Message-ID: 41375127.40008@syscor.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)smiley:~$ sh quiet.sh
postgres(at)smiley:~$

postgres(at)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(at)smiley:~$ sh quiet.sh
psql:/usr/local/pgsql/quiet.sql:2: ERROR: syntax error at or near
"SELECT" at character 26
postgres(at)smiley:~$

Anyone have any ideas on how to get this working?

Thanks
Ron

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron St-Pierre 2004-09-02 17:09:52 Re: [NOVICE] pgAccess via ssh?
Previous Message Mike 2004-09-02 16:51:09 Re: pgAccess via ssh?