MIME-Version: 1.0
Date: Thu, 03 Aug 2023 08:34:44 -0400
From: Chapman Flack <chap@anastigmatix.net>
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, jian he
 <jian.universality@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
 <CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
 <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
 <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
 <CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
User-Agent: Roundcube Webmail/1.6.0
Message-ID: <3198e7c138fc0b5f80512785a87b7bb2@anastigmatix.net>
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/3198e7c138fc0b5f80512785a87b7bb2%40anastigmatix.net>
Precedence: bulk

On 2023-08-03 03:53, Andy Fan wrote:
> I didn't realize timetime types are binary compatible with SQL,
> so maybe we can have some similar optimization as well.
> (It is a pity that timestamp(tz) are not binary, or else we may
> just need one operator).

Not to veer from the thread, but something about that paragraph
has been hard for me to parse/follow.

>> Maybe we can introduce some *internal operator* "extract to type", and 
>> in
>> rewrite stage we can the pattern (x->'field')::type transform to OP(x,
>> 'field', typid)
> 
> Not sure what the OP should be?  If it is a function, what is the
> return value?  It looks to me like it is hard to do in c language?

Now I am wondering about the 'planner support function' available
in CREATE FUNCTION since PG 12. I've never played with that yet.
Would that make it possible to have some, rather generic, extract
from JSON operator that can look at the surrounding expression
and replace itself sometimes with something more efficient?

Regards,
-Chap