public inbox for [email protected]
help / color / mirror / Atom feedRe: add function argument name to substring and substr
12+ messages / 7 participants
[nested] [flat]
* Re: add function argument name to substring and substr
@ 2025-04-01 13:14 Marcos Pegoraro <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Marcos Pegoraro @ 2025-04-01 13:14 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers
Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <
[email protected]> 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: [email protected], Name: John' from
'@(.*), Name')</literal>
regards
Marcos
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-04-01 13:20 Daniel Gustafsson <[email protected]>
parent: Marcos Pegoraro <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Daniel Gustafsson @ 2025-04-01 13:20 UTC (permalink / raw)
To: Marcos Pegoraro <[email protected]>; +Cc: David G. Johnston <[email protected]>; jian he <[email protected]>; pgsql-hackers
> On 1 Apr 2025, at 15:14, Marcos Pegoraro <[email protected]> wrote:
>
> Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <[email protected]> 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: [email protected], 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-04-01 14:40 David G. Johnston <[email protected]>
parent: Marcos Pegoraro <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2025-04-01 14:40 UTC (permalink / raw)
To: Marcos Pegoraro <[email protected]>; +Cc: jian he <[email protected]>; pgsql-hackers
On Tue, Apr 1, 2025 at 6:15 AM Marcos Pegoraro <[email protected]> wrote:
> Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <
> [email protected]> 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: [email protected], 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-07-21 06:31 jian he <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-07-21 06:31 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; pgsql-hackers
On Tue, Apr 1, 2025 at 10:41 PM David G. Johnston
<[email protected]> wrote:
>
> On Tue, Apr 1, 2025 at 6:15 AM Marcos Pegoraro <[email protected]> wrote:
>>
>> Em ter., 1 de abr. de 2025 às 02:00, David G. Johnston <[email protected]> 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: [email protected], 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:
[text/x-patch] v6-0001-add-function-argument-name-to-substr.patch (3.1K, 2-v6-0001-add-function-argument-name-to-substr.patch)
download | inline diff:
From 736ff000500d8d87499eaef0fb7331960173c4e8 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
[text/x-patch] v6-0002-add-function-argument-name-to-function-substring.patch (12.3K, 3-v6-0002-add-function-argument-name-to-function-substring.patch)
download | inline diff:
From 8f36831bed5e6182f4034fa4ae4bd16fe5272460 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-10-13 14:22 jian he <[email protected]>
parent: jian he <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: jian he @ 2025-10-13 14:22 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; pgsql-hackers
On Mon, Jul 21, 2025 at 2:31 PM jian he <[email protected]> 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:
[text/x-patch] v7-0002-add-function-argument-name-to-function-substring.patch (12.5K, 2-v7-0002-add-function-argument-name-to-function-substring.patch)
download | inline diff:
From 5afc6f3ce1ab154576002c1dfe82a2dd22d80964 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
[text/x-patch] v7-0001-add-function-argument-name-to-substr.patch (3.1K, 3-v7-0001-add-function-argument-name-to-substr.patch)
download | inline diff:
From e18bc9123a65bd9c30fb8541ca464a407351fcce Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-10-13 14:58 Andrew Dunstan <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Andrew Dunstan @ 2025-10-13 14:58 UTC (permalink / raw)
To: jian he <[email protected]>; David G. Johnston <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; pgsql-hackers
On 2025-10-13 Mo 10:22 AM, jian he wrote:
> On Mon, Jul 21, 2025 at 2:31 PM jian he <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-10-13 15:26 jian he <[email protected]>
parent: Andrew Dunstan <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: jian he @ 2025-10-13 15:26 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers
On Mon, Oct 13, 2025 at 10:58 PM Andrew Dunstan <[email protected]> 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,
https://www.postgresql.org/message-id/CACJufxG3NFKKsh6x4fRLv8h3V-HvN4W5dA%3DzNKMxsNcDwOKang%40mail.g...,
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-10-13 16:10 Tom Lane <[email protected]>
parent: jian he <[email protected]>
1 sibling, 2 replies; 12+ messages in thread
From: Tom Lane @ 2025-10-13 16:10 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: jian he <[email protected]>; David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers
Andrew Dunstan <[email protected]> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-10-14 07:44 jian he <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: jian he @ 2025-10-14 07:44 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers
On Tue, Oct 14, 2025 at 12:11 AM Tom Lane <[email protected]> wrote:
>
> Andrew Dunstan <[email protected]> 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.
\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)
\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)
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:
[text/x-patch] v8-0002-add-function-argument-name-to-function-substring.patch (19.6K, 2-v8-0002-add-function-argument-name-to-function-substring.patch)
download | inline diff:
From d1b3d6c7eb3ebb4456ea2f6bb2223fd6c535fedd Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
[text/x-patch] v8-0001-add-function-argument-name-to-function-substr.patch (4.9K, 3-v8-0001-add-function-argument-name-to-function-substr.patch)
download | inline diff:
From 0395bf1159c682c428716c801790b389f073cce5 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-11-10 02:30 jian he <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: jian he @ 2025-11-10 02:30 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers
On Tue, Oct 14, 2025 at 12:11 AM Tom Lane <[email protected]> wrote:
>
> Andrew Dunstan <[email protected]> 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:
[text/x-patch] v9-0001-add-function-argument-name-to-function-substr.patch (4.9K, 2-v9-0001-add-function-argument-name-to-function-substr.patch)
download | inline diff:
From 69f11ebcdb76ef2c06b97fa4fc1bcd06edc55a6c Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
[text/x-patch] v9-0002-add-function-argument-name-to-function-substring.patch (19.3K, 3-v9-0002-add-function-argument-name-to-function-substring.patch)
download | inline diff:
From b0b1832adcf0a28bfde6525463a7abc263f4051a Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-12-31 07:47 jian he <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-12-31 07:47 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers
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:
[text/x-patch] v10-0001-Add-argument-names-to-the-substr-functions.patch (4.6K, 2-v10-0001-Add-argument-names-to-the-substr-functions.patch)
download | inline diff:
From d30592f190860cc68d08f956ac6853aa2c60e1bf Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
[text/x-patch] v10-0002-Add-argument-names-to-the-substring-functions.patch (18.7K, 3-v10-0002-Add-argument-names-to-the-substring-functions.patch)
download | inline diff:
From 8fe7182867f833af9bdb2704a01ec358633fb5fd Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2026-03-27 15:52 Peter Eisentraut <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Peter Eisentraut @ 2026-03-27 15:52 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Andrew Dunstan <[email protected]>; David G. Johnston <[email protected]>; Marcos Pegoraro <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>
On 31.12.25 08:47, jian he wrote:
> 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.
I don't really see the point of this. These are standardized functions,
and people should be using them in the standardized ways. By adding
parameter names, we are opening up the use of these in nonstandard and
unportable ways. I don't think the arguments of these functions are
terribly confusing that use of named parameters adds much value. At
least I didn't see this argument being made.
Furthermore, if we somehow decided to do this, let's not do it four
functions at a time but have a general plan about whether, why, and how
to add parameter names to built-in/standard functions.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-03-27 15:52 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-01 13:14 Re: add function argument name to substring and substr Marcos Pegoraro <[email protected]>
2025-04-01 13:20 ` Daniel Gustafsson <[email protected]>
2025-04-01 14:40 ` David G. Johnston <[email protected]>
2025-07-21 06:31 ` jian he <[email protected]>
2025-10-13 14:22 ` jian he <[email protected]>
2025-10-13 14:58 ` Andrew Dunstan <[email protected]>
2025-10-13 15:26 ` jian he <[email protected]>
2025-10-13 16:10 ` Tom Lane <[email protected]>
2025-10-14 07:44 ` jian he <[email protected]>
2025-11-10 02:30 ` jian he <[email protected]>
2025-12-31 07:47 ` jian he <[email protected]>
2026-03-27 15:52 ` Peter Eisentraut <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox