On Mon, Mar 31, 2025 at 9:12 PM jian he <jian.universal...@gmail.com> wrote:
> On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
> <david.g.johns...@gmail.com> wrote:
> >
> > On Tue, Mar 18, 2025 at 9:04 PM jian he <jian.universal...@gmail.com>
> wrote:
> >>
> >>
> >> new patch attached.
> >>
> >
> > I've done v4 with a delta patch.
>
> your v4-0001-v3-0001-substring.patch is not the same as my
> v3-0001-add-argument-name-to-function-substring-and-subst.patch
>
>
Sorry about that. v5 attached. Confirmed with diff the v3 and v5 0001 so
we should be good.
David J.
From 507064e643b54d2bebd7689acd3dde60d230e328 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Mon, 31 Mar 2025 21:36:42 -0700
Subject: [PATCH 1/2] v3-0001 substring
---
doc/src/sgml/func.sgml | 111 +++++++++++++++++++++--
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 +++
3 files changed, 118 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c642f1ea4e..a3569995f1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3806,6 +3806,58 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching POSIX regular expression; see
+ <xref linkend="functions-posix-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '...$')</literal>
+ <returnvalue>mas</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching <acronym>SQL</acronym> regular expression;
+ see <xref linkend="functions-similarto-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+ <returnvalue>oma</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>string</parameter> starting at
+ the <parameter>start</parameter>'th character,
+ and stopping after <parameter>count</parameter> characters if that is
+ specified.
+ </para>
+
+ <para>
+ <literal>substring('Thomas', 2, 3)</literal>
+ <returnvalue>hom</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -4811,6 +4863,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<returnvalue>\x5678</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bytea</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bytes</parameter> starting at
+ the <parameter>start</parameter>'th byte,
+ and stopping after <parameter>count</parameter> bytes if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring('\x1234567890'::bytea, 3, 2)</literal>
+ <returnvalue>\x5678</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -5353,6 +5426,26 @@ cast(-1234 as bytea) <lineannotation>\xfffffb2e</lineannotation>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bits</parameter> <type>bit</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bit</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bits</parameter> starting at
+ the <parameter>start</parameter>'th bit,
+ and stopping after <parameter>count</parameter> bits if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring(B'110010111111', 3, 2)</literal>
+ <returnvalue>00</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -5816,7 +5909,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
</synopsis>
or as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6020,11 +6113,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
</para>
<para>
- The <function>substring</function> function with two parameters,
- <function>substring(<replaceable>string</replaceable> from
- <replaceable>pattern</replaceable>)</function>, provides extraction of a
- substring
- that matches a POSIX regular expression pattern. It returns null if
+ The <function>substring</function> function with two parameters provides extraction of a
+ substring that matches a POSIX regular expression pattern.
+ It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+ It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+ It returns null if
there is no match, otherwise the first portion of the text that matched the
pattern. But if the pattern contains any parentheses, the portion
of the text that matched the first parenthesized subexpression (the
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e44..5ea9d786b6 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0737eb73c9..87e3006fef 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3686,6 +3687,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3696,9 +3698,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4116,6 +4120,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4145,6 +4150,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6239,15 +6245,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6436,9 +6446,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape_character}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
From 6ef3ff43f133dd6be423c4a567afda10f1c64988 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Mon, 31 Mar 2025 21:54:44 -0700
Subject: [PATCH 2/2] v3 delta
---
doc/src/sgml/func.sgml | 46 ++++++++++++------------
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 1 +
3 files changed, 24 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a3569995f1..c9516966c1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2837,7 +2837,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para>
<para>
Extracts the first substring matching POSIX regular expression; see
- <xref linkend="functions-posix-regexp"/>.
+ <xref linkend="functions-posix-regexp"/>. (Same as
+ <literal>substring(string text, pattern text)</literal>.)
</para>
<para>
<literal>substring('Thomas' from '...$')</literal>
@@ -2850,19 +2851,18 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
<returnvalue>text</returnvalue>
</para>
- <para role="func_signature">
- <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
- <returnvalue>text</returnvalue>
- </para>
<para>
Extracts the first substring matching <acronym>SQL</acronym> regular expression;
- see <xref linkend="functions-similarto-regexp"/>. The first form has
- been specified since SQL:2003; the second form was only in SQL:1999
- and should be considered obsolete.
+ see <xref linkend="functions-similarto-regexp"/>. (Same as
+ <literal>substring(string text, pattern text, escape text)</literal>.)
</para>
<para>
<literal>substring('Thomas' similar '%#"o_a#"_' escape '#')</literal>
<returnvalue>oma</returnvalue>
+ </para>
+ <para>
+ Obsolescence note: SQL:1999 introduced this function with <literal>FROM</literal>
+ and <literal>FOR</literal> as the keywords but switched to this in SQL:2003.
</para></entry>
</row>
@@ -3814,7 +3814,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para>
<para>
Extracts the first substring matching POSIX regular expression; see
- <xref linkend="functions-posix-regexp"/>.
+ <xref linkend="functions-posix-regexp"/>. (Same as
+ <literal>substring(string text FROM pattern text)</literal>.)
</para>
<para>
<literal>substring('Thomas', '...$')</literal>
@@ -3824,12 +3825,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<row>
<entry role="func_table_entry"><para role="func_signature">
- <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape </parameter> <type>text</type>)
<returnvalue>text</returnvalue>
</para>
<para>
Extracts the first substring matching <acronym>SQL</acronym> regular expression;
- see <xref linkend="functions-similarto-regexp"/>.
+ see <xref linkend="functions-similarto-regexp"/>. (Same as
+ <literal>substring(string text SIMILAR pattern text ESCAPE escape text)</literal>.)
</para>
<para>
<literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
@@ -5593,8 +5595,8 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
</indexterm>
<synopsis>
-<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
</synopsis>
<para>
@@ -5766,8 +5768,8 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
</indexterm>
<synopsis>
-<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
</synopsis>
<para>
@@ -5901,15 +5903,11 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
regular expression pattern. The function can be written according
to standard SQL syntax:
<synopsis>
-substring(<replaceable>string</replaceable> similar <replaceable>pattern</replaceable> escape <replaceable>escape-character</replaceable>)
-</synopsis>
- or using the now obsolete SQL:1999 syntax:
-<synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable> for <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable> SIMILAR <replaceable>pattern</replaceable> ESCAPE <replaceable>escape</replaceable>)
</synopsis>
- or as a plain three-argument function:
+ It can also written as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6115,9 +6113,9 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
<para>
The <function>substring</function> function with two parameters provides extraction of a
substring that matches a POSIX regular expression pattern.
- It has syntax:
+ The function can be written according to standard SQL syntax:
<synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+substring(<replaceable>string</replaceable> FROM <replaceable>pattern</replaceable>)
</synopsis>
It can also written as a plain two-argument function:
<synopsis>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 5ea9d786b6..544b549ae7 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87e3006fef..8bb2b0de89 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6451,6 +6451,7 @@
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
proargnames => '{string, pattern, escape_character}',
+ proargnames => '{string, pattern, escape}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
