No, don't need all the set stuff, just the result of either:
1/ A SELECT * from a view that is a query,
2/ Or the result of the same query.
Seems like it'd take a lot of horsepower to:
A/ Do the query
B/ Convert it to XML
C/ Convert it to JSON
D/ Then insert it into ElasticSearch.
I'm either going to use C++/Perl/PHP/JAVA doing a simple query to a result array, then convert it and send it to
ElasticSearch(via a http socket)
I'll start with PHP.
Probably going to have to use a cursor (one more thing to learn) as eventually, I'll have 'billions and billlions' of
records,Mwah Ha Ha <evil grin accompaniment />
Dennis Gearon
Signature Warning
----------------
EARTH has a Right To Life,
otherwise we all die.
Read 'Hot, Flat, and Crowded'
Laugh at http://www.yert.com/film.php
--- On Sun, 9/12/10, Craig Ringer <craig@postnewspapers.com.au> wrote:
> From: Craig Ringer <craig@postnewspapers.com.au>
> Subject: Re: [GENERAL] JSON output
> To: "Dennis Gearon" <gearond@sbcglobal.net>, "PG-General Mailing List" <pgsql-general@postgresql.org>
> Date: Sunday, September 12, 2010, 8:57 PM
> 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/
>