Re: Extract numeric filed in JSONB more effectively

Hi

út 15. 8. 2023 v 5:24 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> 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
>