Subject: Re: add function argument names to regex* functions.

From: "Dian Fay" <[email-masked]>
Date: Mon, 01 Jan 2024 13:05:45 -0500

On Wed Dec 27, 2023 at 10:28 PM EST, jian he wrote:
> On Thu, Dec 28, 2023 at 6:25 AM Peter Eisentraut <[email-masked]> wrote:
> >
> > On 27.12.23 17:53, jian he wrote:
> > > similar to [1], add function argument names to the following functions:
> > > regexp_like, regexp_match,regexp_matches,regexp_replace,
> > > regexp_substr,regexp_split_to_array,regexp_split_to_table,regexp_count
> >
> > Note that these functions are a quasi-standard that is shared with other
> > SQL implementations.  It might be worth looking around if there are
> > already other implementations of this idea.
> >
>
> turns out people do like calling functions via explicitly mentioning
> function argument names, example: [0]
> There are no provisions for the argument names.
>
> I looked around the oracle implementation in [1], and the oracle regex
> related function argumentation name in [2]
> I use the doc [3] syntax explanation and add the related function names.
>
> Current, regex.* function syntax seems fine. but only parameter `N`
> seems a little bit weird.
> If we change the function's argument name, we also need to change
> function syntax explanation in the doc; vise versa.
>
> QUOTE:
> The regexp_instr function returns the starting or ending position of
> the N'th match of a POSIX regular expression pattern to a string, or
> zero if there is no such match. It has the syntax regexp_instr(string,
> pattern [, start [, N [, endoption [, flags [, subexpr ]]]]]). pattern
> is searched for in string, normally from the beginning of the string,
> but if the start parameter is provided then beginning from that
> character index. If N is specified then the N'th match of the pattern
> is located, otherwise the first match is located.
> END OF QUOTE.
>
> maybe we can change `N` to occurrence. but `occurrence` is kind of verbose.
>
> [0] https://stackoverflow.com/questions/33387348/oracle-named-parameters-in-regular-functions
> [1] https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/REGEXP_SUBSTR.html#GUID-2903904D-455F-4839-A8B2-1731EF4BD099
> [2] https://dbfiddle.uk/h_SBDEKi
> [3] https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP

I've been trying to use named arguments more diligently so expanding
support for built-in functions is welcome. The patch applies cleanly and
works as advertised.

I agree that the parameter name `n` is not ideal. For example, in
`regexp_replace` it's easy to misinterpret it as "make up to n
replacements". This has not been a problem when `n` only lives in the
documentation which explains exactly what it does, but that context is
not readily available in code expressing `n => 3`.

Another possibility is `index`, which is relatively short and not a
reserved keyword ^1. `position` is not as precise but would avoid the
conceptual overloading of ordinary indices.

1. https://www.postgresql.org/docs/current/sql-keywords-appendix.html

---

From: Jim Nasby <[email-masked]>
Date: Wed, 3 Jan 2024 16:23:37 -0600

On 1/1/24 12:05 PM, Dian Fay wrote:

Agreed; IMO it's worth diverging from what Oracle has done here.

I'm not a fan of "index" since that leaves the question of
whether it's 0 or 1 based. "Position" is a bit better, but I think
Jian's suggestion of "occurance" is best.

--
Jim Nasby, Data Architect, Austin TX

---

From: "Dian Fay" <[email-masked]>
Date: Wed, 03 Jan 2024 18:05:47 -0500

> Jian's suggestion of "occurance" is best.

We do have precedent for one-based `index` in Postgres: array types are
1-indexed by default! "Occurrence" removes that ambiguity but it's long
and easy to misspell (I looked it up after typing it just now and it
_still_ feels off).

How's "instance"?

---

From: Jim Nasby <[email-masked]>
Date: Wed, 3 Jan 2024 17:25:59 -0600

On 1/3/24 5:05 PM, Dian Fay wrote:

How's "instance"?

Presumably someone referencing arguments by name would have just
looked up the names via \df or whatever, so presumably misspelling
wouldn't be a big issue. But I think "instance" is OK as well.

---

From: jian he <[email-masked]>
Date: Thu, 4 Jan 2024 15:03:00 +0800

On Thu, Jan 4, 2024 at 7:26 AM Jim Nasby <[email-masked]> wrote:
> Presumably someone referencing arguments by name would have just looked up the names via \df or whatever, so presumably misspelling wouldn't be a big issue. But I think "instance" is OK as well.
>
> --

regexp_instr: It has the syntax regexp_instr(string, pattern [, start
[, N [, endoption [, flags [, subexpr ]]]]])
oracle:
REGEXP_INSTR (source_char, pattern,  [, position [, occurrence [,
return_opt  [, match_param  [, subexpr ]]]]] )

"string" and "source_char" are almost the same descriptive, so maybe
there is no need to change.
"start" is better than "position", imho.
"return_opt" is better than "endoption", (maybe we need change, for
now I didn't)
"flags" cannot be changed to "match_param", given it quite everywhere
in functions-matching.html.

similarly for function regexp_replace, oracle using "repplace_string",
we use "replacement"(mentioned in the doc).
so I don't think we need to change to "repplace_string".

Based on how people google[0], I think `occurrence` is ok, even though
it's verbose.
to change from `N` to `occurrence`, we also need to change the doc,
that is why this patch is more larger.

[0]: https://www.google.com/search?q=regex+nth+match&oq=regex+nth+match&gs_lcrp=EgZjaHJvbWUyBggAEEUYOTIGCAEQRRg8MgYIAhBFGDzSAQc2MThqMGo5qAIAsAIA&sourceid=chrome&ie=UTF-8
From cb720fd696df3a34032ec2adaea712604a7e3d42 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 4 Jan 2024 13:02:35 +0800
Subject: [PATCH v2 1/1] add function argument names to regex.* functions.

 Specifically add function argument names to the following funtions:
      regexp_replace,
      regexp_match,
      regexp_matches
      regexp_count,
      regexp_instr,
      regexp_like,
      regexp_substr,
      regexp_split_to_table
      regexp_split_to_array
 So these functions can be easiler to understand in psql via \df.
 Also more important make these functions can be called in different notaions.
---
 doc/src/sgml/func.sgml          | 38 +++++++++---------
 src/include/catalog/pg_proc.dat | 71 ++++++++++++++++++++++++++-------
 2 files changed, 76 insertions(+), 33 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cec21e42..b04a427a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3299,7 +3299,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_instr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>occurrence</parameter> <type>integer</type>
          [, <parameter>endoption</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] ] )
@@ -3307,7 +3307,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Returns the position within <parameter>string</parameter> where
-        the <parameter>N</parameter>'th match of the POSIX regular
+        the <parameter>occurrence</parameter>'th match of the POSIX regular
         expression <parameter>pattern</parameter> occurs, or zero if there is
         no such match; see <xref linkend="functions-posix-regexp"/>.
        </para>
@@ -3478,14 +3478,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>occurrence</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] )
         <returnvalue>text</returnvalue>
        </para>
        <para>
         Returns the substring within <parameter>string</parameter> that
-        matches the <parameter>N</parameter>'th occurrence of the POSIX
+        matches the <parameter>occurrence</parameter>'th occurrence of the POSIX
         regular expression <parameter>pattern</parameter>,
         or <literal>NULL</literal> if there is no such match; see
         <xref linkend="functions-posix-regexp"/>.
@@ -5888,13 +5888,13 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
 
     <para>
      The <function>regexp_instr</function> function returns the starting or
