Re: Extract numeric filed in JSONB more effectively

út 15. 8. 2023 v 7:23 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> 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.
>

this syntax can be used instead NULL::type trick

like

SELECT jsonb_populate_record('{...}')::pg_class;

instead

SELECT jsonb_populate_record(NULL::pg_class, '{...}')

>
>
>
> --
>> Best Regards
>> Andy Fan
>>
>