Re: JSON output

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(at)postnewspapers(dot)com(dot)au> wrote:

> From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
> Subject: Re: [GENERAL] JSON output
> To: "Dennis Gearon" <gearond(at)sbcglobal(dot)net>, "PG-General Mailing List" <pgsql-general(at)postgresql(dot)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/
>