-     ending position of the <replaceable>N</replaceable>'th match of a
+     ending position of the <replaceable>occurrence</replaceable>'th match of a
      POSIX regular expression pattern to a string, or zero if there is no
      such match.  It has the syntax
      <function>regexp_instr</function>(<replaceable>string</replaceable>,
      <replaceable>pattern</replaceable>
      <optional>, <replaceable>start</replaceable>
-     <optional>, <replaceable>N</replaceable>
+     <optional>, <replaceable>occurrence</replaceable>
      <optional>, <replaceable>endoption</replaceable>
      <optional>, <replaceable>flags</replaceable>
      <optional>, <replaceable>subexpr</replaceable>
@@ -5903,8 +5903,8 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
      in <replaceable>string</replaceable>, normally from the beginning of
      the string, but if the <replaceable>start</replaceable> parameter is
      provided then beginning from that character index.
-     If <replaceable>N</replaceable> is specified
-     then the <replaceable>N</replaceable>'th match of the pattern
+     If <replaceable>occurrence</replaceable> is specified
+     then the <replaceable>occurrence</replaceable>'th match of the pattern
      is located, otherwise the first match is located.
      If the <replaceable>endoption</replaceable> parameter is omitted or
      specified as zero, the function returns the position of the first
@@ -6024,8 +6024,8 @@ SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
      expression pattern to a string.  It has the same syntax as
      <function>regexp_match</function>.
      This function returns no rows if there is no match, one row if there is
-     a match and the <literal>g</literal> flag is not given, or <replaceable>N</replaceable>
-     rows if there are <replaceable>N</replaceable> matches and the <literal>g</literal> flag
+     a match and the <literal>g</literal> flag is not given, or <replaceable>occurrence</replaceable>
+     rows if there are <replaceable>occurrence</replaceable> matches and the <literal>g</literal> flag
      is given.  Each returned row is a text array containing the whole
      matched substring or the substrings matching parenthesized
      subexpressions of the <replaceable>pattern</replaceable>, just as described above
@@ -6079,10 +6079,10 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
      <function>regexp_replace</function>(<replaceable>source</replaceable>,
      <replaceable>pattern</replaceable>, <replaceable>replacement</replaceable>
      <optional>, <replaceable>start</replaceable>
-     <optional>, <replaceable>N</replaceable>
+     <optional>, <replaceable>occurrence</replaceable>
      </optional></optional>
      <optional>, <replaceable>flags</replaceable> </optional>).
-     (Notice that <replaceable>N</replaceable> cannot be specified
+     (Notice that <replaceable>occurrence</replaceable> cannot be specified
      unless <replaceable>start</replaceable> is,
      but <replaceable>flags</replaceable> can be given in any case.)
      The <replaceable>source</replaceable> string is returned unchanged if
@@ -6102,14 +6102,14 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
      the string, but if the <replaceable>start</replaceable> parameter is
      provided then beginning from that character index.
      By default, only the first match of the pattern is replaced.
-     If <replaceable>N</replaceable> is specified and is greater than zero,
-     then the <replaceable>N</replaceable>'th match of the pattern
+     If <replaceable>occurrence</replaceable> is specified and is greater than zero,
+     then the <replaceable>occurrence</replaceable>'th match of the pattern
      is replaced.
      If the <literal>g</literal> flag is given, or
-     if <replaceable>N</replaceable> is specified and is zero, then all
+     if <replaceable>occurrence</replaceable> is specified and is zero, then all
      matches at or after the <replaceable>start</replaceable> position are
      replaced.  (The <literal>g</literal> flag is ignored
-     when <replaceable>N</replaceable> is specified.)
+     when <replaceable>occurrence</replaceable> is specified.)
      The <replaceable>flags</replaceable> parameter is an optional text
      string containing zero or more single-letter flags that change the
      function's behavior.  Supported flags (though
@@ -6220,7 +6220,7 @@ SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
      <function>regexp_substr</function>(<replaceable>string</replaceable>,
      <replaceable>pattern</replaceable>
      <optional>, <replaceable>start</replaceable>
-     <optional>, <replaceable>N</replaceable>
+     <optional>, <replaceable>occurrence</replaceable>
      <optional>, <replaceable>flags</replaceable>
      <optional>, <replaceable>subexpr</replaceable>
      </optional></optional></optional></optional>).
@@ -6228,8 +6228,8 @@ SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
      in <replaceable>string</replaceable>, normally from the beginning of
      the string, but if the <replaceable>start</replaceable> parameter is
      provided then beginning from that character index.
-     If <replaceable>N</replaceable> is specified
-     then the <replaceable>N</replaceable>'th match of the pattern
+     If <replaceable>occurrence</replaceable> is specified
+     then the <replaceable>occurrence</replaceable>'th match of the pattern
      is returned, otherwise the first match is returned.
      The <replaceable>flags</replaceable> parameter is an optional text
      string containing zero or more single-letter flags that change the
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9052f526..c7e8a23c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3611,105 +3611,148 @@
   prosrc => 'replace_text' },
 { oid => '2284', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
-  proargtypes => 'text text text', prosrc => 'textregexreplace_noopt' },
+  proargtypes => 'text text text',
+  proargnames => '{source, pattern, replacement}',
+  prosrc => 'textregexreplace_noopt' },
 { oid => '2285', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
-  proargtypes => 'text text text text', prosrc => 'textregexreplace' },
+  proargtypes => 'text text text text',
+  proargnames => '{source, pattern, replacement, flags}',
+  prosrc => 'textregexreplace' },
 { oid => '6251', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
   proargtypes => 'text text text int4 int4 text',
+  proargnames => '{source, pattern, replacement, start, occurrence, flags}',
   prosrc => 'textregexreplace_extended' },
 { oid => '6252', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
   proargtypes => 'text text text int4 int4',
+  proargnames => '{source, pattern, replacement, start, occurrence}',
   prosrc => 'textregexreplace_extended_no_flags' },
 { oid => '6253', descr => 'replace text using regexp',
   proname => 'regexp_replace', prorettype => 'text',
   proargtypes => 'text text text int4',
+  proargnames => '{source, pattern, replacement, start}',
   prosrc => 'textregexreplace_extended_no_n' },
 { oid => '3396', descr => 'find first match for regexp',
   proname => 'regexp_match', prorettype => '_text', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_match_no_flags' },
 { oid => '3397', descr => 'find first match for regexp',
   proname => 'regexp_match', prorettype => '_text',
-  proargtypes => 'text text text', prosrc => 'regexp_match' },
+  proargtypes => 'text text text',
+  proargnames => '{string, pattern, flags}',
+  prosrc => 'regexp_match' },
 { oid => '2763', descr => 'find match(es) for regexp',
   proname => 'regexp_matches', prorows => '1', proretset => 't',
   prorettype => '_text', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_matches_no_flags' },
 { oid => '2764', descr => 'find match(es) for regexp',
   proname => 'regexp_matches', prorows => '10', proretset => 't',
   prorettype => '_text', proargtypes => 'text text text',
+  proargnames => '{string, pattern, flags}',
   prosrc => 'regexp_matches' },
 { oid => '6254', descr => 'count regexp matches',
   proname => 'regexp_count', prorettype => 'int4', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_count_no_start' },
 { oid => '6255', descr => 'count regexp matches',
   proname => 'regexp_count', prorettype => 'int4',
-  proargtypes => 'text text int4', prosrc => 'regexp_count_no_flags' },
+  proargtypes => 'text text int4',
+  proargnames => '{string, pattern, start}',
+  prosrc => 'regexp_count_no_flags' },
 { oid => '6256', descr => 'count regexp matches',
   proname => 'regexp_count', prorettype => 'int4',
-  proargtypes => 'text text int4 text', prosrc => 'regexp_count' },
+  proargtypes => 'text text int4 text',
+  proargnames => '{string, pattern, start, flags}',
+  prosrc => 'regexp_count' },
 { oid => '6257', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_instr_no_start' },
 { oid => '6258', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4',
-  proargtypes => 'text text int4', prosrc => 'regexp_instr_no_n' },
+  proargtypes => 'text text int4',
+  proargnames => '{string, pattern, start}',
+  prosrc => 'regexp_instr_no_n' },
 { oid => '6259', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4',
-  proargtypes => 'text text int4 int4', prosrc => 'regexp_instr_no_endoption' },
+  proargtypes => 'text text int4 int4',
+  proargnames => '{string, pattern, start, occurrence}',
+  prosrc => 'regexp_instr_no_endoption' },
 { oid => '6260', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4',
   proargtypes => 'text text int4 int4 int4',
+  proargnames => '{string, pattern, start, occurrence, endoption}',
   prosrc => 'regexp_instr_no_flags' },
 { oid => '6261', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4',
   proargtypes => 'text text int4 int4 int4 text',
+  proargnames => '{string, pattern, start, occurrence, endoption, flags}',
   prosrc => 'regexp_instr_no_subexpr' },
 { oid => '6262', descr => 'position of regexp match',
   proname => 'regexp_instr', prorettype => 'int4',
   proargtypes => 'text text int4 int4 int4 text int4',
+  proargnames => '{string, pattern, start, occurrence, endoption, flags, subexpr}',
   prosrc => 'regexp_instr' },
 { oid => '6263', descr => 'test for regexp match',
-  proname => 'regexp_like', prorettype => 'bool', proargtypes => 'text text',
+  proname => 'regexp_like', prorettype => 'bool',
+  proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_like_no_flags' },
 { oid => '6264', descr => 'test for regexp match',
   proname => 'regexp_like', prorettype => 'bool',
-  proargtypes => 'text text text', prosrc => 'regexp_like' },
+  proargtypes => 'text text text',
+  proargnames => '{string, pattern,flags}',
+  prosrc => 'regexp_like' },
 { oid => '6265', descr => 'extract substring that matches regexp',
   proname => 'regexp_substr', prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_substr_no_start' },
 { oid => '6266', descr => 'extract substring that matches regexp',
   proname => 'regexp_substr', prorettype => 'text',
-  proargtypes => 'text text int4', prosrc => 'regexp_substr_no_n' },
+  proargtypes => 'text text int4',
+  proargnames => '{string, pattern, start}',
+  prosrc => 'regexp_substr_no_n' },
 { oid => '6267', descr => 'extract substring that matches regexp',
   proname => 'regexp_substr', prorettype => 'text',
-  proargtypes => 'text text int4 int4', prosrc => 'regexp_substr_no_flags' },
+  proargtypes => 'text text int4 int4',
+  proargnames => '{string, pattern, start, occurrence}',
+  prosrc => 'regexp_substr_no_flags' },
 { oid => '6268', descr => 'extract substring that matches regexp',
   proname => 'regexp_substr', prorettype => 'text',
   proargtypes => 'text text int4 int4 text',
+  proargnames => '{string, pattern, start, occurrence, flags}',
   prosrc => 'regexp_substr_no_subexpr' },
 { oid => '6269', descr => 'extract substring that matches regexp',
   proname => 'regexp_substr', prorettype => 'text',
-  proargtypes => 'text text int4 int4 text int4', prosrc => 'regexp_substr' },
+  proargtypes => 'text text int4 int4 text int4',
+  proargnames => '{string, pattern, start, occurrence, flags, subexpr}',
+  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' },
 { oid => '2765', descr => 'split string by pattern',
   proname => 'regexp_split_to_table', prorows => '1000', proretset => 't',
   prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'regexp_split_to_table_no_flags' },
 { oid => '2766', descr => 'split string by pattern',
   proname => 'regexp_split_to_table', prorows => '1000', proretset => 't',
   prorettype => 'text', proargtypes => 'text text text',
+  proargnames => '{string, pattern, flags}',
   prosrc => 'regexp_split_to_table' },
 { oid => '2767', descr => 'split string by pattern',
   proname => 'regexp_split_to_array', prorettype => '_text',
-  proargtypes => 'text text', prosrc => 'regexp_split_to_array_no_flags' },
+  proargtypes => 'text text',
+  proargnames => '{string, pattern}',
+  prosrc => 'regexp_split_to_array_no_flags' },
 { oid => '2768', descr => 'split string by pattern',
   proname => 'regexp_split_to_array', prorettype => '_text',
-  proargtypes => 'text text text', prosrc => 'regexp_split_to_array' },
+  proargtypes => 'text text text',
+  proargnames => '{string, pattern, flags}',
+  prosrc => 'regexp_split_to_array' },
 { oid => '9030', descr => 'convert int4 number to binary',
   proname => 'to_bin', prorettype => 'text', proargtypes => 'int4',
   prosrc => 'to_bin32' },
