Re: PATCH: jsonpath string methods: lower, upper, initcap, l/r/btrim, replace, split_part

On Thu, Sep 26, 2024 at 12:04 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Florents Tselai <florents(dot)tselai(at)gmail(dot)com> writes:
> > This patch is a follow-up and generalization to [0].
> > It adds the following jsonpath methods: lower, upper, initcap, l/r/btrim,
> > replace, split_part.
>
> How are you going to deal with the fact that this makes jsonpath
> operations not guaranteed immutable? (See commit cb599b9dd
> for some context.) Those are all going to have behavior that's
> dependent on the underlying locale.
>
> We have the kluge of having separate "_tz" functions to support
> non-immutable datetime operations, but that way doesn't seem like
> it's going to scale well to multiple sources of mutability.

While inventing "_tz" functions I was thinking about jsonpath methods
and operators defined in standard then. Now I see huge interest on
extending that. I wonder if we can introduce a notion of flexible
mutability? Imagine that jsonb_path_query() function (and others) has
another function which analyzes arguments and reports mutability. If
jsonpath argument is constant and all methods inside are safe then
jsonb_path_query() is immutable otherwise it is stable. I was
thinking about that back working on jsonpath, but that time problem
seemed too limited for this kind of solution. Now, it's possibly time
to shake off the dust from this idea. What do you think?

------
Regards,
Alexander Korotkov
Supabase