Re: replace inside regexp_replace

On Mon, Jun 21, 2021 at 02:27:22PM +0100, Oliver Kohll wrote:
> It half works, i.e. it removes the brackets but doesn't seem to process the
> inner replace. It's as if the select were just
> select regexp_replace(
> 'here is [[my text]] to replace and [[some more]]',
> E'\\[\\[(.*?)\\]\\]',
> E'\\1',
> 'g'
> );
> I've a feeling I'm missing something fundamental, any idea what?

\1 works only if it's argument to regexp_replace.
And regexp_replace can't call any other functions.

What you could do is:
$ select string_agg(x[1] || replace(x[2], ' ', '_') || x[3], '') from regexp_matches( 'here is [[my text]] to replace and [[some more]] and maybe [[a bit longer]] too', '(.*?)\[\[(.*?)\]\](.*?)', 'g') x;
string_agg
─────────────────────────────────────────────────────────────────
here is my_text to replace and some_more and maybe a_bit_longer
(1 row)

Or just use plperl, pl/python, or anything like this.

Best regards,

depesz