Writing the output of sql query to a file

Question

I have an SQL query:

SELECT 'DROP TABLE ' || tablename
  FROM pg_catalog.pg_tables where tablename like 'r_395%';

whose output is this:

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

I am using entity manager to execute this query:

StringBuffer query = new StringBuffer();

query.append("SELECT 'DROP TABLE ' || tablename
                FROM pg_catalog.pg_tables where tablename like 'r_395%'");            
entityManager.createNativeQuery(query.toString()).executeUpdate();

I want to write this output to a file. If it was a mere a text file I would use filewriter and buffered writer. But now it is an SQL generated file, I am a little bit confused. Is there a way to achieve this in Java?

Answer 1

If you want to write a file locally (on the machine of the database) there is also the very fast and simple COPY for a case like this:

COPY $$SELECT 'DROP TABLE ' || tablename
         FROM pg_catalog.pg_tables
        WHERE tablename ~~ 'r_395%'$$ TO /path/to/myfile;

I use dollar-quoting here, to avoid escaping the single quotes.