Andrew Dunstan <andrew@dunslane.net> writes:
> On 06/23/2014 07:34 PM, Tom Lane wrote:
>> I'm not following your comment about 9.3. The json[b]_to_record[set]
>> functions are new in 9.4, which is what makes me feel it's not too
>> late to redefine their behavior. But changing behavior of stuff that
>> was in 9.3 seems a lot more debatable.
> This problem is also manifest in json_populate_recordset, which also
> uses the function in question, and is in 9.3:
Ah, I see the problem.
Here is a first cut suggestion:
* Get rid of the use_json_as_text flag argument for the new functions.
In json_populate_record(set), ignore its value and deprecate using it.
(The fact that it already had a default makes that easier.) The
behavior should always be as below.
* For nested json objects, we'll spit those out in json textual format,
which means they'll successfully convert to either text or json/jsonb.
Compared to the old behavior of json_populate_recordset, this just means
that we don't throw an error anymore regardless of the flag value,
which seems ok (though maybe not something to backpatch into 9.3).
* Nested json arrays are a bit more problematic. What I'd ideally like
is to spit them out in a form that would be successfully parsable as a SQL
array of the appropriate element type. Unfortunately, I think that that
ship has sailed because json_populate_recordset failed to do that in 9.3.
What we should probably do is define this the same as the nested object
case, ie, we spit it out in *json* array format, meaning you can insert it
into a text or json/jsonb field of the result record. Maybe sometime in
the future we can add a json-array-to-SQL-array converter function, but
these functions won't do that.
From a user's standpoint this just boils down to (a) fix the bug with
mishandling of the hash tables, and (b) get rid of the gratuitous
error report.
regards, tom lane