full record json output in 9.2 with valid geojson output

[robe]

9.2 now has native json support. YES. It has a json type and I can output a whole dataset as a single json object with this SWEET command.

CREATE TABLE test(gid serial primary key, title text, geog geography(Point,4326));

INSERT INTO test(title, geog) VALUES('My neck of the woods', ST_GeogFromText('POINT(-71.057811 42.358274)'));

INSERT INTO test(title, geog) VALUES('some other neck of the woods', ST_GeogFromText('POINT(42.358274 -71.057811 )'));

SELECT array_to_json(array_agg(t))
FROM test As t

Ah but our beautiful geography which we have grown to love is not outputting using our json function. We need to rectify this.

 [{"gid":1,"title":"My neck of the woods","geog":"0101000020E61000005796E82CB3C3
51C0E98024ECDB2D4540"},{"gid":2,"title":"some other neck of the woods","geog":"0
101000020E6100000E98024ECDB2D45405796E82CB3C351C0"}]

I'm hoping its as trivial as defining a CAST for geography/geometry to json using our ST_AsGeoJSON functions. I'll test that theory out next.

[robe]

Seems it's not as trivial as that: See my whining:  http://www.postgresonline.com/journal/archives/253-PostgreSQL-9.2-native-json-type-support.html

It's still a nice feature and has a fairly easy work around (which allows you to control the rounding). I have to verify it produces that right output, but looks right to me.

SELECT array_to_json(array_agg(t),true) As my_places
FROM (SELECT gid, title
   , ST_AsGeoJson(1, geog, 15, 0)::json As geog 
FROM test) As t;

[{"gid":1,"title":"My neck of the woods","geog":{"type":"Point","coordinates":[-71.057811000000001,42.358274000000002]}},
 {"gid":2,"title":"some other neck of the woods","geog":{"type":"Point","coordinates":[42.358274000000002,-71.057811000000001]}}]

[robe]

I think this requires changes in PostgreSQL that are not there yet. Specifically how to handle types that are not built-in. So this probably can't happen before 9.3.