-- 
2.34.1

---

From: "Dian Fay" <[email-masked]>
Date: Sun, 07 Jan 2024 19:44:55 -0500

> [0]: https://www.google.com/search?q=regex+nth+match&oq=regex+nth+match&gs_lcrp=EgZjaHJvbWUyBggAEEUYOTIGCAEQRRg8MgYIAhBFGDzSAQc2MThqMGo5qAIAsAIA&sourceid=chrome&ie=UTF-8

The `regexp_replace` summary in table 9.10 is mismatched and still
specifies the first parameter name as `string` instead of `source`.
Since all the other functions use `string`, should `regexp_replace` do
the same or is this a case where an established "standard" diverges?

I noticed the original documentation for some of these functions is
rather disorganized; summaries explain `occurrence` without explaining
the prior `start` parameter, and detailed documentation in 9.7 is
usually a single paragraph per function running pell-mell through ifs
and buts without section headings, so entries in table 9.10 have to
reference the entire section 9.7.3 instead of their specific functions.
It's out of scope here, but should I bring this up on pgsql-docs?

---

From: jian he <[email-masked]>
Date: Mon, 8 Jan 2024 22:26:34 +0800

> the same or is this a case where an established "standard" diverges?

got it. Thanks for pointing it out.

in functions-matching.html
if I change <replaceable>source</replaceable> to
<replaceable>string</replaceable> then
there are no markup "string" and markup "string", it's kind of
slightly confusing.

