Re: Extract numeric filed in JSONB more effectively
От | Pavel Stehule |
---|---|
Тема | Re: Extract numeric filed in JSONB more effectively |
Дата | |
Msg-id | CAFj8pRC+4pvSuibB2xcNKJ=6PSF=TAcOtRNpdLPBXZjijFg7ag@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Extract numeric filed in JSONB more effectively (Andy Fan <zhihui.fan1213@gmail.com>) |
Ответы |
Re: Extract numeric filed in JSONB more effectively
(Pavel Stehule <pavel.stehule@gmail.com>)
Re: Extract numeric filed in JSONB more effectively (Andy Fan <zhihui.fan1213@gmail.com>) Re: Extract numeric filed in JSONB more effectively (Andy Fan <zhihui.fan1213@gmail.com>) |
Список | pgsql-hackers |
Hi
út 15. 8. 2023 v 5:24 odesílatel Andy Fan <zhihui.fan1213@gmail.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 Ihave 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 RegardsAndy Fan
В списке pgsql-hackers по дате отправления: