SQL*Plus, the Oracle database‘s command-line tool, provides the SPOOL command to “store query results in a file.” The next screen snapshot shows SPOOL used in SQL*Plus to spool the listing of user tables to a file called C:\pdf\output.txt.
Both the executed query and the results of the query have been spooled to the file output.txt as shown in the next listing of that file.
Oracle’s SQL*Plus’s SPOOL-ed output.txt
SQL> select table_name from user_tables; TABLE_NAME ------------------------------ REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY PEOPLE NUMERAL NUMBER_EXAMPLE COUNTRIES 10 rows selected. SQL> spool off
PostgreSQL‘s command-line tool, psql, provides functionality similar to SQL*Plus’s SPOOL with the \o (\out) meta-command. The following screen snapshot shows this in action in psql.
The file output.txt written via psql‘s \o meta-command is shown in the next listing.
List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | albums | table | postgres (1 row)
Only the results of the query run in psql are contained in the generated output.txt file. The query itself, even the longer query produced by using \set ECHO_HIDDEN on, is not contained in the output.
One approach to ensuring that the query itself is output with the query’s results written to the file is to use the \qecho meta-command to explicitly write the query to the spooled file before running the query. This is demonstrated in the next screen snapshot.
Using \qecho in conjunction with \o does place the query itself in the written file with the query’s results as shown in the next listed output.
select * from albums;
title | artist | year
---------------------------+-----------------+------
Back in Black | AC/DC | 1980
Slippery When Wet | Bon Jovi | 1986
Third Stage | Boston | 1986
Hysteria | Def Leppard | 1987
Some Great Reward | Depeche Mode | 1984
Violator | Depeche Mode | 1990
Brothers in Arms | Dire Straits | 1985
Rio | Duran Duran | 1982
Hotel California | Eagles | 1976
Rumours | Fleetwood Mac | 1977
Kick | INXS | 1987
Appetite for Destruction | Guns N' Roses | 1987
Thriller | Michael Jackson | 1982
Welcome to the Real World | Mr. Mister | 1985
Never Mind | Nirvana | 1991
Please | Pet Shop Boys | 1986
The Dark Side of the Moon | Pink Floyd | 1973
Look Sharp! | Roxette | 1988
Songs from the Big Chair | Tears for Fears | 1985
Synchronicity | The Police | 1983
Into the Gap | Thompson Twins | 1984
The Joshua Tree | U2 | 1987
1984 | Van Halen | 1984
(23 rows)The main downside to use of \qecho is that it must be used before every statement to be written to the output file.
The psql variable ECHO can be set to queries to have “all SQL commands sent to the server [sent] to standard output as well.” This is demonstrated in the next screen snapshot.
Unfortunately, although setting the psql variable ECHO to queries leads to the query being output along with the results in the psql window, the query is not written to the file by the \o meta-command. Instead, when \o is used with ECHO set to queries, the query itself is printed out again to the window and the results only are written to the specified file. This is because, as the documentation states (I added the emphasis), the \o meta-command writes “the query output … to the standard output.” This is demonstrated in the next screen snapshot.
I have not been able to figure out a way to easily use the \o meta-data command and have both the query and its results written to the file without needing to use \qecho. However, another approach that doesn’t require \qecho is to run not try to spool the file output from within psql interactively, but to instead execute a SQL script input file externally.
For example, if I make an input file called input.sql that consisted only of a single line with query
select * from albums;
I could run
psql with the command
psql -U postgres --echo-queries < input.txt > outputWithQuery.txt to read that single-line file with the query and write output to the outputWithQuery.txt file. The --echo-queries option works like the \set ECHO queries from within psql and running this command successfully generates the prescribed output file with query and results. The following screen snapshot and the code listing following that demonstrate this.
outputWithQuery.txt
select * from albums;
title | artist | year
---------------------------+-----------------+------
Back in Black | AC/DC | 1980
Slippery When Wet | Bon Jovi | 1986
Third Stage | Boston | 1986
Hysteria | Def Leppard | 1987
Some Great Reward | Depeche Mode | 1984
Violator | Depeche Mode | 1990
Brothers in Arms | Dire Straits | 1985
Rio | Duran Duran | 1982
Hotel California | Eagles | 1976
Rumours | Fleetwood Mac | 1977
Kick | INXS | 1987
Appetite for Destruction | Guns N' Roses | 1987
Thriller | Michael Jackson | 1982
Welcome to the Real World | Mr. Mister | 1985
Never Mind | Nirvana | 1991
Please | Pet Shop Boys | 1986
The Dark Side of the Moon | Pink Floyd | 1973
Look Sharp! | Roxette | 1988
Songs from the Big Chair | Tears for Fears | 1985
Synchronicity | The Police | 1983
Into the Gap | Thompson Twins | 1984
The Joshua Tree | U2 | 1987
1984 | Van Halen | 1984
(23 rows)I don’t know how to exactly imitate SQL*Plus’s writing of the query with its results from within SQL*Plus in psql without needing to add \qecho meta-commands, but passing the input script to psql with the --echo-queries option works very similarly to invoking and spooling the script from within SQL*Plus.
| Reference: | SPOOLing Queries with Results in psql from our JCG partner Dustin Marx at the Inspired by Actual Events blog. |
Hi Dustin,
Thank you very much for you research and sharing the knowledge. regarding the last part how do we combine both “I don’t know how to exactly imitate SQL*Plus’s writing of the query with its results from within SQL*Plus in psql without needing to add \qecho meta-commands” We have a way of achieving this . Once you logged into database using PSQL all we need to do is pass the script as input file.
\i C:/Users/nkanchet/temp/input.sql;