So does the following refactored description of regexp_replace make sense:

     The <replaceable>string</replaceable> is returned unchanged if
     there is no match to the <replaceable>pattern</replaceable>.  If there is a
     match, the <replaceable>string</replaceable> is returned with the
     <replaceable>replacement</replaceable> string substituted for the matching
     substring.  The <replaceable>replacement</replaceable> string can contain
     <literal>\</literal><replaceable>n</replaceable>, where
<replaceable>n</replaceable> is 1
     through 9, to indicate that the source substring matching the
     <replaceable>n</replaceable>'th parenthesized subexpression of
the pattern should be
     inserted, and it can contain <literal>\&amp;</literal> to indicate that the
     substring matching the entire pattern should be inserted.  Write
     <literal>\\</literal> if you need to put a literal backslash in
the replacement
     text.

> It's out of scope here, but should I bring this up on pgsql-docs?

I got it.
in Table 9.10. Other String Functions and Operators, if we can
reference the specific function would be great.
As for now, in the browser, you need to use Ctrl+F to find the
detailed explanation in 9.7.3.
you can just bring your suggested or patch to [email-masked].

---

From: "Dian Fay" <[email-masked]>
Date: Mon, 08 Jan 2024 19:51:58 -0500

>      <literal>\\</literal> if you need to put a literal backslash in

That change makes sense to me! I'll see about the section refactoring
after this lands.

---

From: jian he <[email-masked]>
Date: Wed, 10 Jan 2024 22:18:00 +0800

