Re: Variable substitution in jsonb functions fails for jsonpath operator like_regex

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Wed, Oct 18, 2023 at 5:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Maybe it wouldn't take more than s/STRING_P/starts_with_initial/
>> here (though I'd then rename starts_with_initial to something
>> more generic).

> I tried it, and it didn't work. No error, it just doesn't match
> anything--including literal values which do match things in HEAD.

Oh, duh, makeItemLikeRegex() is expecting a plain JsonPathString,
and JsonPathParseItem.value.like_regex is set up for just a
constant pattern. We could no doubt fix that, but it's a bigger
lift than I was hoping.

> Maybe the problem is that the regex pattern is compiled at the same time
> the jsonpath is compiled?

Doesn't look that way to me: executeLikeRegex is just using
RE_compile_and_execute every time. (It's "caching" a text datum
representing the pattern string, which might be a good candidate for
the silliest use of caching I've ever seen in PG; it's surely not
buying any useful increment of performance.)

regards, tom lane