Re: Extract numeric filed in JSONB more effectively

>
>
>>> Perhaps one of the more senior developers will chime in, but to me,
>>> leaving out the relabel nodes looks more like "all of PostgreSQL's
>>> type checking happened before the SupportRequestSimplify, so nothing
>>> has noticed that we rewrote the tree with mismatched types, and as
>>> long as nothing crashes we sort of got away with it."
>>>
>>> Suppose somebody writes an extension to double-check that plan
>>> trees are correctly typed. Or improves EXPLAIN to check a little more
>>> carefully than it seems to. Omitting the relabel nodes could spell
>>> trouble then.
>>>
>>> Or, someone more familiar with the code than I am might say "oh,
>>> mismatches like that are common in rewritten trees, we live with it."
>>> But unless somebody tells me that, I'm not believing it.
>>>
>>
>> Well, this sounds long-lived. I kind of prefer to label it now. Adding
>> the 3rd commit to relabel the arg and return value.
>>
>>
After we label it, we will get error like this:

select (a->'a')::int4 from m;
ERROR: cannot display a value of type internal

However the following statement can work well.

select ('{"a": 12345}'::jsonb->'a')::numeric;
numeric
---------
12345

That's mainly because the later query doesn't go through the planner
support function. I didn't realize this before so the test case doesn't
catch it. Will add the test case in the next version. The reason why
we get the error for the first query is because the query tree says
we should output an "internal" result at last and then pg doesn't
know how to output an internal data type. This is kind of in conflict
with our goal.

So currently the only choices are: PATCH 001 or PATCH 001 + 002.

https://www.postgresql.org/message-id/CAKU4AWrs4Pzajm2_tgtUTf%3DCWfDJEx%3D3h45Lhqg7tNOVZw5YxA%40mail.gmail.com

--
Best Regards
Andy Fan