> That change makes sense to me! I'll see about the section refactoring

I put the changes into the new patch.
From 8fa04ed1fecb48ca8254d2ed7e60a9013fa130a3 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 10 Jan 2024 17:46:18 +0800
Subject: [PATCH v3 1/1] add function argument names to regex.* functions.

Specifically add function argument names to the following funtions:
    regexp_replace,
    regexp_match,
    regexp_matches
    regexp_count,
    regexp_instr,
    regexp_like,
    regexp_substr,
    regexp_split_to_table
    regexp_split_to_array
So it would be easier to understand these functions in psql via \df.
Also now these functions can be called in different notaions.
---
 doc/src/sgml/func.sgml          | 50 +++++++++++------------
 src/include/catalog/pg_proc.dat | 71 ++++++++++++++++++++++++++-------
 2 files changed, 82 insertions(+), 39 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index de78d58d..23ef07a5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3299,7 +3299,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_instr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>occurrence</parameter> <type>integer</type>
          [, <parameter>endoption</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] ] )
@@ -3307,7 +3307,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Returns the position within <parameter>string</parameter> where
-        the <parameter>N</parameter>'th match of the POSIX regular
+        the <parameter>occurrence</parameter>'th match of the POSIX regular
         expression <parameter>pattern</parameter> occurs, or zero if there is
         no such match; see <xref linkend="functions-posix-regexp"/>.
        </para>
@@ -3413,14 +3413,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        <entry role="func_table_entry"><para role="func_signature">
         <function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>,
          <parameter>start</parameter> <type>integer</type>,
-         <parameter>N</parameter> <type>integer</type>
+         <parameter>occurrence</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type> ] )
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Replaces the substring that is the <parameter>N</parameter>'th
+        Replaces the substring that is the <parameter>occurrence</parameter>'th
         match to the POSIX regular expression <parameter>pattern</parameter>,
-        or all such matches if <parameter>N</parameter> is zero; see
+        or all such matches if <parameter>occurrence</parameter> is zero; see
         <xref linkend="functions-posix-regexp"/>.
        </para>
        <para>
@@ -3478,14 +3478,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>occurrence</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] )
         <returnvalue>text</returnvalue>
        </para>
        <para>
         Returns the substring within <parameter>string</parameter> that
-        matches the <parameter>N</parameter>'th occurrence of the POSIX
+        matches the <parameter>occurrence</parameter>'th occurrence of the POSIX
         regular expression <parameter>pattern</parameter>,
         or <literal>NULL</literal> if there is no such match; see
         <xref linkend="functions-posix-regexp"/>.
@@ -5888,13 +5888,13 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
 
     <para>
@@ -6076,18 +6076,18 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
      The <function>regexp_replace</function> function provides substitution of
      new text for substrings that match POSIX regular expression patterns.
      It has the syntax
