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/