469,927 Members | 1,640 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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

---------------------------(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

Nov 23 '05 #1
2 4844
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

Nov 23 '05 #2
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

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Daniel Schuchardt | last post: by
7 posts views Thread by Willem Herremans | last post: by
4 posts views Thread by Brendan Jurd | last post: by
15 posts views Thread by Dino Vliet | last post: by
1 post views Thread by Josué Maldonado | last post: by
2 posts views Thread by Russ Brown | last post: by
5 posts views Thread by David Thielen | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.