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.
I don't think this is a key conflict so far. but I'd explain this in more
detail. If timestamp -> timestamptz or timestamptz -> timestamp is
binary compatible, we can only have 1 operator to return a timestamp.
then when we cast it to timestamptz, it will be a no-op during runtime.
however cast between timestamp and timestamptz is not binary
compatible. whose castmethod is 'f';
>> 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 efficient?
I didn't realize this before, 'planner support function' looks
amazing and SupportRequestSimplify looks promising, I will check it