On 13/09/10 10:36, Dennis Gearon wrote: > I'm trying to import from a postgres database (which will work in parallel) to a ElasticSearch databse (JSON input). > > Is there anyway to get JSON output from postgres? Not out of the box. The closest you'll get, AFAIK, is XML output from the SQLXML functions. http://www.postgresql.org/docs/current/static/datatype-xml.html http://www.postgresql.org/docs/current/static/functions-xml.html In particular, table_to_xml, query_to_xml or cursor_to_xml will probably be helpful. Once you have the XML, you can use any existing tool for an XML-to-JSON transform, possibly in a plpython/plperl stored procedure within PostgreSQL its self. classads=> select * FROM table_to_xml( 'ad_status'::regclass, true, false, ''); table_to_xml ------------------------------------------------------------------- <ad_status xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> <row> <id>0</id> <name>OK</name> </row> <row> <id>1</id> <name>HELD</name> </row> <row> <id>2</id> <name>CANCELLED</name> </row> </ad_status> If you need something more complex than table contents or the end result of a query, you'll have to roll your own. There isn't anything to do ORM-like extraction of join results into nested sets if you need that. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
