Re: JSON in 9.2: limitations

On Tue, Aug 7, 2012 at 7:26 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> On 08/08/2012 03:45 AM, Merlin Moncure wrote:
>
>> Given that you can do that, if you had the ability to emit json from
>> an hstore the OP's problem would be trivially handled.
>
>
> That's where my thinking went at first too, but there's a wrinkle with that:
> json represents the number 1 and the string "1" differently. hstore doesn't.
> The input data would need to be JSON-escaped before being added to hstore to
> preserve that difference - so at minimum some kind of scalar
> json_escape(...) function is still needed.
>
> I was also trying to avoid the need to use *two* extensions for the job.

yeah -- i see your point. that said, in the OP's example, he's
crosstabbing to a tuple which is going to make it a uniform type
anyways. so I'd argue that the problem is that you can't combine
tuples for output to json without discarding a) types or b) field
names:

postgres=# create table foo(a int, b text);
CREATE TABLE
postgres=# create table bar(c int, d text);
CREATE TABLE

via hstore (lose type)
postgres=# select hstore((1,'abc')::foo) || hstore((1,'abc')::bar);
?column?
--------------------------------------------
"a"=>"1", "b"=>"abc", "c"=>"1", "d"=>"abc"

(with hypothetical hstore_to_json, note quoted numerics) :
{"f1":"1","f2":"abc","f3":"2","f4":"def"}

via row() (lose attribute names):
postgres=# select row_to_json(row(((1,'abc')::foo).*, ((2,'def')::bar).*));
row_to_json
---------------------------------------
{"f1":1,"f2":"abc","f3":2,"f4":"def"}

I also get your argument about extension soup, but at the end of the
day I think the right way to go is to get transformation mechanics in
SQL worked out -- that benefits non-json use cases as well. I can't
think of anything better than what you've come up with though.

merlin