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;
psql with the command
psql -U postgres --echo-queries < input.txt > outputWithQuery.txt
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.
No comments:
Post a Comment