Re: JSON for PG 9.2

On 01/31/2012 01:32 PM, Merlin Moncure wrote:
> On Tue, Jan 31, 2012 at 12:15 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>> Andrew,
>>
>>> based on Abhijit's feeling and some discussion offline, the consensus
>>> seems to be to remove query_to_json.
>> If we do that, what would getting complete query results back from a
>> query look like? It's important to make this as simple for developers
>> as possible.
> two options:
> 1. row_to_json(rowvar)
> SELECT row_to_json(foo) from foo;
> SELECT row_to_json(row(a,b,c)) from foo;
>
> 2. array_to_json(array_agg()/array())
> SELECT array_to_json(array(select foo from foo));
> SELECT array_to_json(array[1,2,3]);
>
> #1 I expect will be the more used version -- most json handling client
> side api (for example node.js drivers) are optimized for row by row
> processing, but via #2 you can stuff a whole query into single json
> object if you're so inclined.
>

You could also write a wrapper something like this:

create function query_to_json(qtext text) returns json language
plpgsql as
$$
begin
return query execute 'select array_to_json(array(' || qtext ||
'))';
end;
$$;

cheers

andrew