On 01/30/2014 07:21 PM, Merlin Moncure wrote:
Something seems off:
postgres=# create type z as (a int, b int[]);
CREATE TYPE
postgres=# create type y as (a int, b z[]);
CREATE TYPE
postgres=# create type x as (a int, b y[]);
CREATE TYPE
-- test a complicated construction
postgres=# select row(1, array[row(1, array[row(1, array[1,2])::z])::y])::x;
row
-------------------------------------------------------------------------------------
(1,"{""(1,\\""{\\""\\""(1,\\\\\\\\\\""\\""{1,2}\\\\\\\\\\""\\"")\\""\\""}\\"")""}")
postgres=# select hstore(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
hstore
----------------------------------------------------------------------------------------------
"a"=>1,
"b"=>"{\"(1,\\\"{\\\"\\\"(1,\\\\\\\\\\\"\\\"{1,2}\\\\\\\\\\\"\\\")\\\"\\\"}\\\")\"}"
here, the output escaping has leaked into the internal array
structures. istm we should have a json expressing the internal
structure.
What has this to do with json at all? It's clearly a failure in the
hstore() function.
It does (weirdly) map back however:
postgres=# select populate_record(null::x, hstore(row(1, array[row(1,
array[row(1, array[1,2])::z])::y])::x));
populate_record
-------------------------------------------------------------------------------------
(1,"{""(1,\\""{\\""\\""(1,\\\\\\\\\\""\\""{1,2}\\\\\\\\\\""\\"")\\""\\""}\\"")""}")
OTOH, if I go via json route:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
row_to_json
-----------------------------------------------
{"a":1,"b":[{"a":1,"b":[{"a":1,"b":[1,2]}]}]}
so far, so good. let's push to hstore:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x)::jsonb::hstore;
row_to_json
-------------------------------------------------------
"a"=>1, "b"=>[{"a"=>1, "b"=>[{"a"=>1, "b"=>[1, 2]}]}]
this ISTM is the 'right' behavior. but what if we bring it back to
record object?
postgres=# select populate_record(null::x, row_to_json(row(1,
array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb::hstore);
ERROR: malformed array literal: "{{"a"=>1, "b"=>{{"a"=>1, "b"=>{1, 2}}}}}"
yikes. The situation as I read it is that (notwithstanding my comments
upthread) there is no clean way to slide rowtypes to/from hstore and
jsonb while preserving structure. IMO, the above query should work
and the populate function record above should return the internally
structured row object, not the text escaped version.
And this is a failure in populate_record().
I think we possibly need to say that handling of nested composites and
arrays is an area that needs further work. OTOH, the refusal of
json_populate_record() and json_populate_recordset() to handle these in
9.3 has not generated a flood of complaints, so I don't think it's a
tragedy, just a limitation, which should be documented if it's not
already. (And of course hstore hasn't handled nested anything before now.)
Meanwhile, maybe Teodor can fix the two hstore bugs shown here.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
