On Tue, Feb 23, 2016 at 12:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dan S <strd911@gmail.com> writes: > I have this table, data and query:
> create table test > ( > id int, > txt text, > txt_arr text[], > f float > );
> insert into test > values > (1,'jkl','{abc,def,fgh}',3.14159),(2,'hij','{abc,def,fgh}',3.14159),(2,null,null,null),(3,'def',null,0);
> select j, json_populate_record(null::test, j) > from > ( > select to_json(t) as j from test t > ) r; > ERROR: malformed array literal: "["abc","def","fgh"]" > DETAIL: "[" must introduce explicitly-specified array dimensions.
> Is it a bug or how am I supposed to use the populate function ?
AFAICS, json_populate_record has no intelligence about nested container situations. It'll basically just push the JSON text representation of any field of the top-level object at the input converter for the corresponding composite-type column. That doesn't work if you're trying to convert a JSON array to a Postgres array, and it wouldn't work for sub-object to composite column either, because of syntax discrepancies.
Ideally this would work for arbitrarily-deeply-nested array+record structures, but it looks like a less than trivial amount of work to make that happen. > If I try an equivalent example with hstore it works well.
hstore hasn't got any concept of substructure in its field values, so it's hard to see how you'd create an "equivalent" situation.
Equivalent in the "ability to round-trip" sense. Since hstore doesn't have nested containers internal serialization of a record to hstore is forced to "stringify" the array which can then be fed back in as-is. But the [row_]to_json
logic converts the PostgreSQL arrays to JSON arrays and then we fail to handle them on the return portion of the trip.
Arrays are likely to be a much for common scenario but I agree that dealing with arbitrary depths and objects would make the feature complete.
And yes, back-patching should only occur (and ideally behavior changing) for situations that today raise errors - as the example does.
