Add assorted new regexp_xxx SQL functions.

This patch adds new functions regexp_count(), regexp_instr(),
regexp_like(), and regexp_substr(), and extends regexp_replace()
with some new optional arguments.  All these functions follow
the definitions used in Oracle, although there are small differences
in the regexp language due to using our own regexp engine -- most
notably, that the default newline-matching behavior is different.
Similar functions appear in DB2 and elsewhere, too.  Aside from
easing portability, these functions are easier to use for certain
tasks than our existing regexp_match[es] functions.

Gilles Darold, heavily revised by me

Discussion: https://postgr.es/m/fc160ee0-c843-b024-29bb-97b5da61971f@darold.net

3 files changed, 67 insertions, 2 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 549c8991442..876583adaa6 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202107261
+#define CATALOG_VERSION_NO	202108031
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8cd02520824..b603700ed9d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3565,6 +3565,18 @@
 { oid => '2285', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
   proargtypes => 'text text text text', prosrc => 'textregexreplace' },
+{ oid => '9611', descr => 'replace text using regexp',
+  proname => 'regexp_replace', prorettype => 'text',
+  proargtypes => 'text text text int4 int4 text',
+  prosrc => 'textregexreplace_extended' },
+{ oid => '9612', descr => 'replace text using regexp',
+  proname => 'regexp_replace', prorettype => 'text',
+  proargtypes => 'text text text int4 int4',
+  prosrc => 'textregexreplace_extended_no_flags' },
+{ oid => '9613', descr => 'replace text using regexp',
+  proname => 'regexp_replace', prorettype => 'text',
+  proargtypes => 'text text text int4',
+  prosrc => 'textregexreplace_extended_no_n' },
 { oid => '3396', descr => 'find first match for regexp',
   proname => 'regexp_match', prorettype => '_text', proargtypes => 'text text',
   prosrc => 'regexp_match_no_flags' },
@@ -3579,6 +3591,58 @@
   proname => 'regexp_matches', prorows => '10', proretset => 't',
   prorettype => '_text', proargtypes => 'text text text',
   prosrc => 'regexp_matches' },
+{ oid => '9614', descr => 'count regexp matches',
+  proname => 'regexp_count', prorettype => 'int4', proargtypes => 'text text',
+  prosrc => 'regexp_count_no_start' },
+{ oid => '9615', descr => 'count regexp matches',
+  proname => 'regexp_count', prorettype => 'int4',
+  proargtypes => 'text text int4', prosrc => 'regexp_count_no_flags' },
+{ oid => '9616', descr => 'count regexp matches',
+  proname => 'regexp_count', prorettype => 'int4',
+  proargtypes => 'text text int4 text', prosrc => 'regexp_count' },
+{ oid => '9617', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4', proargtypes => 'text text',
+  prosrc => 'regexp_instr_no_start' },
+{ oid => '9618', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4',
+  proargtypes => 'text text int4', prosrc => 'regexp_instr_no_n' },
+{ oid => '9619', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4',
+  proargtypes => 'text text int4 int4', prosrc => 'regexp_instr_no_endoption' },
+{ oid => '9620', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4',
+  proargtypes => 'text text int4 int4 int4',
+  prosrc => 'regexp_instr_no_flags' },
+{ oid => '9621', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4',
+  proargtypes => 'text text int4 int4 int4 text',
+  prosrc => 'regexp_instr_no_subexpr' },
+{ oid => '9622', descr => 'position of regexp match',
+  proname => 'regexp_instr', prorettype => 'int4',
+  proargtypes => 'text text int4 int4 int4 text int4',
+  prosrc => 'regexp_instr' },
+{ oid => '9623', descr => 'test for regexp match',
+  proname => 'regexp_like', prorettype => 'bool', proargtypes => 'text text',
+  prosrc => 'regexp_like_no_flags' },
+{ oid => '9624', descr => 'test for regexp match',
+  proname => 'regexp_like', prorettype => 'bool',
+  proargtypes => 'text text text', prosrc => 'regexp_like' },
+{ oid => '9625', descr => 'extract substring that matches regexp',
+  proname => 'regexp_substr', prorettype => 'text', proargtypes => 'text text',
+  prosrc => 'regexp_substr_no_start' },
+{ oid => '9626', descr => 'extract substring that matches regexp',
+  proname => 'regexp_substr', prorettype => 'text',
+  proargtypes => 'text text int4', prosrc => 'regexp_substr_no_n' },
+{ oid => '9627', descr => 'extract substring that matches regexp',
+  proname => 'regexp_substr', prorettype => 'text',
+  proargtypes => 'text text int4 int4', prosrc => 'regexp_substr_no_flags' },
+{ oid => '9628', descr => 'extract substring that matches regexp',
+  proname => 'regexp_substr', prorettype => 'text',
+  proargtypes => 'text text int4 int4 text',
+  prosrc => 'regexp_substr_no_subexpr' },
+{ oid => '9629', descr => 'extract substring that matches regexp',
+  proname => 'regexp_substr', prorettype => 'text',
+  proargtypes => 'text text int4 int4 text int4', prosrc => 'regexp_substr' },
 { oid => '2088', descr => 'split string by field_sep and return field_num',
   proname => 'split_part', prorettype => 'text',
   proargtypes => 'text text int4', prosrc => 'split_part' },
diff --git a/src/include/utils/varlena.h b/src/include/utils/varlena.h
index 5c39723332d..6645e2af137 100644
--- a/src/include/utils/varlena.h
+++ b/src/include/utils/varlena.h
@@ -34,6 +34,7 @@ extern bool SplitDirectoriesString(char *rawstring, char separator,
 extern bool SplitGUCList(char *rawstring, char separator,
 						 List **namelist);
 extern text *replace_text_regexp(text *src_text, void *regexp,
-								 text *replace_text, bool glob);
+								 text *replace_text,
+								 int search_start, int n);
 
 #endif