-     <function>regexp_replace</function>(<replaceable>source</replaceable>,
+     <function>regexp_replace</function>(<replaceable>string</replaceable>,
-     The <replaceable>source</replaceable> string is returned unchanged if
+     The <replaceable>string</replaceable> is returned unchanged if
-     match, the <replaceable>source</replaceable> string is returned with the
+     match, the <replaceable>string</replaceable> is returned with the
      <literal>\</literal><replaceable>n</replaceable>, where <replaceable>n</replaceable> is 1
index 79793927..3b4330a6 100644
+  proargnames => '{string, pattern, replacement}',
+  proargnames => '{string, pattern, replacement, flags}',
+  proargnames => '{string, pattern, replacement, start, occurrence, flags}',
+  proargnames => '{string, pattern, replacement, start, occurrence}',
+  proargnames => '{string, pattern, replacement, start}',

---

From: "Dian Fay" <[email-masked]>
Date: Wed, 10 Jan 2024 22:57:33 -0500

> I put the changes into the new patch.

Sorry, I missed one minor issue with v2. The replacement on lines
6027-6028 of func.sgml (originally "`n` rows if there are `n` matches")
is not needed and could be more confusing since the `n` represents a
number, not an argument to `regexp_matches`. I've built v3 and gone over
everything else one more time and it looks good.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 18 Jan 2024 09:17:11 +0100

> I put the changes into the new patch.

Reading back through the discussion, I wasn't quite able to interpret 
the resolution regarding Oracle compatibility.  From the patch, it looks 
like you chose not to adopt the parameter names from Oracle.  Was that 
your intention?

---

From: jian he <[email-masked]>
Date: Sat, 20 Jan 2024 10:55:41 +0800

> like you chose not to adopt the parameter names from Oracle.  Was that

per committee message:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=6424337073589476303b10f6d7cc74f501b8d9d7
Even if the names are all the same, our function is still not the same
as oracle.

There is a documentation bug.
In [0], Table 9.25. Regular Expression Functions Equivalencies
regexp_replace function definition: regexp_replace(string, pattern, replacement)

In one of the <tip> section below, regexp_replace explains as
<<<<<
The regexp_replace function provides substitution of new text for
substrings that match POSIX regular expression patterns. It has the
syntax regexp_replace(source, pattern, replacement [, start [, N ]] [,
flags ]). (Notice that N cannot be specified unless start is, but
flags can be given in any case.)
<<<<<
So I changed the first argument of regexp_replace to "string". So
accordingly, the doc needs to change also, which I did.

another regex* function argument changes: from "N" to "occurences",  example:
    + If <replaceable>occurrence</replaceable> is specified
    + then the <replaceable>occurrence</replaceable>'th match of the pattern
    + is located,

but [2] says
Speaking of the "occurrence'th
occurrence" is just silly, not to mention long and easy to misspell."

summary:
adding function-named notation is my intention.
To make regex.* functions named-notation works, we need to add
proargnames to src/include/catalog/pg_proc.dat.
add proargnames also require changing the doc.
naming proargnames is a matter of taste now, So I only change 'N' to
'occurrence'.

[0] https://www.postgresql.org/docs/current/functions-matching.html
[1] https://www.postgresql.org/message-id/flat/fc160ee0-c843-b024-29bb-97b5da61971f%40darold.net

---

From: jian he <[email-masked]>
Date: Sat, 20 Jan 2024 11:03:02 +0800

> occurrence" is just silly, not to mention long and easy to misspell."

sorry.
[2], The reference link is
https://www.postgresql.org/message-id/1567465.1627860115%40sss.pgh.pa.us

my previous post will link to the whole thread.

---

From: Tom Lane <[email-masked]>
Date: Tue, 02 Apr 2024 16:45:33 -0400

> Even if the names are all the same, our function is still not the same

The fact that there's minor discrepancies in the regex languages
doesn't seem to me to have a lot of bearing on whether we should
follow Oracle's choices of parameter names.

However, if we do follow Oracle, it seems like we should do that
consistently, which this patch doesn't.  For instance, per [1]
Oracle calls the arguments of regex_substr

	source_char,
	pattern,
	position,
	occurrence,
	match_param,
	subexpr

while we have

	string,
	pattern,
	start,
	N,
	flags,
	subexpr

The patch proposes to replace "N" with "occurrence" but not touch
the other discrepancies, which seems to me to be a pretty poor
choice.  "occurrence" is very long and difficult to spell correctly,
and if you're not following Oracle slavishly, exactly what is the
argument in its favor?  I quite agree that Oracle's other choices
aren't improvements over ours, but neither is that one.

On the whole my inclination would be to stick to the names we have
in the documentation.  There might be an argument for changing "N"
to something lower-case so you don't have to quote it; but if we do,
I'd go for, say, "count".

			regards, tom lane

---

From: jian he <[email-masked]>
Date: Thu, 4 Apr 2024 21:54:53 +0800

> I'd go for, say, "count".

we have
---------------------------------------------------------------
The replacement string can contain \n, where n is 1 through 9, to
indicate that the source substring matching the n'th parenthesized
subexpression of the pattern should be inserted, and it can contain \&
to indicate that the substring matching the entire pattern should be
inserted.
----------------------------------------------------------------------------
in the regexp_replace explanation section.
changing "N" to lower-case would be misleading for regexp_replace?
so I choose "count".

By the way, I think the above  is so hard to comprehend.
I can only find related test in src/test/regress/sql/strings.sql are:
SELECT regexp_replace('1112223333', E'(\\d{3})(\\d{3})(\\d{4})',
E'(\\1) \\2-\\3');
SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\&Y', 'g');
SELECT regexp_replace('foobarrbazz', E'(.)\\1', E'X\\\\Y', 'g');

but these tests seem not friendly.
maybe we should have some simple examples to demonstrate the above paragraph.
From 372a0c6cb894194b819fc380efda179bf6d1055d Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Thu, 4 Apr 2024 21:50:13 +0800
Subject: [PATCH v4 1/1] add regex functions argument names.

Specifically add function argument names to the following funtions:
    regexp_replace,
    regexp_match,
    regexp_matches,
    regexp_count,
    regexp_instr,
    regexp_like,
    regexp_substr,
    regexp_split_to_table,
    regexp_split_to_array
So it would be easier to understand these functions in psql via \df.
now these functions can be called in different notaions.

discussion: https://postgr.es/m/CACJufxG3NFKKsh6x4fRLv8h3V-HvN4W5dA%3DzNKMxsNcDwOKang%40mail.gmail.com
---
 doc/src/sgml/func.sgml          | 50 +++++++++++------------
 src/include/catalog/pg_proc.dat | 71 ++++++++++++++++++++++++++-------
 2 files changed, 82 insertions(+), 39 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ff690113..57ad1624 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3332,7 +3332,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_instr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>count</parameter> <type>integer</type>
          [, <parameter>endoption</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] ] )
