public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Chapman Flack <[email protected]>
Cc: Pavel Stehule <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Thu, 3 Aug 2023 21:50:15 +0800
Message-ID: <CAKU4AWrMR8AS2XoedYB=X0dwmrHv23z2STtByazDVVJLkDt9UA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
Hi:
On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack <[email protected]> wrote:
> 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
more.
--
Best Regards
Andy Fan
view thread (8+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAKU4AWrMR8AS2XoedYB=X0dwmrHv23z2STtByazDVVJLkDt9UA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox