Re: Data dumps to files - best methods?

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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ralf Schuchardt
> Sent: 23 July 2010 01:37 PM
> To: Machiel Richards
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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