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

On Thu, 19 Oct 2023 at 21:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Wed, Oct 18, 2023, 18:47 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> 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.
>
> > Not in a position to do it myself but we should confirm we aren't simply
> > following the standard here, and if so decide whether we want to deviate.
>
> Hmm ... looks like we *are* following the standard:
>
>
> <JSON like_regex predicate> ::=
> <JSON path wff> like_regex <JSON like_regex pattern>
> [ flag <JSON like_regex flags> ]
> <JSON like_regex pattern> ::=
> <JSON path string literal>
> <JSON like_regex flag> ::=
> <JSON path string literal>
>
> whereas "starts with" has
>
> <JSON starts with predicate> ::=
> <JSON starts with whole> starts with <JSON starts with initial>
> <JSON starts with whole> ::=
> <JSON path wff>
> <JSON starts with initial> ::=
> <JSON path wff>
>
> The text mentions that "the second operand is permitted to be an SQL/JSON
> sequence and to support existential semantics", whereas they evidently
> don't want that for a regex pattern.
>

So input from "vars" cannot be substituted into the jsonpath expression
after "like_regex" (as opposed to all other jsonpath operators). Seems
pretty random from a user's perspective.

Be that as it may, if that's the consensus, I'll have a closer look at the
manual page to try and convey the rules.
Do we have an accord?

Regards
Erwin