How to format in PostgreSQL CITEXT variable to a regex to be used for search

First of all – all credits goes to Laurenz Albe answer here

I hava a JSONB colum where I need to perform a search on the values (exactly as explained in the linked question). I think to get advantage from the answer there and the trigram index. However in the answer the regex which is searched is hardcoded directly WHERE visitors::text *~ 'mJohn DoeM';

My value comes from the front end where the user enters a search string which is named in the postgres function as search_term.

If I was to use the answer directly I would have to execute WHERE visitors::text search_term which obviously won’t work.

My question is how to continue the query so that I can modify the search_term variable and get the same effet as the *~ 'mJohn DoeM' in the original post?

Answer

Just pass the variable as the right operand to regexp operator *~;

WHERE visitors::text *~ 'm' || search_term || 'M'