Search Postgresql Archives

Re: JSON in 9.2: limitations

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Aug 7, 2012 at 7:26 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux