Re: Extract numeric filed in JSONB more effectively - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Extract numeric filed in JSONB more effectively
Date
Msg-id CAFj8pRDvuAAAjt95-kyFHFH3LPiBWBSSSomBExtu8bN6Ywi0gA@mail.gmail.com
Whole thread Raw
In response to Re: Extract numeric filed in JSONB more effectively  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-hackers


út 15. 8. 2023 v 7:23 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
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 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

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Extract numeric filed in JSONB more effectively
Next
From: Vik Fearing
Date:
Subject: Re: proposal: jsonb_populate_array