Andrew Dunstan <andrew.duns...@2ndquadrant.com> wrote:
Bryn Llewellyn wrote:
>
> Andrew replied
>
> The function above has many deficiencies, including lack of error
> checking and use of 'execute' which will significantly affect
> performance. Still, if it works for you, that's your affair.
>
> These functions were written to accommodate PostgreSQL limitations. We
> don't have a heterogenous array type. So json_object() will return an
> object where all the values are strings, even if they look like numbers,
> booleans etc. And indeed, this is shown in the documented examples.
> jsonb_build_object and jsonb_build_array overcome that issue, but there
> the PostgreSQL limitation is that you can't pass in an actual array as
> the variadic element, again because we don't have heterogenous arrays.
>
> Bryn replies:
>
>
> Of course I didn’t show error handling. Doing so would have increased the
> source text size and made it harder to appreciate the point.
>
> I used dynamic SQL because I was modeling the use case where on-the-fly
> analysis determines what JSON object or array must be built—i.e. the number
> of components and the datatype of each. It’s nice that jsonb_build_object()
> and jsonb_build_array() accommodate this dynamic need by being variadic. But
> I can’t see a way to wrote the invocation using only static code.
>
> What am I missing?
Probably not much, These functions work best from application code which
builds up the query. But if you do that and then call a function which
in turn calls execute you get a double whammy of interpreter overhead.
I'm also not a fan of functions that in effect take bits of SQL text and
interpolate them into a query in plpgsql, like your query does.
json_object() is meant to be an analog of the hstore() function that
takes one or two text arrays and return an hstore. Of course, it doesn't
have the issue you complained about, since all values in an hstore are
strings.
Bryn replied:
We don’t yet support the hstore() function in YugabyteDB. So, meanwhile, I see
no alternative to the approach that I illustrated—whatever that implies for
doing things of which you’re not a fan. That’s why I asked “ What am I
missing?”. But your “ Probably not much” seems, then, to force my hand.
B.t.w., you earlier said “The double quotes [around “dog”] serve a specific
purpose, to allow values containing commas to be treated as a single value (see
syntax details for the exact rules) in the resulting array of text values.” But
this test shows that they are not needed for that purpose:
select jsonb_pretty(jsonb_object(
'{a, 17, b, dog house, c, true}'::varchar[]
))
This is the result:
{ +
"a": "17", +
"b": "dog house",+
"c": "true" +
}
The commas are sufficient separators.
It seems to me, therefore, that writing the double quotes gives the wrong
message: they make it look like you are indeed specifying a text value rather
than a numeric or integer value. But we know that the double quotes do *not*
achieve this.
