psql \o weirdness

[Ron St-Pierre]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Peter Eisentraut]

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/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Ron St-Pierre]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html