Re: Data dumps to files - best methods?

[Ralf Schuchardt <rasc@xxxxxx>]

Hi,

Am 26.07.2010 um 11:46 schrieb Machiel Richards:

       Is there a method of outputting / dumping the data into .xml format as part of the scripts / crons/ db processes?

It depends on your requirements of the xml file format.

psql can output data in html/xhtml format (--html switch), which might or might not be enough for you.

$ psql --html -c "select * from atable" adb > myfile.xhtml.part

Another way is to use the function described here: http://www.postgresql.org/docs/8.4/interactive/functions-xml.html#FUNCTIONS-XML-MAPPING

"query_to_xml" generates a proper xml version of the query results.

Ralf

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Ralf Schuchardt

Sent: 23 July 2010 01:37 PM
To: Machiel Richards
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Data dumps to files - best methods?

 

Hi,

 

Am 23.07.2010 um 10:32 schrieb Machiel Richards:

       As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. 

     However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files?

 

You can use the "\copy" command in psql to export the result of query into a file. For example:

 

> psql -c "\\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header" adb

 

will copy all rows from "atable" in "adb" to "myfile.csv" in csv format.

 

 

Ralf