public inbox for [email protected]  

help / color / mirror / Atom feed

From: Pavel Stehule <[email protected]>
To: Andy Fan <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Tue, 15 Aug 2023 07:23:26 +0200
Message-ID: <CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWrGM5bK7wi4Y8bTYhKgh=A1fW=X00eC_jfk6_JXyaEURQ@mail.gmail.com>
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>
	<[email protected]>
	<CAKU4AWp+KLes8g=BWLqZfDmW9+=ZY0UC4G0i3qVcYEviK_dDTA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CAKU4AWrBY9GHj9oZbvhiOG1BgiWyZC8FGPAET-CfRKDhYyv1HQ@mail.gmail.com>
	<CAKU4AWrGM5bK7wi4Y8bTYhKgh=A1fW=X00eC_jfk6_JXyaEURQ@mail.gmail.com>

Hi

út 15. 8. 2023 v 5:24 odesílatel Andy Fan <[email protected]> napsal:

>
>>  jsonb_extract_xx_type just cares about the argtype, but
>> 'explain select xx'  will still access the const->constvalue.
>> const->constvalue is 0 which is set by makeNullConst currently,
>> and it is ok for the current supported type.
>>
>
> The exception is numeric data type, the constvalue can't be 0.
> so hack it with the below line.  maybe not good enough,  but I
> have no better solution now.
>
> +                       Const   *target =
>  makeNullConst(fexpr->funcresulttype,
> +
>                -1,
> +
>                InvalidOid);
> +                       /*
> +                        * Since all the above functions are strict, we
> can't input
> +                        * a NULL value.
> +                        */
> +                       target->constisnull = false;
> +
> +                       Assert(target->constbyval || target->consttype ==
> NUMERICOID);
> +
> +                       /* Mock a valid datum for !constbyval type. */
> +                       if (fexpr->funcresulttype == NUMERICOID)
> +                               target->constvalue =
> DirectFunctionCall1(numeric_in, CStringGetDatum("0"));
>
>
Personally I think this workaround is too dirty, and better to use a strict
function (I believe so the overhead for NULL values is acceptable), or
introduce a different mechanism.

Your design is workable, and I think acceptable, but I don't think it is an
ideal or final solution. It is not really generic. It doesn't help with XML
or Hstore. You need to touch cast functions, which I think is not best,
because cast functions should not cooperate on optimization of execution of
another function.

My idea of an ideal solution is the introduction of the possibility to use
"any" pseudotype as return type with possibility to set default return
type. Now, "any" is allowed only for arguments. The planner can set the
expected type when it knows it, or can use the default type.

so for extraction of jsonb field we can use FUNCTION
jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb

if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb,
if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns date

With this possibility we don't need to touch to cast functions, and we can
simply implement similar functions for other non atomic types.



-- 
> Best Regards
> Andy Fan
>

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Extract numeric filed in JSONB more effectively
  In-Reply-To: <CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox