Variable substitution in jsonb functions fails for jsonpath operator like_regex

Erwin Brandstetter <brsaweda@gmail.com>

From: Erwin Brandstetter <brsaweda@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2023-10-18T19:07:34Z
Lists: pgsql-bugs
The functions jsonb_path_exists() and friends accept a "vars" parameter for
parameter substitution in the jsonpath argument. This seems to work for all
jsonpath operators except "like_regex":

SELECT * FROM tbl
WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $foo)', '{"foo":
"CEO"}');

> ERROR:  syntax error at or near "$foo" of jsonpath input
> LINE 3: WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $...

Notably, the same works even for "starts with":

SELECT * FROM tbl
WHERE  jsonb_path_exists(data, '$[*].value ? (@ starts with $foo)',
'{"foo": "CEO"}');

I also tested related jsonb functions including jsonb_path_query(),
jsonb_path_query_first(), jsonb_path_query(). Always the same error message.

Here is the question on stackoverflow.com that brought the issue to my
attention (plus my answer with more details):
https://stackoverflow.com/questions/77317468/variable-substitution-for-postgres-jsonpath-operator-like-regex/77318568

Here is a related fiddle to play with:
https://dbfiddle.uk/4yRjIYlh

Tested with Postgres 16.0.

Regards
Erwin