psql \o weirdness

Started by Ron St-Pierreover 21 years ago3 messagesgeneral
Jump to latest
#1Ron St-Pierre
rstpierre@syscor.com

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

#2Peter Eisentraut
peter_e@gmx.net
In reply to: Ron St-Pierre (#1)
Re: psql \o weirdness

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/

#3Ron St-Pierre
rstpierre@syscor.com
In reply to: Ron St-Pierre (#1)
Re: psql \o weirdness

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