add function argument name to substring and substr

Started by jian he12 months ago23 messages

jian.universality@gmail.com

12 months ago

hi.

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

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.

jian he

jian.universality@gmail.com

12 months ago

In reply to: jian he (#1)

1 attachment(s)

Re: add function argument name to substring and substr

hi.

I forgot to attach the patch.
here we are.

Attachments:

v1-0001-add-function-argument-name-to-function-substring-.patchtext/x-patch; charset=US-ASCII; name=v1-0001-add-function-argument-name-to-function-substring-.patchDownload

From 249a083627b0d8ad15c698601f7b7649be5262a1 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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

David G. Johnston

david.g.johnston@gmail.com

11 months ago

In reply to: jian he (#1)

Re: add function argument name to substring and substr

On Tue, Jan 21, 2025 at 11:21 PM jian he <jian.universality@gmail.com>
wrote:

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.

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.

vignesh C

vignesh21@gmail.com

10 months ago

In reply to: jian he (#2)

Re: add function argument name to substring and substr

On Mon, 27 Jan 2025 at 07:51, jian he <jian.universality@gmail.com> wrote:

hi.

I forgot to attach the patch.
here we are.

I noticed that David's comments from [1]/messages/by-id/CAKFQuwYD477A9Oy2PxRr_KY8t=k=jbOUK83ie0dS7fcpBsXS3Q@mail.gmail.com 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]: /messages/by-id/CAKFQuwYD477A9Oy2PxRr_KY8t=k=jbOUK83ie0dS7fcpBsXS3Q@mail.gmail.com

Regards,
Vignesh

jian he

jian.universality@gmail.com

10 months ago

In reply to: David G. Johnston (#3)

1 attachment(s)

Re: add function argument name to substring and substr

On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
<david.g.johnston@gmail.com> 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.

Attachments:

v2-0001-add-argument-name-to-function-substring-and-subst.patchtext/x-patch; charset=US-ASCII; name=v2-0001-add-argument-name-to-function-substring-and-subst.patchDownload

From f0b49ccd27d41ff1bd1619284656efcb1d3c3e0f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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
   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
(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>
 
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..544b549ae74 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 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 890822eaf79..1e023c6465b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,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',
@@ -3686,6 +3687,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',
@@ -3696,9 +3698,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',
@@ -4116,6 +4120,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',
@@ -4145,6 +4150,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',
@@ -6239,15 +6245,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',
@@ -6436,9 +6446,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}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

David G. Johnston

david.g.johnston@gmail.com

10 months ago

In reply to: jian he (#5)

Re: add function argument name to substring and substr

On Mon, Mar 17, 2025 at 8:20 PM jian he <jian.universality@gmail.com> wrote:

On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
<david.g.johnston@gmail.com> 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)

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

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.

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.

also add an example about using named natation call substring:

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.

jian he

jian.universality@gmail.com

10 months ago

In reply to: David G. Johnston (#6)

Re: add function argument name to substring and substr

On Wed, Mar 19, 2025 at 8:19 AM David G. Johnston
<david.g.johnston@gmail.com> 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.

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

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

David G. Johnston

david.g.johnston@gmail.com

10 months ago

In reply to: jian he (#7)

Re: add function argument name to substring and substr

On Tue, Mar 18, 2025 at 6:20 PM jian he <jian.universality@gmail.com> wrote:

On Wed, Mar 19, 2025 at 8:19 AM David G. Johnston
<david.g.johnston@gmail.com> 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.

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

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

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.

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)

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.

jian he

jian.universality@gmail.com

10 months ago

In reply to: David G. Johnston (#8)

1 attachment(s)

Re: add function argument name to substring and substr

hi.

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.

Attachments:

v3-0001-add-argument-name-to-function-substring-and-subst.patchtext/x-patch; charset=US-ASCII; name=v3-0001-add-argument-name-to-function-substring-and-subst.patchDownload

From 835dc9364f9f986445b77af8fa761e7577cbbdff Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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
   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
(8 rows)

\df substr
                                     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)

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 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>
-     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
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..5ea9d786b60 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 890822eaf79..e63a8279e16 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,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',
@@ -3686,6 +3687,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',
@@ -3696,9 +3698,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',
@@ -4116,6 +4120,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',
@@ -4145,6 +4150,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',
@@ -6239,15 +6245,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',
@@ -6436,9 +6446,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

#10

David G. Johnston

david.g.johnston@gmail.com

10 months ago

In reply to: jian he (#9)

2 attachment(s)

Re: add function argument name to substring and substr

On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universality@gmail.com> wrote:

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.

Attachments:

v4-0001-v3-0001-substring.patchtext/x-patch; charset=US-ASCII; name=v4-0001-v3-0001-substring.patchDownload

From b2f64615da9522427a2e2662b1d060ffed97088c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
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>
+       <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>
-     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
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e44..5ea9d786b6 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 0737eb73c9..87e3006fef 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,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',
@@ -3686,6 +3687,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',
@@ -3696,9 +3698,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',
@@ -4116,6 +4120,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',
@@ -4145,6 +4150,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',
@@ -6239,15 +6245,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',
@@ -6436,9 +6446,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

v4-0002-v3-0002-delta.patchtext/x-patch; charset=US-ASCII; name=v4-0002-v3-0002-delta.patchDownload

From 182f0b447d7860fe5db62863da0d7f582a0abfb9 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
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' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

#11

jian he

jian.universality@gmail.com

10 months ago

In reply to: David G. Johnston (#10)

Re: add function argument name to substring and substr

On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universality@gmail.com> wrote:

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.

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
<david.g.johnston@gmail.com> wrote:

On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universality@gmail.com> wrote:

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.

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
<david.g.johnston@gmail.com> wrote:

On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universality@gmail.com> wrote:

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.

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.

#12

David G. Johnston

david.g.johnston@gmail.com

10 months ago

In reply to: jian he (#11)

2 attachment(s)

Re: add function argument name to substring and substr

On Mon, Mar 31, 2025 at 9:12 PM jian he <jian.universality@gmail.com> wrote:

On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universality@gmail.com>

wrote:

new patch attached.

I've done v4 with a delta patch.

your v4-0001-v3-0001-substring.patch is not the same as my
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.

Attachments:

v5-0001-v3-0001-substring.patchtext/x-patch; charset=US-ASCII; name=v5-0001-v3-0001-substring.patchDownload

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

---
 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 c642f1ea4e..a3569995f1 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>
-     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
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e44..5ea9d786b6 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 0737eb73c9..87e3006fef 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,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',
@@ -3686,6 +3687,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',
@@ -3696,9 +3698,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',
@@ -4116,6 +4120,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',
@@ -4145,6 +4150,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',
@@ -6239,15 +6245,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',
@@ -6436,9 +6446,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

v5-0002-v3-delta.patchtext/x-patch; charset=US-ASCII; name=v5-0002-v3-delta.patchDownload

From 6ef3ff43f133dd6be423c4a567afda10f1c64988 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <David.G.Johnston@Gmail.com>
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(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a3569995f1..c9516966c1 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,19 +2851,18 @@ 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">
-        <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>
-       </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>
+       <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>
 
@@ -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>
        <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 +5595,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 +5768,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 +5903,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 +6113,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..8bb2b0de89 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6451,6 +6451,7 @@
 { 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' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

#13

Marcos Pegoraro

marcos@f10.com.br

10 months ago

In reply to: David G. Johnston (#12)

Re: add function argument name to substring and substr

Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <
david.g.johnston@gmail.com> escreveu:

Wouldn't it be good to add the use of parentheses using posix ? It's useful
and rarely documented
<literal>substring('Thomas', '...$')</literal>
+ <literal>substring('Email: johnjohn@mymail.com, Name: John' from
'@(.*), Name')</literal>

regards
Marcos

#14

Daniel Gustafsson

daniel@yesql.se

10 months ago

In reply to: Marcos Pegoraro (#13)

Re: add function argument name to substring and substr

On 1 Apr 2025, at 15:14, Marcos Pegoraro <marcos@f10.com.br> wrote:

Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:

Wouldn't it be good to add the use of parentheses using posix ? It's useful and rarely documented
<literal>substring('Thomas', '...$')</literal>
+ <literal>substring('Email: johnjohn@mymail.com, Name: John' from '@(.*), Name')</literal>

While not commenting on the usefulness of the suggestion; any usage of email
addresses in the documentation should use @example.{com|org}.

--
Daniel Gustafsson

#15

David G. Johnston

david.g.johnston@gmail.com

10 months ago

In reply to: Marcos Pegoraro (#13)

Re: add function argument name to substring and substr

On Tue, Apr 1, 2025 at 6:15 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <
david.g.johnston@gmail.com> escreveu:

Wouldn't it be good to add the use of parentheses using posix ? It's
useful and rarely documented
<literal>substring('Thomas', '...$')</literal>
+ <literal>substring('Email: johnjohn@mymail.com, Name: John' from
'@(.*), Name')</literal>

Agreed. A second example using () would be good here.

Was pondering explaining the "no parentheses" case here; but for someone
familiar with PREs the behavior is obvious and everyone else has the link
needed to learn what is happening.

David J.

#16

jian he

jian.universality@gmail.com

6 months ago

In reply to: David G. Johnston (#15)

2 attachment(s)

Re: add function argument name to substring and substr

On Tue, Apr 1, 2025 at 10:41 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Apr 1, 2025 at 6:15 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <david.g.johnston@gmail.com> escreveu:

Wouldn't it be good to add the use of parentheses using posix ? It's useful and rarely documented
<literal>substring('Thomas', '...$')</literal>
+ <literal>substring('Email: johnjohn@mymail.com, Name: John' from '@(.*), Name')</literal>

Agreed. A second example using () would be good here.

actually,
section (9.7.3.)
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
already have example, like:
substring('foobar' from 'o(.)b')

new patch attached, split substr, substring to make review more easier.
v6-0001: add function argument name to function substr
v6-0002: add function argument name to function substring

v6-0002 incorporated some of the changes in v5-0002-v3-delta.patch.
some of the changes in v5-0002-v3-delta.patch are not related to this thread,
so I didn't incorporate them, right now.

Attachments:

v6-0001-add-function-argument-name-to-substr.patchtext/x-patch; charset=US-ASCII; name=v6-0001-add-function-argument-name-to-substr.patchDownload

From 736ff000500d8d87499eaef0fb7331960173c4e8 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 21 Jul 2025 11:58:37 +0800
Subject: [PATCH v6 1/2] add function argument name to substr.

HEAD
\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

with patch
 \df substr
                                     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

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 src/include/catalog/pg_proc.dat | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1fc19146f46..2f29f3757a5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3706,6 +3706,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',
@@ -3724,6 +3725,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',
@@ -6286,9 +6288,11 @@
   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',
-- 
2.34.1

v6-0002-add-function-argument-name-to-function-substring.patchtext/x-patch; charset=US-ASCII; name=v6-0002-add-function-argument-name-to-function-substring.patchDownload

From 8f36831bed5e6182f4034fa4ae4bd16fe5272460 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 21 Jul 2025 14:20:34 +0800
Subject: [PATCH v6 2/2] add function argument name to function substring

HEAD
\df substr
                             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
\df substring
  Schema   |   Name    | Result data type |               Argument data types                | Type
------------+-----------+------------------+--------------------------------------------------+------
 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 | bit              | string bit, start 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
(8 rows)

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func.sgml                   | 110 +++++++++++++++++++++--
 src/backend/catalog/system_functions.sql |   2 +-
 src/include/catalog/pg_proc.dat          |   8 ++
 3 files changed, 113 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f5a0e0954a1..a26dbf6fe0a 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3806,6 +3806,56 @@ 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</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>
@@ -4859,6 +4909,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>
@@ -5401,6 +5472,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>
@@ -5413,6 +5504,7 @@ cast(-1234 as bytea)           <lineannotation>\xfffffb2e</lineannotation>
         Extracts the substring of <parameter>bits</parameter> starting at
         the <parameter>start</parameter>'th bit if that is specified,
         and stopping after <parameter>count</parameter> bits if that is
+        and stopping after <parameter>count</parameter> bits if that is
         specified.  Provide at least one of <parameter>start</parameter>
         and <parameter>count</parameter>.
        </para>
@@ -5864,7 +5956,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
@@ -6068,11 +6160,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 <acronym>POSIX</acronym> regular expression pattern.
+     The function can be written according to standard <acronym>SQL</acronym> 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
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..544b549ae74 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 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 2f29f3757a5..6b8a918cdd6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3736,9 +3736,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',
@@ -4156,6 +4158,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',
@@ -4185,6 +4188,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{string, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6282,9 +6286,11 @@
   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',
@@ -6484,9 +6490,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}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

#17

jian he

jian.universality@gmail.com

3 months ago

In reply to: jian he (#16)

2 attachment(s)

Re: add function argument name to substring and substr

On Mon, Jul 21, 2025 at 2:31 PM jian he <jian.universality@gmail.com> wrote:

actually,
section (9.7.3.)
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
already have example, like:
substring('foobar' from 'o(.)b')

new patch attached, split substr, substring to make review more easier.
v6-0001: add function argument name to function substr
v6-0002: add function argument name to function substring

v6-0002 incorporated some of the changes in v5-0002-v3-delta.patch.
some of the changes in v5-0002-v3-delta.patch are not related to this thread,
so I didn't incorporate them, right now.

hi.

rebased.

Attachments:

v7-0002-add-function-argument-name-to-function-substring.patchtext/x-patch; charset=US-ASCII; name=v7-0002-add-function-argument-name-to-function-substring.patchDownload

From 5afc6f3ce1ab154576002c1dfe82a2dd22d80964 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 13 Oct 2025 21:04:40 +0800
Subject: [PATCH v7 2/2] add function argument name to function substring

HEAD
\df substr
                             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 applied

\df substring
  Schema   |   Name    | Result data type |               Argument data types                | Type
------------+-----------+------------------+--------------------------------------------------+------
 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 | bit              | string bit, start 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
(8 rows)

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 20 ++++++++++
 doc/src/sgml/func/func-bitstring.sgml    | 20 ++++++++++
 doc/src/sgml/func/func-matching.sgml     | 18 ++++++---
 doc/src/sgml/func/func-string.sgml       | 51 ++++++++++++++++++++++++
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  8 ++++
 6 files changed, 112 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index dd7037811af..4786a096dd5 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -216,6 +216,26 @@
        </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>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/func/func-bitstring.sgml b/doc/src/sgml/func/func-bitstring.sgml
index f03dd63afcc..c1de0963ec0 100644
--- a/doc/src/sgml/func/func-bitstring.sgml
+++ b/doc/src/sgml/func/func-bitstring.sgml
@@ -274,6 +274,26 @@
        </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>
diff --git a/doc/src/sgml/func/func-matching.sgml b/doc/src/sgml/func/func-matching.sgml
index ebe0b22c8f6..1454db0378d 100644
--- a/doc/src/sgml/func/func-matching.sgml
+++ b/doc/src/sgml/func/func-matching.sgml
@@ -377,7 +377,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
@@ -581,11 +581,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 <acronym>POSIX</acronym> regular expression pattern.
+     The function can be written according to standard <acronym>SQL</acronym> 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
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 01cc94c234e..35d657eb418 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -1412,6 +1412,57 @@
        </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">
+        <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"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+        <returnvalue>oma</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> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching <acronym>POSIX</acronym> 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">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..d5f8bbd953f 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 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 b6d23315e46..432a75b41b4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3748,9 +3748,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',
@@ -4168,6 +4170,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',
@@ -4197,6 +4200,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{string, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6302,9 +6306,11 @@
   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',
@@ -6504,9 +6510,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}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

v7-0001-add-function-argument-name-to-substr.patchtext/x-patch; charset=US-ASCII; name=v7-0001-add-function-argument-name-to-substr.patchDownload

From e18bc9123a65bd9c30fb8541ca464a407351fcce Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 21 Jul 2025 11:58:37 +0800
Subject: [PATCH v7 1/2] add function argument name to substr.

HEAD
\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

with patch
 \df substr
                                     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

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 src/include/catalog/pg_proc.dat | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b51d2b17379..b6d23315e46 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3718,6 +3718,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',
@@ -3736,6 +3737,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',
@@ -6306,9 +6308,11 @@
   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',
-- 
2.34.1

#18

Andrew Dunstan

andrew@dunslane.net

3 months ago

In reply to: jian he (#17)

Re: add function argument name to substring and substr

On 2025-10-13 Mo 10:22 AM, jian he wrote:

On Mon, Jul 21, 2025 at 2:31 PM jian he <jian.universality@gmail.com> wrote:

actually,
section (9.7.3.)
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP
already have example, like:
substring('foobar' from 'o(.)b')

new patch attached, split substr, substring to make review more easier.
v6-0001: add function argument name to function substr
v6-0002: add function argument name to function substring

v6-0002 incorporated some of the changes in v5-0002-v3-delta.patch.
some of the changes in v5-0002-v3-delta.patch are not related to this thread,
so I didn't incorporate them, right now.

hi.

rebased.

I'm late to the party on this, but I wonder if it wouldn't be better to
use a type-neutral parameter name here, like "source", which could cover
all these cases, instead of "string", "bytes", etc.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#19

jian he

jian.universality@gmail.com

3 months ago

In reply to: Andrew Dunstan (#18)

Re: add function argument name to substring and substr

On Mon, Oct 13, 2025 at 10:58 PM Andrew Dunstan <andrew@dunslane.net> wrote:

I'm late to the party on this, but I wonder if it wouldn't be better to
use a type-neutral parameter name here, like "source", which could cover
all these cases, instead of "string", "bytes", etc.

\df *regexp*

psql output generally begins with "string text, pattern text"
that's because of commit 580f872.

in that commit, discussion,
/messages/by-id/CACJufxG3NFKKsh6x4fRLv8h3V-HvN4W5dA=zNKMxsNcDwOKang@mail.gmail.com,
in that first patch, i did tried to use "source" in some cases, then I found out
if you changed to "source" then all the documentation also needs to be
changed. so
I stuck to "string" in commit 580f872.

Here, for the functions substr and substring, I followed the precedent set by
the regex function using the term "string".

sure, we can change it to "source" if that's what people want.

#20

Tom Lane

tgl@sss.pgh.pa.us

3 months ago

In reply to: Andrew Dunstan (#18)

Re: add function argument name to substring and substr

Andrew Dunstan <andrew@dunslane.net> writes:

I'm late to the party on this, but I wonder if it wouldn't be better to
use a type-neutral parameter name here, like "source", which could cover
all these cases, instead of "string", "bytes", etc.

+1 for that idea. As Jian notes, we'd need to make the docs match,
but I think that this would be an improvement across the board.
Parameter names like "string" don't convey much information.

regards, tom lane

#21

jian he

jian.universality@gmail.com

3 months ago

In reply to: Tom Lane (#20)

2 attachment(s)

Re: add function argument name to substring and substr

On Tue, Oct 14, 2025 at 12:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

I'm late to the party on this, but I wonder if it wouldn't be better to
use a type-neutral parameter name here, like "source", which could cover
all these cases, instead of "string", "bytes", etc.

+1 for that idea. As Jian notes, we'd need to make the docs match,
but I think that this would be an improvement across the board.
Parameter names like "string" don't convey much information.

please check the attached v8.

For function substr, the doc change is not that a big deal.

For function substring, in doc/src/sgml/func/func-matching.sgml, we need
change some of the
<replaceable>string</replaceable>
to
<replaceable>source</replaceable>.

we also need to change many
<parameter>string</parameter>
to
<parameter>source</parameter>.
That's why v8-0002 is big.

Attachments:

v8-0002-add-function-argument-name-to-function-substring.patchtext/x-patch; charset=US-ASCII; name=v8-0002-add-function-argument-name-to-function-substring.patchDownload

From d1b3d6c7eb3ebb4456ea2f6bb2223fd6c535fedd Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 14 Oct 2025 15:42:57 +0800
Subject: [PATCH v8 2/2] add function argument name to function substring

with patch applied
\df substring
                                       List of functions
   Schema   |   Name    | Result data type |            Argument data types             | Type
------------+-----------+------------------+--------------------------------------------+------
 pg_catalog | substring | bit              | source bit, start integer                  | func
 pg_catalog | substring | bit              | source bit, start integer, count integer   | func
 pg_catalog | substring | bytea            | source bytea, start integer                | func
 pg_catalog | substring | bytea            | source bytea, start integer, count integer | func
 pg_catalog | substring | text             | source text, pattern text                  | func
 pg_catalog | substring | text             | source text, pattern text, escape text     | func
 pg_catalog | substring | text             | source text, start integer                 | func
 pg_catalog | substring | text             | source text, start integer, count integer  | func
(8 rows)

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 26 ++++++++--
 doc/src/sgml/func/func-bitstring.sgml    | 24 ++++++++-
 doc/src/sgml/func/func-matching.sgml     | 32 +++++++-----
 doc/src/sgml/func/func-string.sgml       | 63 +++++++++++++++++++++---
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  8 +++
 6 files changed, 130 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 294d1d97233..98f12c05bfa 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -200,11 +200,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>bytea</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>bytes</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte if that is specified,
         and stopping after <parameter>count</parameter> bytes if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -216,6 +216,26 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th byte,
+        and stopping after <parameter>count</parameter> bytes if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>'\x1234567890'::bytea, start=>3, count=>2)</literal>
+        <returnvalue>\x5678</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -579,7 +599,7 @@
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
-        as <literal>substring(<parameter>bytes</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
diff --git a/doc/src/sgml/func/func-bitstring.sgml b/doc/src/sgml/func/func-bitstring.sgml
index f03dd63afcc..9f5dcb6ff2a 100644
--- a/doc/src/sgml/func/func-bitstring.sgml
+++ b/doc/src/sgml/func/func-bitstring.sgml
@@ -279,11 +279,31 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bits</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
+        the <parameter>start</parameter>'th bit,
+        and stopping after <parameter>count</parameter> bits if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>B'110010111111', start=>3, count=>2)</literal>
+        <returnvalue>00</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>source</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <returnvalue>bit</returnvalue>
+       </para>
+       <para>
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th bit if that is specified,
         and stopping after <parameter>count</parameter> bits if that is
         specified.  Provide at least one of <parameter>start</parameter>
diff --git a/doc/src/sgml/func/func-matching.sgml b/doc/src/sgml/func/func-matching.sgml
index ebe0b22c8f6..ea4c2546f85 100644
--- a/doc/src/sgml/func/func-matching.sgml
+++ b/doc/src/sgml/func/func-matching.sgml
@@ -234,13 +234,13 @@
    </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>source</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>source</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
 </synopsis>
 
    <para>
     The <function>SIMILAR TO</function> operator returns true or
-    false depending on whether its pattern matches the given string.
+    false depending on whether its pattern matches the given string (the <replaceable>source</replaceable>).
     It is similar to <function>LIKE</function>, except that it
     interprets the pattern using the SQL standard's definition of a
     regular expression.  SQL regular expressions are a curious cross
@@ -369,15 +369,15 @@
     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>)
+substring(<replaceable>source</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>source</replaceable> from <replaceable>pattern</replaceable> for <replaceable>escape-character</replaceable>)
 </synopsis>
     or as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>source</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
@@ -581,11 +581,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 <acronym>POSIX</acronym> regular expression pattern.
+     The function can be written according to standard <acronym>SQL</acronym> syntax:
+<synopsis>
+substring(<replaceable>source</replaceable> FROM <replaceable>pattern</replaceable>)
+</synopsis>
+     It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>source</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
@@ -600,8 +606,8 @@ 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(source=>'foobar', pattern=>'o.b')     <lineannotation>oob</lineannotation>
+substring('foobar' from 'o(.)b')                <lineannotation>o</lineannotation>
 </programlisting>
    </para>
 
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index f7d03eda2bc..39872181b15 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -400,11 +400,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>string</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character if that is specified,
         and stopping after <parameter>count</parameter> characters if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -426,7 +426,7 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
@@ -441,11 +441,11 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <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> )
+        <function>substring</function> ( <parameter>source</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">
-        <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> )
+        <function>substring</function> ( <parameter>source</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>
        </para>
        <para>
@@ -1398,7 +1398,7 @@
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
-        as <literal>substring(<parameter>string</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
@@ -1412,6 +1412,57 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th character,
+        and stopping after <parameter>count</parameter> characters if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>'Thomas', start=>2, count=>3)</literal>
+        <returnvalue>hom</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>source</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"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+        <returnvalue>oma</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching <acronym>POSIX</acronym> 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">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..f0850f9ef64 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"(source 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 1cbb94f22ea..c272240d422 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3748,9 +3748,11 @@
 
 { oid => '936', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '937', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '2087',
   descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4168,6 +4170,7 @@
   prosrc => 'bitcat' },
 { oid => '1680', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bitsubstr' },
 { oid => '1681', descr => 'bitstring length',
   proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4197,6 +4200,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{source, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6302,9 +6306,11 @@
   prosrc => 'byteacat' },
 { oid => '2012', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea',
+  proargnames => '{source, start, count}',
   proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
 { oid => '2013', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
@@ -6504,9 +6510,11 @@
 
 { oid => '2073', descr => 'extract text matching regular expression',
   proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{source, pattern}',
   prosrc => 'textregexsubstr' },
 { oid => '2074', descr => 'extract text matching SQL regular expression',
   proname => 'substring', prolang => 'sql', prorettype => 'text',
+  proargnames => '{source, pattern, escape}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

v8-0001-add-function-argument-name-to-function-substr.patchtext/x-patch; charset=US-ASCII; name=v8-0001-add-function-argument-name-to-function-substr.patchDownload

From 0395bf1159c682c428716c801790b389f073cce5 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 14 Oct 2025 15:16:04 +0800
Subject: [PATCH v8 1/2] add function argument name to function substr

with patch applied

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

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 4 ++--
 doc/src/sgml/func/func-string.sgml       | 4 ++--
 src/include/catalog/pg_proc.dat          | 4 ++++
 3 files changed, 8 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index dd7037811af..294d1d97233 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -571,11 +571,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 01cc94c234e..f7d03eda2bc 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -1390,11 +1390,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b51d2b17379..1cbb94f22ea 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3718,6 +3718,7 @@
   prosrc => 'rtrim' },
 { oid => '877', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '878', descr => 'map a set of characters appearing in string',
   proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3736,6 +3737,7 @@
   prosrc => 'rtrim1' },
 { oid => '883', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '884', descr => 'trim selected characters from both ends of string',
   proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -6306,9 +6308,11 @@
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bytea_substr' },
 { oid => '2086', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2014', descr => 'position of substring',
   proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
-- 
2.34.1

#22

jian he

jian.universality@gmail.com

2 months ago

In reply to: Tom Lane (#20)

2 attachment(s)

Re: add function argument name to substring and substr

On Tue, Oct 14, 2025 at 12:11 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andrew Dunstan <andrew@dunslane.net> writes:

I'm late to the party on this, but I wonder if it wouldn't be better to
use a type-neutral parameter name here, like "source", which could cover
all these cases, instead of "string", "bytes", etc.

+1 for that idea. As Jian notes, we'd need to make the docs match,
but I think that this would be an improvement across the board.
Parameter names like "string" don't convey much information.

hi.

regexp_count(string text, pattern text, start integer, flags text)
regexp_instr(string text, pattern text, start integer, "N" integer,
endoption integer, flags text, subexpr integer)
regexp_like(string text, pattern text, flags text)
regexp_match(string text, pattern text, flags text)
regexp_matches (string text, pattern text, flags text)
regexp_replace(string text, pattern text, replacement text, start
integer, "N" integer, flags text)
regexp_split_to_table(string text, pattern text, flags text)
regexp_substr(string text, pattern text, start integer, "N" integer,
flags text, subexpr integer)

For the above regex function, other function argument names look good
to me except the "string".
Do we also need to rename these function's first argument from "string" to
"source"? This would be a compatibility break, but if we do it now, it would
only impact one release.

-------------------
rebased due to conflict by commit:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=49d43faa835f3c6817be9fc0b98bec0d661c2587

Attachments:

v9-0001-add-function-argument-name-to-function-substr.patchtext/x-patch; charset=US-ASCII; name=v9-0001-add-function-argument-name-to-function-substr.patchDownload

From 69f11ebcdb76ef2c06b97fa4fc1bcd06edc55a6c Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 14 Oct 2025 15:16:04 +0800
Subject: [PATCH v9 1/2] add function argument name to function substr

with patch applied

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

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 4 ++--
 doc/src/sgml/func/func-string.sgml       | 4 ++--
 src/include/catalog/pg_proc.dat          | 4 ++++
 3 files changed, 8 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index b256381e01f..58051595126 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -571,11 +571,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 7ad1436e5f8..3325ade065a 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -1390,11 +1390,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..aa92f72b299 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3718,6 +3718,7 @@
   prosrc => 'rtrim' },
 { oid => '877', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '878', descr => 'map a set of characters appearing in string',
   proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3736,6 +3737,7 @@
   prosrc => 'rtrim1' },
 { oid => '883', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '884', descr => 'trim selected characters from both ends of string',
   proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -6306,9 +6308,11 @@
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bytea_substr' },
 { oid => '2086', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2014', descr => 'position of substring',
   proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
-- 
2.34.1

v9-0002-add-function-argument-name-to-function-substring.patchtext/x-patch; charset=US-ASCII; name=v9-0002-add-function-argument-name-to-function-substring.patchDownload

From b0b1832adcf0a28bfde6525463a7abc263f4051a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 10 Nov 2025 10:17:55 +0800
Subject: [PATCH v9 2/2] add function argument name to function substring

with patch applied
\df substring
                                       List of functions
   Schema   |   Name    | Result data type |            Argument data types             | Type
------------+-----------+------------------+--------------------------------------------+------
 pg_catalog | substring | bit              | source bit, start integer                  | func
 pg_catalog | substring | bit              | source bit, start integer, count integer   | func
 pg_catalog | substring | bytea            | source bytea, start integer                | func
 pg_catalog | substring | bytea            | source bytea, start integer, count integer | func
 pg_catalog | substring | text             | source text, pattern text                  | func
 pg_catalog | substring | text             | source text, pattern text, escape text     | func
 pg_catalog | substring | text             | source text, start integer                 | func
 pg_catalog | substring | text             | source text, start integer, count integer  | func
(8 rows)

discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 26 ++++++++--
 doc/src/sgml/func/func-bitstring.sgml    | 24 ++++++++-
 doc/src/sgml/func/func-matching.sgml     | 28 ++++++-----
 doc/src/sgml/func/func-string.sgml       | 62 +++++++++++++++++++++---
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  8 +++
 6 files changed, 127 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 58051595126..24eb1ecf6e4 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -200,11 +200,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>bytea</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>bytes</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte if that is specified,
         and stopping after <parameter>count</parameter> bytes if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -216,6 +216,26 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th byte,
+        and stopping after <parameter>count</parameter> bytes if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>'\x1234567890'::bytea, start=>3, count=>2)</literal>
+        <returnvalue>\x5678</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -579,7 +599,7 @@
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
-        as <literal>substring(<parameter>bytes</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
diff --git a/doc/src/sgml/func/func-bitstring.sgml b/doc/src/sgml/func/func-bitstring.sgml
index 3f59de464a4..2dd828df4a4 100644
--- a/doc/src/sgml/func/func-bitstring.sgml
+++ b/doc/src/sgml/func/func-bitstring.sgml
@@ -279,11 +279,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bits</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>bit</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>bits</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th bit if that is specified,
         and stopping after <parameter>count</parameter> bits if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -295,6 +295,26 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>         
+        </indexterm>
+        <function>substring</function> ( <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th bit,
+        and stopping after <parameter>count</parameter> bits if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>B'110010111111', start=>3, count=>2)</literal>
+        <returnvalue>00</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/func/func-matching.sgml b/doc/src/sgml/func/func-matching.sgml
index 91a0b7ca0de..228684397d0 100644
--- a/doc/src/sgml/func/func-matching.sgml
+++ b/doc/src/sgml/func/func-matching.sgml
@@ -234,13 +234,13 @@
    </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>source</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>source</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
 </synopsis>
 
    <para>
     The <function>SIMILAR TO</function> operator returns true or
-    false depending on whether its pattern matches the given string.
+    false depending on whether its pattern matches the given string (the <replaceable>source</replaceable>).
     It is similar to <function>LIKE</function>, except that it
     interprets the pattern using the SQL standard's definition of a
     regular expression.  SQL regular expressions are a curious cross
@@ -369,15 +369,15 @@
     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>)
+substring(<replaceable>source</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>source</replaceable> FROM <replaceable>pattern</replaceable> FOR <replaceable>escape-character</replaceable>)
 </synopsis>
     or as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>source</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
@@ -581,11 +581,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 <acronym>POSIX</acronym> regular expression pattern.
+     The function can be written according to standard <acronym>SQL</acronym> syntax:
+<synopsis>
+substring(<replaceable>source</replaceable> FROM <replaceable>pattern</replaceable>)
+</synopsis>
+     It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>source</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
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 3325ade065a..e87bbb0dfe5 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -400,11 +400,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>string</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character if that is specified,
         and stopping after <parameter>count</parameter> characters if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -426,7 +426,7 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
@@ -441,11 +441,11 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <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> )
+        <function>substring</function> ( <parameter>source</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">
-        <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> )
+        <function>substring</function> ( <parameter>source</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>
        </para>
        <para>
@@ -1398,7 +1398,7 @@
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
-        as <literal>substring(<parameter>string</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
@@ -1412,6 +1412,56 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> ( <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th character,
+        and stopping after <parameter>count</parameter> characters if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>'Thomas', start=>2, count=>3)</literal>
+        <returnvalue>hom</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>source</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"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+        <returnvalue>oma</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching <acronym>POSIX</acronym> 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">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..f0850f9ef64 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"(source 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 aa92f72b299..19700ebd9e6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3748,9 +3748,11 @@
 
 { oid => '936', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '937', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '2087',
   descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4168,6 +4170,7 @@
   prosrc => 'bitcat' },
 { oid => '1680', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bitsubstr' },
 { oid => '1681', descr => 'bitstring length',
   proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4197,6 +4200,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{source, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6302,9 +6306,11 @@
   prosrc => 'byteacat' },
 { oid => '2012', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea',
+  proargnames => '{source, start, count}',
   proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
 { oid => '2013', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
@@ -6504,9 +6510,11 @@
 
 { oid => '2073', descr => 'extract text matching regular expression',
   proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{source, pattern}',
   prosrc => 'textregexsubstr' },
 { oid => '2074', descr => 'extract text matching SQL regular expression',
   proname => 'substring', prolang => 'sql', prorettype => 'text',
+  proargnames => '{source, pattern, escape}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1

#23

jian he

jian.universality@gmail.com

12 days ago

In reply to: jian he (#22)

2 attachment(s)

Re: add function argument name to substring and substr

hi.

rebased, and rechecked it again.

seems no changes to the citext extension are required, since the citext data
type does not define specialized substring/substr function.

--
jian
https://www.enterprisedb.com

Attachments:

v10-0001-Add-argument-names-to-the-substr-functions.patchtext/x-patch; charset=US-ASCII; name=v10-0001-Add-argument-names-to-the-substr-functions.patchDownload

From d30592f190860cc68d08f956ac6853aa2c60e1bf Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 31 Dec 2025 15:26:31 +0800
Subject: [PATCH v10 1/2] Add argument names to the substr functions

This change allows substr function to be called using named-argument notation,
which can be helpful for readability, particularly for the ones with many
arguments.

No changes to the citext extension are required, since the citext data type does
not define a specialized substr function.

commitfest: https://commitfest.postgresql.org/patch/5524
Discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 4 ++--
 doc/src/sgml/func/func-string.sgml       | 4 ++--
 src/include/catalog/pg_proc.dat          | 4 ++++
 3 files changed, 8 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index b256381e01f..58051595126 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -571,11 +571,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 7ad1436e5f8..3325ade065a 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -1390,11 +1390,11 @@
         <indexterm>
          <primary>substr</primary>
         </indexterm>
-        <function>substr</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substr</function> ( <parameter>source</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
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 60f7ce502f6..6c6f31f7043 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3718,6 +3718,7 @@
   prosrc => 'rtrim' },
 { oid => '877', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '878', descr => 'map a set of characters appearing in string',
   proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3736,6 +3737,7 @@
   prosrc => 'rtrim1' },
 { oid => '883', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '884', descr => 'trim selected characters from both ends of string',
   proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -6306,9 +6308,11 @@
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bytea_substr' },
 { oid => '2086', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2014', descr => 'position of substring',
   proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
-- 
2.34.1

v10-0002-Add-argument-names-to-the-substring-functions.patchtext/x-patch; charset=US-ASCII; name=v10-0002-Add-argument-names-to-the-substring-functions.patchDownload

From 8fe7182867f833af9bdb2704a01ec358633fb5fd Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 31 Dec 2025 15:24:54 +0800
Subject: [PATCH v10 2/2] Add argument names to the substring functions

This change allows substring function to be called using named-argument
notation, which can be helpful for readability, particularly for the ones with
many arguments.

No changes to the citext extension are required, since the citext data type does
not define a specialized substring function.

commitfest: https://commitfest.postgresql.org/patch/5524
Discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
 doc/src/sgml/func/func-binarystring.sgml | 29 ++++++++--
 doc/src/sgml/func/func-bitstring.sgml    | 27 +++++++++-
 doc/src/sgml/func/func-matching.sgml     | 28 ++++++----
 doc/src/sgml/func/func-string.sgml       | 69 +++++++++++++++++++++---
 src/backend/catalog/system_functions.sql |  2 +-
 src/include/catalog/pg_proc.dat          |  8 +++
 6 files changed, 140 insertions(+), 23 deletions(-)

diff --git a/doc/src/sgml/func/func-binarystring.sgml b/doc/src/sgml/func/func-binarystring.sgml
index 58051595126..6f3b6c40a62 100644
--- a/doc/src/sgml/func/func-binarystring.sgml
+++ b/doc/src/sgml/func/func-binarystring.sgml
@@ -200,11 +200,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>bytea</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>bytea</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>bytes</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th byte if that is specified,
         and stopping after <parameter>count</parameter> bytes if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -216,6 +216,29 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> (
+          <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th byte,
+        and stopping after <parameter>count</parameter> bytes if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>'\x1234567890'::bytea, start=>3, count=>2)</literal>
+        <returnvalue>\x5678</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -579,7 +602,7 @@
         the <parameter>start</parameter>'th byte,
         and extending for <parameter>count</parameter> bytes if that is
         specified.  (Same
-        as <literal>substring(<parameter>bytes</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
diff --git a/doc/src/sgml/func/func-bitstring.sgml b/doc/src/sgml/func/func-bitstring.sgml
index 3f59de464a4..2c85989228c 100644
--- a/doc/src/sgml/func/func-bitstring.sgml
+++ b/doc/src/sgml/func/func-bitstring.sgml
@@ -279,11 +279,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>bits</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>bit</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>bit</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>bits</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th bit if that is specified,
         and stopping after <parameter>count</parameter> bits if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -295,6 +295,29 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> (
+          <parameter>source</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>source</parameter> starting at
+        the <parameter>start</parameter>'th bit,
+        and stopping after <parameter>count</parameter> bits if that is
+        specified.
+       </para>
+       <para>
+        <literal>substring(source=>B'110010111111', start=>3, count=>2)</literal>
+        <returnvalue>00</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/doc/src/sgml/func/func-matching.sgml b/doc/src/sgml/func/func-matching.sgml
index f466860ddb0..fb574bb83cb 100644
--- a/doc/src/sgml/func/func-matching.sgml
+++ b/doc/src/sgml/func/func-matching.sgml
@@ -234,13 +234,13 @@
    </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>source</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>source</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
 </synopsis>
 
    <para>
     The <function>SIMILAR TO</function> operator returns true or
-    false depending on whether its pattern matches the given string.
+    false depending on whether its pattern matches the given string (the <replaceable>source</replaceable>).
     It is similar to <function>LIKE</function>, except that it
     interprets the pattern using the SQL standard's definition of a
     regular expression.  SQL regular expressions are a curious cross
@@ -369,15 +369,15 @@
     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>)
+substring(<replaceable>source</replaceable> SIMILAR <replaceable>pattern</replaceable> ESCAPE <replaceable>escape</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>source</replaceable> FROM <replaceable>pattern</replaceable> FOR <replaceable>escape</replaceable>)
 </synopsis>
     or as a plain three-argument function:
 <synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>source</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
@@ -581,11 +581,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 <acronym>POSIX</acronym> regular expression pattern.
+     The function can be written according to standard <acronym>SQL</acronym> syntax:
+<synopsis>
+substring(<replaceable>source</replaceable> FROM <replaceable>pattern</replaceable>)
+</synopsis>
+     It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>source</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
diff --git a/doc/src/sgml/func/func-string.sgml b/doc/src/sgml/func/func-string.sgml
index 3325ade065a..41035c1e8af 100644
--- a/doc/src/sgml/func/func-string.sgml
+++ b/doc/src/sgml/func/func-string.sgml
@@ -400,11 +400,11 @@
         <indexterm>
          <primary>substring</primary>
         </indexterm>
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <optional> <literal>FROM</literal> <parameter>start</parameter> <type>integer</type> </optional> <optional> <literal>FOR</literal> <parameter>count</parameter> <type>integer</type> </optional> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
-        Extracts the substring of <parameter>string</parameter> starting at
+        Extracts the substring of <parameter>source</parameter> starting at
         the <parameter>start</parameter>'th character if that is specified,
         and stopping after <parameter>count</parameter> characters if that is
         specified.  Provide at least one of <parameter>start</parameter>
@@ -426,7 +426,7 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
+        <function>substring</function> ( <parameter>source</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> )
         <returnvalue>text</returnvalue>
        </para>
        <para>
@@ -441,11 +441,11 @@
 
       <row>
        <entry role="func_table_entry"><para role="func_signature">
-        <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> )
+        <function>substring</function> ( <parameter>source</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">
-        <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> )
+        <function>substring</function> ( <parameter>source</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>
        </para>
        <para>
@@ -1398,7 +1398,7 @@
         the <parameter>start</parameter>'th character,
         and extending for <parameter>count</parameter> characters if that is
         specified.  (Same
-        as <literal>substring(<parameter>string</parameter>
+        as <literal>substring(<parameter>source</parameter>
         from <parameter>start</parameter>
         for <parameter>count</parameter>)</literal>.)
        </para>
@@ -1412,6 +1412,63 @@
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>substring</primary>
+        </indexterm>
+        <function>substring</function> (
+          <parameter>source</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>source</parameter> starting at the
+        <parameter>start</parameter>'th character, and stopping after
+        <parameter>count</parameter> characters if that is specified.
+       </para>
+       <para>
+        <literal>substring(source=>'Thomas', start=>2, count=>3)</literal>
+        <returnvalue>hom</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> (
+          <parameter>source</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"/>.
+       </para>
+       <para>
+        <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+        <returnvalue>oma</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <function>substring</function> (
+          <parameter>source</parameter> <type>text</type>,
+          <parameter>pattern</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Extracts the first substring matching <acronym>POSIX</acronym> 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">
         <indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..f0850f9ef64 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"(source 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 6c6f31f7043..aed03195012 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3748,9 +3748,11 @@
 
 { oid => '936', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'text_substr' },
 { oid => '937', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+  proargnames => '{source, start}',
   prosrc => 'text_substr_no_len' },
 { oid => '2087',
   descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4168,6 +4170,7 @@
   prosrc => 'bitcat' },
 { oid => '1680', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+  proargnames => '{source, start, count}',
   prosrc => 'bitsubstr' },
 { oid => '1681', descr => 'bitstring length',
   proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4197,6 +4200,7 @@
   prosrc => 'bitposition' },
 { oid => '1699', descr => 'extract portion of bitstring',
   proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+  proargnames => '{source, start}',
   prosrc => 'bitsubstr_no_len' },
 
 { oid => '3030', descr => 'substitute portion of bitstring',
@@ -6302,9 +6306,11 @@
   prosrc => 'byteacat' },
 { oid => '2012', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea',
+  proargnames => '{source, start, count}',
   proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
 { oid => '2013', descr => 'extract portion of string',
   proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+  proargnames => '{source, start}',
   prosrc => 'bytea_substr_no_len' },
 { oid => '2085', descr => 'extract portion of string',
   proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
@@ -6504,9 +6510,11 @@
 
 { oid => '2073', descr => 'extract text matching regular expression',
   proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+  proargnames => '{source, pattern}',
   prosrc => 'textregexsubstr' },
 { oid => '2074', descr => 'extract text matching SQL regular expression',
   proname => 'substring', prolang => 'sql', prorettype => 'text',
+  proargnames => '{source, pattern, escape}',
   proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
 
 { oid => '2075', descr => 'convert int8 to bitstring',
-- 
2.34.1