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@xxxxxxxxxxxxxxxxxxxxx> wrote: > From: Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> > Subject: Re: JSON output > To: "Dennis Gearon" <gearond@xxxxxxxxxxxxx>, "PG-General Mailing List" <pgsql-general@xxxxxxxxxxxxxx> > 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/ > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
