Subject: add function argument name to substring and substr

From: jian he <[email-masked]>
Date: Wed, 22 Jan 2025 14:21:05 +0800

hi.

attached patch add function argument name to function substring and substr
you can see the visual changes.
mater behavior:

\df substring
                             List of functions
   Schema   |   Name    | Result data type |   Argument data types   | Type
------------+-----------+------------------+-------------------------+------
 pg_catalog | substring | bit              | bit, integer            | func
 pg_catalog | substring | bit              | bit, integer, integer   | func
 pg_catalog | substring | bytea         | bytea, integer          | func
 pg_catalog | substring | bytea         | bytea, integer, integer | func
 pg_catalog | substring | text             | text, integer           | func
 pg_catalog | substring | text             | text, integer, integer  | func
 pg_catalog | substring | text             | text, text              | func
 pg_catalog | substring | text             | text, text, text        | func

with patch
                                          List of functions
   Schema   |   Name    | Result data type |               Argument
data types                | Type
------------+-----------+------------------+--------------------------------------------------+------
 pg_catalog | substring | bit              | bits bit, start integer
                       | func
 pg_catalog | substring | bit              | bits bit, start integer,
count integer           | func
 pg_catalog | substring | bytea         | bytes bytea, start integer
                    | func
 pg_catalog | substring | bytea         | bytes bytea, start integer,
count integer        | func
 pg_catalog | substring | text             | string text, pattern text
                       | func
 pg_catalog | substring | text             | string text, pattern
text, escape_character text | func
 pg_catalog | substring | text             | string text, start
integer                       | func
 pg_catalog | substring | text             | string text, start
integer, count integer        | func

I did the same change to the function substr.
since 9.7.2. SIMILAR TO Regular Expressions we use
substring(string, pattern, escape-character)
so i refactor the substring function argument name to
substring(string text, pattern text, escape_character text).

we can make it as ``substring(string text, pattern text, escape text).``
then in 9.7.2, we need to change the substring synopsis section.

one thing I am not sure is about
9.4. String Functions and Operators
Table 9.9. SQL String Functions and Operators.
Do we need entries for substring related functions?

current signature
substring ( string text [ FROM start integer ] [ FOR count integer ] )
is kind of different from
substring(string text, start integer, count integer).
since the previous one can allow keywords "FROM", "FOR", the latter one won't.

---

From: jian he <[email-masked]>
Date: Mon, 27 Jan 2025 10:20:16 +0800

hi.

I forgot to attach the patch.
here we are.
From 249a083627b0d8ad15c698601f7b7649be5262a1 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 22 Jan 2025 14:19:15 +0800
Subject: [PATCH v1 1/1] add function argument name to function substring and
 substr

---
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          | 12 ++++++++++++
 2 files changed, 13 insertions(+), 1 deletion(-)

diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 591157b1d1..0fc1d9a4a5 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
  IMMUTABLE PARALLEL SAFE STRICT COST 1
 RETURN rpad($1, $2, ' ');
 
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 18560755d2..b2ebccb5b6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3637,6 +3637,7 @@
   prosrc => 'rtrim' },
 { oid => '877', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{string, start, count}',
   prosrc => 'text_substr' },
 { oid => '878', descr => 'map a set of characters appearing in string',
   proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3655,6 +3656,7 @@
   prosrc => 'rtrim1' },
 { oid => '883', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{string, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '884', descr => 'trim selected characters from both ends of string',
   proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3665,9 +3667,11 @@
 
 { oid => '936', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{string, start, count}',
   prosrc => 'text_substr' },
 { oid => '937', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{string, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '2087',
   descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4085,6 +4089,7 @@
   prosrc => 'bitcat' },
 { oid => '1680', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+  proargnames => '{bits, start, count}',
   prosrc => 'bitsubstr' },
 { oid => '1681', descr => 'bitstring length',
   proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4114,6 +4119,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{bits, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6185,15 +6191,19 @@
   prosrc => 'byteacat' },
 { oid => '2012', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea',
+  proargnames => '{bytes, start, count}',
   proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
 { oid => '2013', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{bytes, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+  proargnames => '{bytes, start, count}',
   prosrc => 'bytea_substr' },
 { oid => '2086', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{bytes, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2014', descr => 'position of substring',
   proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6382,9 +6392,11 @@
 
 { oid => '2073', descr => 'extract text matching regular expression',
   proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{string, pattern}',
   prosrc => 'textregexsubstr' },
 { oid => '2074', descr => 'extract text matching SQL regular expression',
   proname => 'substring', prolang => 'sql', prorettype => 'text',
+  proargnames => '{string, pattern, escape_character}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 17 Feb 2025 15:12:59 -0700

> since the previous one can allow keywords "FROM", "FOR", the latter one
Table 9.9 limits itself to those functions defined in the SQL standard;
which are basically the ones that use keywords instead of commas.

The substring(string, start, count) function you note is already covered in
Table 9.10 but we spell it substr(...)

I don't think adding yet more spellings of this same function is warranted
or desirable at this point.  I'd maybe add a note if substring(,,,) works
to substr saying that substring is a valid alias.  I could be convinced to
just document though.

David J.

---

From: vignesh C <[email-masked]>
Date: Mon, 17 Mar 2025 12:18:25 +0530

> I forgot to attach the patch.

I noticed that David's comments from [1] have not yet been addressed,
I have changed the status of commitfest entry to "Waiting on Author",
please address them and change the status to "Needs review".
[1] - https://www.postgresql.org/message-id/CAKFQuwYD477A9Oy2PxRr_KY8t=k=[email-masked]

Regards,
Vignesh

---

From: jian he <[email-masked]>
Date: Tue, 18 Mar 2025 11:20:16 +0800

On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
<[email-masked]> wrote:
>
> Table 9.9 limits itself to those functions defined in the SQL standard; which are basically the ones that use keywords instead of commas.
>
> The substring(string, start, count) function you note is already covered in Table 9.10 but we spell it substr(...)
>
> I don't think adding yet more spellings of this same function is warranted or desirable at this point.

ok.

> I'd maybe add a note if substring(,,,) works to substr saying that substring is a valid alias.  I could be convinced to just document though.
>
it seems already in the doc.

substr ( string text, start integer [, count integer ] ) → text
Extracts the substring of string starting at the start'th character,
and extending for count characters if that is specified. (Same as
substring(string from start for count).)

substr ( bytes bytea, start integer [, count integer ] ) → bytea
Extracts the substring of bytes starting at the start'th byte, and
extending for count bytes if that is specified. (Same as
substring(bytes from start for count).)

new patch attached.
main changes:
1. change 3 argument func argument from
(string text, pattern text, escape_character text)
to
(string text, pattern text, escape text)

2. add synopsis section in 9.7.3. POSIX Regular Expressions for
function substring.
we only have the synopsis section for function substring in 9.7.2
section, now add it to 9.7.3.
also add an example about using named natation call substring:
substring(string=>'foobar', pattern=> 'o.b')

the patch is small, I just put the
\df substring
\df substr
(before and after patch) output into the commit message.
From f0b49ccd27d41ff1bd1619284656efcb1d3c3e0f Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Tue, 18 Mar 2025 11:13:11 +0800
Subject: [PATCH v2 1/1] add argument name to function substring and substr

visual changes.
before

\df substr
                            List of functions
   Schema   |  Name  | Result data type |   Argument data types   | Type
------------+--------+------------------+-------------------------+------
 pg_catalog | substr | bytea            | bytea, integer          | func
 pg_catalog | substr | bytea            | bytea, integer, integer | func
 pg_catalog | substr | text             | text, integer           | func
 pg_catalog | substr | text             | text, integer, integer  | func
(4 rows)

now
                                     List of functions
   Schema   |  Name  | Result data type |            Argument data types            | Type
------------+--------+------------------+-------------------------------------------+------
 pg_catalog | substr | bytea            | bytes bytea, start integer                | func
 pg_catalog | substr | bytea            | bytes bytea, start integer, count integer | func
 pg_catalog | substr | text             | string text, start integer                | func
 pg_catalog | substr | text             | string text, start integer, count integer | func
(4 rows)

before
\df substring
                             List of functions
(8 rows)

now
                                      List of functions
   Schema   |   Name    | Result data type |            Argument data types            | Type
------------+-----------+------------------+-------------------------------------------+------
 pg_catalog | substring | bit              | bits bit, start integer                   | func
 pg_catalog | substring | bit              | bits bit, start integer, count integer    | func
 pg_catalog | substring | bytea            | bytes bytea, start integer                | func
 pg_catalog | substring | bytea            | bytes bytea, start integer, count integer | func
 pg_catalog | substring | text             | string text, pattern text                 | func
 pg_catalog | substring | text             | string text, pattern text, escape text    | func
 pg_catalog | substring | text             | string text, start integer                | func
 pg_catalog | substring | text             | string text, start integer, count integer | func
(8 rows)
---
 doc/src/sgml/func.sgml                   | 23 +++++++++++++++--------
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          | 12 ++++++++++++
 3 files changed, 28 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1c3810e1a04..be47e06727d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5816,7 +5816,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
 </synopsis>
     or as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
 </synopsis>
     As with <literal>SIMILAR TO</literal>, the
     specified pattern must match the entire data string, or else the
@@ -6020,11 +6020,17 @@ substring('foobar' similar '#"o_b#"%' escape '#')    <lineannotation>NULL</linea
     </para>
 
     <para>
-     The <function>substring</function> function with two parameters,
-     <function>substring(<replaceable>string</replaceable> from
-     <replaceable>pattern</replaceable>)</function>, provides extraction of a
-     substring
-     that matches a POSIX regular expression pattern.  It returns null if
+     The <function>substring</function> function with two parameters provides extraction of a
+     substring that matches a POSIX regular expression pattern.
+     It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+     It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+     It returns null if
      there is no match, otherwise the first portion of the text that matched the
      pattern.  But if the pattern contains any parentheses, the portion
      of the text that matched the first parenthesized subexpression (the
@@ -6039,8 +6045,9 @@ substring('foobar' similar '#"o_b#"%' escape '#')    <lineannotation>NULL</linea
    <para>
     Some examples:
 <programlisting>
-substring('foobar' from 'o.b')     <lineannotation>oob</lineannotation>
-substring('foobar' from 'o(.)b')   <lineannotation>o</lineannotation>
+substring('foobar' from 'o.b')                   <lineannotation>oob</lineannotation>
+substring(string=>'foobar', pattern=> 'o.b')     <lineannotation>oob</lineannotation>
+substring('foobar' from 'o(.)b')                 <lineannotation>o</lineannotation>
 </programlisting>
    </para>
 
index 566f308e443..544b549ae74 100644
 
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
index 890822eaf79..1e023c6465b 100644
@@ -3668,6 +3668,7 @@
@@ -3686,6 +3687,7 @@
@@ -3696,9 +3698,11 @@
 
@@ -4116,6 +4120,7 @@
@@ -4145,6 +4150,7 @@
 
@@ -6239,15 +6245,19 @@
@@ -6436,9 +6446,11 @@
 
+  proargnames => '{string, pattern, escape}',

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Mar 2025 17:18:28 -0700

On Mon, Mar 17, 2025 at 8:20 PM jian he <[email-masked]> wrote:

> > The substring(string, start, count) function you note is already covered
> in Table 9.10 but we spell it substr(...)
> >
> > I'd maybe add a note if substring(,,,) works to substr saying that
> substring is a valid alias.  I could be convinced to just document though.
> >

Why?  It can only be one character so that existing name seems well chosen.

postgres=# select substring('123^^,123',',','^^');
ERROR:  invalid escape string
HINT:  Escape string must be empty or one character.
CONTEXT:  SQL function "substring" statement 1

> section, now add it to 9.7.3.

I'd probably try and resolve that the other way...point the reader to the
reference page for the function if they want to see syntax.  I'm mixed on
(leaning against) whether choosing this place to demonstrate all the
possible spellings is the best.

> substring(string=>'foobar', pattern=> 'o.b')

I'm already pulling my hair out at this showing all the insanity that
exists without adding this to the mix.

The vast majority of examples throughout the manual use traditional
function call syntax  func_name(arg1, arg2, etc.);  I'd rather keep with
convention than start to scatter about alternative syntax choices just to
give the random reader who happens upon this fairly esoteric part of the
manual the benefit of seeing their options.  If that is a goal, then I'd
suggest spending some time in our Tutorial adding some more examples with
these alternative forms to people looking to be exposed to new things in
the place they'd go to look for them.  They probably won't learn about them
from the Syntax section.

On the plus side, I agree now we should add:
substring(string text, pattern text[, escape-character text])
to Table 9.10

I'd also rename escape to escape-character in the other SQL substring
function synopses.  The RegEx page got that part correct.

Do as little or as much with the RegEx section as you'd like, though it
seems like separate material from $subject.  The page seems to already use
replaceable names instead of data types so on that score it should be
unaffected if we've chosen the same names.

Food for thought, it seems a bit redundant to name the first argument
basically the same as the data type.  I was thinking that "content" would
be a better choice.  This is basically a polymorphic function where all the
inputs are the same thing just having different types - and that thing is
being "content".

David J.

---

From: jian he <[email-masked]>
Date: Wed, 19 Mar 2025 09:19:42 +0800

On Wed, Mar 19, 2025 at 8:19 AM David G. Johnston
<[email-masked]> wrote:
>
> The vast majority of examples throughout the manual use traditional function call syntax  func_name(arg1, arg2, etc.);  I'd rather keep with convention than start to scatter about alternative syntax choices just to give the random reader who happens upon this fairly esoteric part of the manual the benefit of seeing their options.  If that is a goal, then I'd suggest spending some time in our Tutorial adding some more examples with these alternative forms to people looking to be exposed to new things in the place they'd go to look for them.  They probably won't learn about them from the Syntax section.
>
in Table Table 9.9 we have
```
substring ( string text FROM pattern text ) → text
Extracts the first substring matching POSIX regular expression; see
Section 9.7.3.
substring('Thomas' from '...$') → mas
```

can we change to
substring ( string text FROM pattern text ) → text
substring ( string text, pattern text ) → text
Extracts the first substring matching POSIX regular expression;
the second format is not standardized. see Section 9.7.3.
substring('Thomas' from '...$') → mas

if we add to
``substring ( string text, pattern text ) → text``
Table 9.10,
then maybe it feels like duplication?
(same function in Table 9.9, Table 9.10, then we also need some words
saying that they are the same)

I do realized we have brief explanation about Table 9.9 and Table 9.10 in
second paragraph of
https://www.postgresql.org/docs/current/functions-string.html

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Mar 2025 18:35:52 -0700

> substring('Thomas' from '...$') → mas
No, based on the (I presume) fact that the substring(string, pattern)
variant is not defined in the SQL standard and Table 9.9 is reserved for
those functions.

It would be a different, but probably worth considering, patch to simply
combine Tables 9.9 and 9.10 and just denote which entries are standard and
which are not.  The decision to split the tables along that property came
well before our current table format which seems much more amenable to
merging them together.

> saying that they are the same)

We can/should add substring(string, pattern) to Table 9.10 for the same
reason and the same general wording that substr(string, start) exists on
that table.

I would be in favor of adding a similar "same as" comment to the functions
in Table 9.9

I just now processed the cross references in Table 9.9 to the POSIX
section.  The new entry in Table 9.10 would want that too.

David J.

---

From: jian he <[email-masked]>
Date: Wed, 19 Mar 2025 12:04:21 +0800

hi.

new patch attached.
now \df substring looks like:
                                          List of functions
 pg_catalog | substring | bytea            | bytes bytea, start
 pg_catalog | substring | bytea            | bytes bytea, start

Table 9.10 also have doc entry for substring.
in  Table 9.10, i have:
substring ( string text, pattern text ) → text
substring ( string text, pattern text, escape_character text) → text

we can put it into one,
but one is based on POSIX regular expression, another one is based on SQL,
the difference is big, that's why put it into two.
From 835dc9364f9f986445b77af8fa761e7577cbbdff Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 19 Mar 2025 11:58:55 +0800
Subject: [PATCH v3 1/1] add argument name to function substring and substr

also add substr and substring (no keywords) functions entry to doc.
Table 9.9 is about keywords substring entry,
now Table 9.10 have function argument doc entry of substring.

now psql looks like:

\df substring
                                          List of functions
 pg_catalog | substring | text             | string text, pattern text, escape_character text | func

\df substr
                                     List of functions

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=[email-masked]
---
 doc/src/sgml/func.sgml                   | 111 +++++++++++++++++++++--
 src/backend/catalog/system_functions.sql |   2 +-
 src/include/catalog/pg_proc.dat          |  12 +++
 3 files changed, 118 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2ab5661602c..88ca1ef3f54 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3806,6 +3806,58 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para></entry>
       </row>
 
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching POSIX regular expression; see
+        <xref linkend="functions-posix-regexp"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '...$')</literal>
+        <returnvalue>mas</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching <acronym>SQL</acronym> regular expression;
+        see <xref linkend="functions-similarto-regexp"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+        <returnvalue>oma</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the substring of <parameter>string</parameter> starting at
+        the <parameter>start</parameter>'th character,
+        and stopping after <parameter>count</parameter> characters if that is
+        specified.
+       </para>
+
+       <para>
+        <literal>substring('Thomas', 2, 3)</literal>
+        <returnvalue>hom</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4811,6 +4863,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
         <returnvalue>\x5678</returnvalue>
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Extracts the substring of <parameter>bytes</parameter> starting at
+        the <parameter>start</parameter>'th byte,
+        and stopping after <parameter>count</parameter> bytes if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring('\x1234567890'::bytea, 3, 2)</literal>
+        <returnvalue>\x5678</returnvalue>
+       </para></entry>
+      </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -5353,6 +5426,26 @@ cast(-1234 as bytea)           <lineannotation>\xfffffb2e</lineannotation>
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>bits</parameter> <type>bit</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <returnvalue>bit</returnvalue>
+       </para>
+       <para>
+        Extracts the substring of <parameter>bits</parameter> starting at
+        the <parameter>start</parameter>'th bit,
+        and stopping after <parameter>count</parameter> bits if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(B'110010111111', 3, 2)</literal>
+        <returnvalue>00</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -5816,7 +5909,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
 </synopsis>
     or as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
 </synopsis>
     As with <literal>SIMILAR TO</literal>, the
     specified pattern must match the entire data string, or else the
@@ -6020,11 +6113,17 @@ substring('foobar' similar '#"o_b#"%' escape '#')    <lineannotation>NULL</linea
     </para>
 
     <para>
index 566f308e443..5ea9d786b60 100644
 
index 890822eaf79..e63a8279e16 100644

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 15:22:14 -0700

> new patch attached.
I've done v4 with a delta patch.

Decided to standardize on calling the SQL Similar To regular expression
escape replaceable "escape" everywhere.

Instead of fully documenting the obsolete syntax I added a note explaining
the keyword choice difference.  Removed mention of it completely from the
Pattern Matching portion of the documentation - that section has enough
going on.

I also add "Same as" references for the two pairs of entries.  Not married
to them but they do seem warranted; having Pattern Matching be required
reading to make that connection seems undesirable.

David J.
From b2f64615da9522427a2e2662b1d060ffed97088c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 14:32:12 -0700
Subject: [PATCH 1/2] v3 0001 substring

---
 doc/src/sgml/func.sgml                   | 115 +++++++++++++++++++++--
 src/backend/catalog/system_functions.sql |   2 +-
 src/include/catalog/pg_proc.dat          |  12 +++
 3 files changed, 120 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c642f1ea4e..e4c95f1e88 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2850,9 +2850,9 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
         <returnvalue>text</returnvalue>
        </para>
-       <para role="func_signature">
+       <para role="func_signature"><s>
         <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
-        <returnvalue>text</returnvalue>
+        <returnvalue>text</returnvalue></s>
        </para>
        <para>
         Extracts the first substring matching <acronym>SQL</acronym> regular expression;
@@ -3806,6 +3806,58 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para></entry>
       </row>
 
+
+      <row>
 
+      <row>
 
     <para>
index 566f308e44..5ea9d786b6 100644
 
index 0737eb73c9..87e3006fef 100644

 

From 182f0b447d7860fe5db62863da0d7f582a0abfb9 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 15:10:28 -0700
Subject: [PATCH 2/2] v3 0002 delta

---
 doc/src/sgml/func.sgml                   | 49 ++++++++++++------------
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  2 +-
 3 files changed, 26 insertions(+), 27 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e4c95f1e88..b5e315c17b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2837,7 +2837,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Extracts the first substring matching POSIX regular expression; see
-        <xref linkend="functions-posix-regexp"/>.
+        <xref linkend="functions-posix-regexp"/>.  (Same as
+        <literal>substring(string text, pattern text)</literal>.)
        </para>
        <para>
         <literal>substring('Thomas' from '...$')</literal>
@@ -2850,20 +2851,20 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
         <returnvalue>text</returnvalue>
        </para>
-       <para role="func_signature"><s>
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
-        <returnvalue>text</returnvalue></s>
-       </para>
        <para>
         Extracts the first substring matching <acronym>SQL</acronym> regular expression;
-        see <xref linkend="functions-similarto-regexp"/>.  The first form has
-        been specified since SQL:2003; the second form was only in SQL:1999
-        and should be considered obsolete.
+        see <xref linkend="functions-similarto-regexp"/>.  (Same as
+        <literal>substring(string text, pattern text, escape text)</literal>.)
        </para>
        <para>
         <literal>substring('Thomas' similar '%#"o_a#"_' escape '#')</literal>
         <returnvalue>oma</returnvalue>
-       </para></entry>
+       </para>
+       <para>
+        Obsolescence note: SQL:1999 introduced this function with <literal>FROM</literal>
+        and <literal>FOR</literal> as the keywords but switched to this in SQL:2003.
+       </para>
+       </entry>
       </row>
 
       <row>
@@ -3814,7 +3815,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Extracts the first substring matching POSIX regular expression; see
-        <xref linkend="functions-posix-regexp"/>.
+        <xref linkend="functions-posix-regexp"/>.  (Same as
+        <literal>substring(string text FROM pattern text)</literal>.)
        </para>
        <para>
         <literal>substring('Thomas', '...$')</literal>
@@ -3824,12 +3826,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+        <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape </parameter> <type>text</type>)
         <returnvalue>text</returnvalue>
        </para>
        <para>
         Extracts the first substring matching <acronym>SQL</acronym> regular expression;
-        see <xref linkend="functions-similarto-regexp"/>.
+        see <xref linkend="functions-similarto-regexp"/>.  (Same as
+        <literal>substring(string text SIMILAR pattern text ESCAPE escape text)</literal>.)
        </para>
        <para>
         <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
@@ -5593,8 +5596,8 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
    </indexterm>
 
 <synopsis>
-<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
 </synopsis>
 
     <para>
@@ -5766,8 +5769,8 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
    </indexterm>
 
 <synopsis>
-<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
 </synopsis>
 
    <para>
@@ -5901,15 +5904,11 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
     regular expression pattern.  The function can be written according
     to standard SQL syntax:
 <synopsis>
-substring(<replaceable>string</replaceable> similar <replaceable>pattern</replaceable> escape <replaceable>escape-character</replaceable>)
-</synopsis>
-    or using the now obsolete SQL:1999 syntax:
-<synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable> for <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable> SIMILAR <replaceable>pattern</replaceable> ESCAPE <replaceable>escape</replaceable>)
 </synopsis>
-    or as a plain three-argument function:
+    It can also written as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
 </synopsis>
     As with <literal>SIMILAR TO</literal>, the
     specified pattern must match the entire data string, or else the
@@ -6115,9 +6114,9 @@ substring('foobar' similar '#"o_b#"%' escape '#')    <lineannotation>NULL</linea
     <para>
      The <function>substring</function> function with two parameters provides extraction of a
      substring that matches a POSIX regular expression pattern.
-     It has syntax:
+     The function can be written according to standard SQL syntax:
 <synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+substring(<replaceable>string</replaceable> FROM <replaceable>pattern</replaceable>)
 </synopsis>
      It can also written as a plain two-argument function:
 <synopsis>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 5ea9d786b6..544b549ae7 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
  IMMUTABLE PARALLEL SAFE STRICT COST 1
 RETURN rpad($1, $2, ' ');
 
-CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
  RETURNS text
  LANGUAGE sql
  IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87e3006fef..5267f06aec 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6450,7 +6450,7 @@
   prosrc => 'textregexsubstr' },
 { oid => '2074', descr => 'extract text matching SQL regular expression',
   proname => 'substring', prolang => 'sql', prorettype => 'text',
-  proargnames => '{string, pattern, escape_character}',
+  proargnames => '{string, pattern, escape}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },

---

From: jian he <[email-masked]>
Date: Tue, 1 Apr 2025 12:11:34 +0800

On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email-masked]> wrote:
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference.  Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries.  Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>

your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch

for example:

-       <para role="func_signature">
+       <para role="func_signature"><s>
         <function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
-        <returnvalue>text</returnvalue>
+        <returnvalue>text</returnvalue></s>

can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch.

because I tried
git am

On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email-masked]> wrote:
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference.  Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries.  Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>

your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch

for example:

-       <para role="func_signature">
+       <para role="func_signature"><s>
         <function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
-        <returnvalue>text</returnvalue>
+        <returnvalue>text</returnvalue></s>

can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch.

because I tried git am

On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email-masked]> wrote:
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference.  Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries.  Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>

can not build docs based on your v4-0001.

your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch

for example:
-       <para role="func_signature">
+       <para role="func_signature"><s>
         <function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
-        <returnvalue>text</returnvalue>
+        <returnvalue>text</returnvalue></s>

because I tried
git am v3-0001-add-argument-name-to-function-substring-and-subst.patch.
patch -p1 < v4-0002-v3-0002-delta.patch
Then there are several places that differ, it doesn't seem easy to
resolve the difference.

Can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch,
then I can review your delta patch.

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 21:59:38 -0700

> v3-0001-add-argument-name-to-function-substring-and-subst.patch
Sorry about that.  v5 attached.  Confirmed with diff the v3 and v5 0001 so
we should be good.

David J.
From 507064e643b54d2bebd7689acd3dde60d230e328 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 21:36:42 -0700
Subject: [PATCH 1/2] v3-0001 substring

---

index c642f1ea4e..a3569995f1 100644
 
+
+      <row>
 
+      <row>
 
     <para>

 

From 6ef3ff43f133dd6be423c4a567afda10f1c64988 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 21:54:44 -0700
Subject: [PATCH 2/2] v3 delta

---
 doc/src/sgml/func.sgml                   | 46 ++++++++++++------------
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  1 +
 3 files changed, 24 insertions(+), 25 deletions(-)

index a3569995f1..c9516966c1 100644
@@ -2850,19 +2851,18 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
 
@@ -3814,7 +3814,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        </para>
        <para>
         Extracts the first substring matching POSIX regular expression; see
-        <xref linkend="functions-posix-regexp"/>.
+        <xref linkend="functions-posix-regexp"/>.  (Same as
+        <literal>substring(string text FROM pattern text)</literal>.)
        </para>
        <para>
         <literal>substring('Thomas', '...$')</literal>
@@ -3824,12 +3825,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
 
       <row>
@@ -5593,8 +5595,8 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
    </indexterm>
 
 <synopsis>
 
     <para>
@@ -5766,8 +5768,8 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
    </indexterm>
 
 <synopsis>
 
    <para>
@@ -5901,15 +5903,11 @@ cast(-44 as bit(12))           <lineannotation>111111010100</lineannotation>
@@ -6115,9 +6113,9 @@ substring('foobar' similar '#"o_b#"%' escape '#')    <lineannotation>NULL</linea
 
index 87e3006fef..8bb2b0de89 100644
@@ -6451,6 +6451,7 @@
   proargnames => '{string, pattern, escape_character}',