input query from file output result to file

Steve_Angell

I have a complex query I am testing some performance issues against, I want to load the query from file and be able to explain the query, profile the query etc but I also want to output the result from the query / explain plan to another file for comparisons

Abhishek_Rana

Hi Steve,

Below are two options for your requirement:

1.

 To execute commands from a specific file: 
 ===============================

\i <filename>

This command reads input from the file filename and executes it as though it had been typed on the keyboard.

Eg:
dbadmin=> \i query1.sql 

2.

 To create a file of output of commands: 
 ======================================

\o meta-command is used to control where vsql directs its query output. 

The output can be written to a file, piped to a shell command, or sent to the standard output.
\o FILE sends all subsequent query output to FILE.
Eg: 
dbadmin> \o a.out
dbadmin> execute set of queries .....
dbadmin> \o

Regards'

Abhishek

Steve_Angell

Hi Abhishek, yep I know how to get the individual bits to work what I want to do is do it in one command - is this possible ?

I thought as follows would work but it didn't seem to:

dbadmin> \i Input_Query.sql \o output_result.txt

With above all i got was a blank output file ..

Thanks

Abhishek_Rana

I think you can use below small workaround trick for this:

dbadmin=> cat a.sql | vsql -p 5433 -w password -o out.txt

where , a.sql is sql file.
And      out.txt is file which stores the output.

Regards'

Abhishek

Daniel_Leybovic

>> is this possible ?
Everything is possible if to think a little, just change an order of meta commands:

dbadmin=> \o output_result.txt \i Input_Query.sql

Example

daniel=> \! ls -l /tmp/out.csv

ls: cannot access /tmp/out.csv: No such file or directory

daniel=> \! cat /tmp/query.sql

select * from visits limit 3;

daniel=> \o /tmp/out.csv \i /tmp/query.sql

daniel=> \! cat /tmp/out.csv

         in          |         out         | person 

---------------------+---------------------+--------

 2013-11-01 22:52:34 | 2013-11-01 23:07:24 | Jager

 2013-11-02 22:55:22 | 2013-11-02 23:04:36 | James

 2013-11-03 22:03:45 | 2013-11-03 23:56:13 | Jager

(3 rows)

daniel=>