MIME-Version: 1.0
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
 <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
 <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
 <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
 <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
 <CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>
 <CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com>
 <CAFj8pRD4cdUmK0RG4oN5B2KRSeDhwfMYaL=XpfEu4iaLeZ_Kow@mail.gmail.com>
 <CAKU4AWp8ab61e96v57OaB-Gm1bMfBNVLVy+s17U6_Ne3veB84g@mail.gmail.com>
 <3507485.1691090027@sss.pgh.pa.us> <CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
In-Reply-To: <CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 14 Aug 2023 10:01:31 +0200
Message-ID: <CAFj8pRDbZCBgZhnOPK5JbMRDpUrpxceaGVaorz30PycAjteeig@mail.gmail.com>
Subject: Re: Extract numeric filed in JSONB more effectively
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, jian he <jian.universality@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a4b5e10602dd7abb"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRDbZCBgZhnOPK5JbMRDpUrpxceaGVaorz30PycAjteeig%40mail.gmail.com>
Precedence: bulk

--000000000000a4b5e10602dd7abb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

po 14. 8. 2023 v 9:06 odes=C3=ADlatel Andy Fan <zhihui.fan1213@gmail.com> n=
apsal:

>
>> We'd still have functions like jsonb_field_as_numeric() under the
>> hood, but there's not an expectation that users call them explicitly.
>>
>
> To avoid the lots of functions like jsonb_field_as_int2/int4, I defined
> Datum jsonb_object_field_type(.., Oid target_oid) at last,  so the
> function must return "internal" or "anyelement".  Then we can see:
>
> select jsonb_object_field_type(tb.a, 'a'::text, 1700) from tb;
> ERROR:  cannot display a value of type anyelement.
>

you cannot to use type as parameter. There should be some typed value - lik=
e

jsonb_object_field, '{"a":10}', 'a', NULL::int)

and return type should be anyelement.

Another solution should be more deeper change like implementation of
"coalesce"


>
> The reason is clear to me, but  I'm not sure how to fix that or deserves
> a fix? Or shall I define jsonb_object_field_int2/int8 to avoid this?
>
> This is an unresolved issue at the latest patch.
> --
> Best Regards
> Andy Fan
>

--000000000000a4b5e10602dd7abb
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">po 14. 8. 2023 v=C2=A09:06 odes=C3=AD=
latel Andy Fan &lt;<a href=3D"mailto:zhihui.fan1213@gmail.com">zhihui.fan12=
13@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><br>
We&#39;d still have functions like jsonb_field_as_numeric() under the<br>
hood, but there&#39;s not an expectation that users call them explicitly.<b=
r></blockquote><div><br></div><div>To avoid the lots of functions like json=
b_field_as_int2/int4, I defined</div><div>Datum jsonb_object_field_type(..,=
 Oid target_oid) at last,=C2=A0 so the</div><div>function must return &quot=
;internal&quot; or &quot;anyelement&quot;.=C2=A0 Then we can see:</div><div=
><br></div>select jsonb_object_field_type(tb.a, &#39;a&#39;::text, 1700) fr=
om tb;<br>ERROR: =C2=A0cannot display a value of type anyelement.</div></di=
v></blockquote><div><br></div><div>you cannot to use type as parameter. The=
re should be some typed value - like</div><div><br></div><div>jsonb_object_=
field, &#39;{&quot;a&quot;:10}&#39;, &#39;a&#39;, NULL::int)</div><div><br>=
</div><div>and return type should be anyelement.</div><div><br></div><div>A=
nother solution should be more deeper change like implementation of &quot;c=
oalesce&quot;</div><div><br></div><div>=C2=A0<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><=
br></div><div class=3D"gmail_quote">The reason is clear to me, but=C2=A0 I&=
#39;m not sure how to fix=C2=A0that or deserves</div><div class=3D"gmail_qu=
ote">a fix? Or shall I define jsonb_object_field_int2/int8 to avoid this?=
=C2=A0</div><div class=3D"gmail_quote"><div><br></div><div>This is an unres=
olved issue at the latest patch.=C2=A0</div></div><span class=3D"gmail_sign=
ature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div=
 dir=3D"ltr">Best Regards<div>Andy Fan</div></div></div></div>
</blockquote></div></div>

--000000000000a4b5e10602dd7abb--