How to capture pattern mtach line and next n line in postgresql-CodePudding

select regexp_replace(
E'In most cases regexp_matches() should be used with the g flag,' ||
E'since if you only want\n the first match, it''s easier and more efficient'||
E'to use regexp_match(). \nHowever, regexp_match() only exists in '||
E'PostgreSQL version 10 and up. When working in older\n versions,' ||
E'a common trick is to place a regexp_matches() call in a sub-select,'||
E'for\n example:', E'(.|\n)*?(PostgreSQL version 10)', E'\n');

Return

                                  regexp_replace                                  
----------------------------------------------------------------------------------
                                                                                  
  and up. When working in older                                                   
  versions,a common trick is to place a regexp_matches() call in a sub-select,for 
  example:
(1 row)

But I'm looking for a generic regex pattern that will capture pattern line (whole line) and following n line.(n as a integer, can be 1 or 2 or 3).

I even tried:

select regexp_replace(
E'In most cases regexp_matches() should be used with the g flag,' ||
E'since if you only want\n the first match, it''s easier and more efficient'||
E'to use regexp_match(). \nHowever, regexp_match() only exists in '||
E'PostgreSQL version 10 and up. When working in older\n versions,' ||
E'a common trick is to place a regexp_matches() call in a sub-select,'||
E'for\n example:', E'.*?(PostgreSQL version 10).*?\n', E'\n');

expected result:
Match one line: The whole line contain string "PostgreSQL version 10" will become newline, others remain the same.

Match two line: The whole line contain string "PostgreSQL version 10" will become newline, the following line will also become new line, others remain the same.

CodePudding user response：

I think this regex should work:

(\s\\n).*(PostgreSQL version 10)(.*?(\\n\s)){T}

where T is the number of line you would like replaced with \n. Demo here.

Explanation:

(\s\\n) - matches the string starting with a space, \ and n.
.* - match anything between
(PostgreSQL version 10) - match the words PostgreSQL version 10
(.*?(\\n\s)) - .*? matches any character except line terminator between 0 and unlimited times. \\ matches for \ while n does the same for char n. \s matches whitespace char.
{T} - matches the last group i.e. (.*?(\\n\s)) T times.

Edit:

As highlighted by OP, above regex doesn't work in Postgres 14 and 15, updated and tested regex that works in Postgres is: (\s\n).*?(PostgreSQL version 10)(.*?(\n\s)){T}. DB Fiddle Demo