Re: JSON for PG 9.2

2012/1/14 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/12/2012 10:51 AM, Andrew Dunstan wrote:
>>
>>
>>
>> On 01/12/2012 10:44 AM, Pavel Stehule wrote:
>>>
>>> 2012/1/12 Andrew Dunstan<andrew(at)dunslane(dot)net>:
>>>>
>>>>
>>>> On 01/12/2012 09:00 AM, Joey Adams wrote:
>>>>>
>>>>> I wrote an array_to_json function during GSoC 2010:
>>>>>
>>>>>
>>>>>
>>>>> http://git.postgresql.org/gitweb/?p=json-datatype.git;a=blob;f=json_io.c#l289
>>>>>
>>>>> It's not exposed as a procedure called array_to_json: it's part of the
>>>>> to_json function, which decides what to do based on the argument type.
>>>>>
>>>>
>>>> Excellent, this is just the point at which I stopped work last night, so
>>>> with your permission I'll steal this and it will save me a good chunk of
>>>> time.
>>>>
>>> this should be little bit more enhanced to support a row arrays - it
>>> can be merged with some routines from pst tool
>>> http://okbob.blogspot.com/2010/11/new-version-of-pst-collection-is.html
>>>
>>>
>>
>> I will be covering composites.
>>
>>
>
> OK, here's a patch that does both query_to_json and array_to_json, along
> with docs and regression tests. It include Robert's original patch, although
> I can produce a differential patch if required. It can also be pulled from
> <https://bitbucket.org/adunstan/pgdevel>
>
> A couple of things to note. First, the problem about us losing column names
> that I noted a couple of months ago and Tom did a bit of work on is
> exercised by this. We really need to fix it. Example:
>

support SELECT ROW (x AS "real name", y AS "real name") is good idea
and should be used more time than only here.

Regards

Pavel

>   andrew=#  select array_to_json(array_agg(row(z.*)))
>       from (select $$a$$ || x as b,
>
>                y as c,
>                array[row(x.*,array[1,2,3]),
>                      row(y.*,array[4,5,6])] as z
>             from generate_series(1,1) x,
>                  generate_series(4,4) y) z;
>                                  array_to_json
>   -------------------------------------------------------------------------
>     [{"f1":"a1","f2":4,"f3":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}]
>   (1 row)
>
>
> Here we've lost b, c and z as column names.
>
> Second, what should be do when the database encoding isn't UTF8? I'm
> inclined to emit a \unnnn escape for any non-ASCII character (assuming it
> has a unicode code point - are there any code points in the non-unicode
> encodings that don't have unicode equivalents?). The alternative would be to
> fail on non-ASCII characters, which might be ugly. Of course, anyone wanting
> to deal with JSON should be using UTF8 anyway, but we still have to deal
> with these things. What about SQL_ASCII? If there's a non-ASCII sequence
> there we really have no way of telling what it should be. There at least I
> think we should probably error out.
>
> cheers
>
> andrew
>
>
>