@@ -3340,7 +3340,7 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Returns the position within <parameter>string</parameter> where
-        the <parameter>N</parameter>'th match of the POSIX regular
+        the <parameter>count</parameter>'th match of the POSIX regular
         expression <parameter>pattern</parameter> occurs, or zero if there is
         no such match; see <xref linkend="functions-posix-regexp"/>.
        </para>
@@ -3446,14 +3446,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        <entry role="func_table_entry"><para role="func_signature">
         <function>regexp_replace</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>replacement</parameter> <type>text</type>,
          <parameter>start</parameter> <type>integer</type>,
-         <parameter>N</parameter> <type>integer</type>
+         <parameter>count</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type> ] )
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Replaces the substring that is the <parameter>N</parameter>'th
+        Replaces the substring that is the <parameter>count</parameter>'th
         match to the POSIX regular expression <parameter>pattern</parameter>,
-        or all such matches if <parameter>N</parameter> is zero; see
+        or all such matches if <parameter>count</parameter> is zero; see
         <xref linkend="functions-posix-regexp"/>.
        </para>
        <para>
@@ -3511,14 +3511,14 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         </indexterm>
         <function>regexp_substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>
          [, <parameter>start</parameter> <type>integer</type>
-         [, <parameter>N</parameter> <type>integer</type>
+         [, <parameter>count</parameter> <type>integer</type>
          [, <parameter>flags</parameter> <type>text</type>
          [, <parameter>subexpr</parameter> <type>integer</type> ] ] ] ] )
         <returnvalue>text</returnvalue>
        </para>
        <para>
         Returns the substring within <parameter>string</parameter> that
-        matches the <parameter>N</parameter>'th occurrence of the POSIX
+        matches the <parameter>count</parameter>'th occurrence of the POSIX
         regular expression <parameter>pattern</parameter>,
         or <literal>NULL</literal> if there is no such match; see
         <xref linkend="functions-posix-regexp"/>.
@@ -5921,13 +5921,13 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
 
     <para>
+     ending position of the <replaceable>count</replaceable>'th match of a
+     <optional>, <replaceable>count</replaceable>
@@ -5936,8 +5936,8 @@ regexp_count('ABCABCAXYaxy', 'A.', 1, 'i')  <lineannotation>4</lineannotation>
+     If <replaceable>count</replaceable> is specified
+     then the <replaceable>count</replaceable>'th match of the pattern
@@ -6057,8 +6057,8 @@ SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
+     a match and the <literal>g</literal> flag is not given, or <replaceable>count</replaceable>
+     rows if there are <replaceable>count</replaceable> matches and the <literal>g</literal> flag
@@ -6109,18 +6109,18 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
+     <optional>, <replaceable>count</replaceable>
+     (Notice that <replaceable>count</replaceable> cannot be specified
@@ -6135,14 +6135,14 @@ SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab;
+     If <replaceable>count</replaceable> is specified and is greater than zero,
+     then the <replaceable>count</replaceable>'th match of the pattern
+     if <replaceable>count</replaceable> is specified and is zero, then all
+     when <replaceable>count</replaceable> is specified.)
@@ -6253,7 +6253,7 @@ SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
+     <optional>, <replaceable>count</replaceable>
@@ -6261,8 +6261,8 @@ SELECT foo FROM regexp_split_to_table('the quick brown fox', '\s*') AS foo;
+     If <replaceable>count</replaceable> is specified
+     then the <replaceable>count</replaceable>'th match of the pattern
index 153d816a..be28950c 100644
@@ -3623,105 +3623,148 @@
+  proargnames => '{string, pattern, replacement, start, count, flags}',
+  proargnames => '{string, pattern, replacement, start, count}',
+  proargnames => '{string, pattern, start, count}',
+  proargnames => '{string, pattern, start, count, endoption}',
+  proargnames => '{string, pattern, start, count, endoption, flags}',
+  proargnames => '{string, pattern, start, count, endoption, flags, subexpr}',
+  proargnames => '{string, pattern, start, count}',
+  proargnames => '{string, pattern, start, count, flags}',
+  proargnames => '{string, pattern, start, count, flags, subexpr}',

base-commit: 6f4d63e989ffbdb44f5856a17fae5ae68c40327d
-- 
2.34.1