Bryn Llewellyn wrote:
> ...I wrote my own wrapper for jsonb_build_array()
> and jsonb_build_object():
>
> create function my_jsonb_build(
> kind in varchar,
> variadic_elements in varchar)
> returns jsonb
> immutable
> language plpgsql
> as $body$
> declare
> stmt varchar :=
> case kind
> when 'array' then
> 'select jsonb_build_array('||variadic_elements||')'
> when 'object' then
> 'select jsonb_build_object('||variadic_elements||')'
> end;
> j jsonb;
> begin
> execute stmt into j;
> return j;
> end;
> $body$;
>
Andrew replied
Please don't top-post on PostgreSQL lists. See
<http://idallen.com/topposting.html>
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:
Ah… I didn’t know about the bottom-posting rule.
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?
