Re: JSON for PG 9.2

2012/1/11 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 01/11/2012 01:18 AM, Pavel Stehule wrote:
>>
>>
>> I like this patch and this feature.
>
>
> I'm about to read the patch in detail - I certainly like the feature.
>
>
>>
>> I see only one issue - there is not functionality that helps generate
>> JSON in pg.
>>
>> What do you think about functions: array_to_json(anyarray),
>> row_to_json(any) and format_json(text, text, ...)
>>
>
> Actually, more than these, I (and at least one very interested client) want
> query_to_json, which would do something like:
>
>   # select q2json('select $$a$$ || x as b, y as c from generate_series(1,3)
> x, generate_series(4,5) y');
>                                                     q2json
>
> ---------------------------------------------------------------------------------------------------------
>
> [{"b":"a1","c":4},{"b":"a1","c":5},{"b":"a2","c":4},{"b":"a2","c":5},{"b":"a3","c":4},{"b":"a3","c":5}]
>

we have a query_to_xml - so there should similar query_to_json. But
this is not enough for usage from SP. What about two rich functions

* query_to_json - by your proposal
* array_to_json - with possibility to serialize array of records

This can be a basic set

Regards

Pavel

>
> No doubt several variants are possible such as returning a setof json, one
> per row, instead of a single json, and allowing query parameters as separate
> arguments (maybe just using variadic functions), but probably for a first go
> just something as simple as this would meet the case.

>
> Given the short time span available before patches must be in, I am prepared
> to work on this ASAP.
>
> cheers
>
> andrew
>