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.
Decided to standardize on calling the SQL Similar To regular expression
escape replaceable "escape" everywhere.
Instead of fully documenting the obsolete syntax I added a note explaining
the keyword choice difference. Removed mention of it completely from the
Pattern Matching portion of the documentation - that section has enough
going on.
I also add "Same as" references for the two pairs of entries. Not married
to them but they do seem warranted; having Pattern Matching be required
reading to make that connection seems undesirable.
David J.
From b2f64615da9522427a2e2662b1d060ffed97088c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Mon, 31 Mar 2025 14:32:12 -0700
Subject: [PATCH 1/2] v3 0001 substring
---
doc/src/sgml/func.sgml | 115 +++++++++++++++++++++--
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 +++
3 files changed, 120 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index c642f1ea4e..e4c95f1e88 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2850,9 +2850,9 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
<returnvalue>text</returnvalue>
</para>
- <para role="func_signature">
+ <para role="func_signature"><s>
<function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
- <returnvalue>text</returnvalue>
+ <returnvalue>text</returnvalue></s>
</para>
<para>
Extracts the first substring matching <acronym>SQL</acronym> regular expression;
@@ -3806,6 +3806,58 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching POSIX regular expression; see
+ <xref linkend="functions-posix-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '...$')</literal>
+ <returnvalue>mas</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching <acronym>SQL</acronym> regular expression;
+ see <xref linkend="functions-similarto-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+ <returnvalue>oma</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>string</parameter> starting at
+ the <parameter>start</parameter>'th character,
+ and stopping after <parameter>count</parameter> characters if that is
+ specified.
+ </para>
+
+ <para>
+ <literal>substring('Thomas', 2, 3)</literal>
+ <returnvalue>hom</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -4811,6 +4863,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<returnvalue>\x5678</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bytea</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bytes</parameter> starting at
+ the <parameter>start</parameter>'th byte,
+ and stopping after <parameter>count</parameter> bytes if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring('\x1234567890'::bytea, 3, 2)</literal>
+ <returnvalue>\x5678</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -5353,6 +5426,26 @@ cast(-1234 as bytea) <lineannotation>\xfffffb2e</lineannotation>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bits</parameter> <type>bit</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bit</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bits</parameter> starting at
+ the <parameter>start</parameter>'th bit,
+ and stopping after <parameter>count</parameter> bits if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring(B'110010111111', 3, 2)</literal>
+ <returnvalue>00</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -5816,7 +5909,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
</synopsis>
or as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6020,11 +6113,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
</para>
<para>
- The <function>substring</function> function with two parameters,
- <function>substring(<replaceable>string</replaceable> from
- <replaceable>pattern</replaceable>)</function>, provides extraction of a
- substring
- that matches a POSIX regular expression pattern. It returns null if
+ The <function>substring</function> function with two parameters provides extraction of a
+ substring that matches a POSIX regular expression pattern.
+ It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+ It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+ It returns null if
there is no match, otherwise the first portion of the text that matched the
pattern. But if the pattern contains any parentheses, the portion
of the text that matched the first parenthesized subexpression (the
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e44..5ea9d786b6 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0737eb73c9..87e3006fef 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3686,6 +3687,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3696,9 +3698,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4116,6 +4120,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4145,6 +4150,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6239,15 +6245,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6436,9 +6446,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape_character}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
From 182f0b447d7860fe5db62863da0d7f582a0abfb9 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <david.g.johns...@gmail.com>
Date: Mon, 31 Mar 2025 15:10:28 -0700
Subject: [PATCH 2/2] v3 0002 delta
---
doc/src/sgml/func.sgml | 49 ++++++++++++------------
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 2 +-
3 files changed, 26 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e4c95f1e88..b5e315c17b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2837,7 +2837,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para>
<para>
Extracts the first substring matching POSIX regular expression; see
- <xref linkend="functions-posix-regexp"/>.
+ <xref linkend="functions-posix-regexp"/>. (Same as
+ <literal>substring(string text, pattern text)</literal>.)
</para>
<para>
<literal>substring('Thomas' from '...$')</literal>
@@ -2850,20 +2851,20 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>SIMILAR</literal> <parameter>pattern</parameter> <type>text</type> <literal>ESCAPE</literal> <parameter>escape</parameter> <type>text</type> )
<returnvalue>text</returnvalue>
</para>
- <para role="func_signature"><s>
- <function>substring</function> ( <parameter>string</parameter> <type>text</type> <literal>FROM</literal> <parameter>pattern</parameter> <type>text</type> <literal>FOR</literal> <parameter>escape</parameter> <type>text</type> )
- <returnvalue>text</returnvalue></s>
- </para>
<para>
Extracts the first substring matching <acronym>SQL</acronym> regular expression;
- see <xref linkend="functions-similarto-regexp"/>. The first form has
- been specified since SQL:2003; the second form was only in SQL:1999
- and should be considered obsolete.
+ see <xref linkend="functions-similarto-regexp"/>. (Same as
+ <literal>substring(string text, pattern text, escape text)</literal>.)
</para>
<para>
<literal>substring('Thomas' similar '%#"o_a#"_' escape '#')</literal>
<returnvalue>oma</returnvalue>
- </para></entry>
+ </para>
+ <para>
+ Obsolescence note: SQL:1999 introduced this function with <literal>FROM</literal>
+ and <literal>FOR</literal> as the keywords but switched to this in SQL:2003.
+ </para>
+ </entry>
</row>
<row>
@@ -3814,7 +3815,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para>
<para>
Extracts the first substring matching POSIX regular expression; see
- <xref linkend="functions-posix-regexp"/>.
+ <xref linkend="functions-posix-regexp"/>. (Same as
+ <literal>substring(string text FROM pattern text)</literal>.)
</para>
<para>
<literal>substring('Thomas', '...$')</literal>
@@ -3824,12 +3826,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
<row>
<entry role="func_table_entry"><para role="func_signature">
- <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape </parameter> <type>text</type>)
<returnvalue>text</returnvalue>
</para>
<para>
Extracts the first substring matching <acronym>SQL</acronym> regular expression;
- see <xref linkend="functions-similarto-regexp"/>.
+ see <xref linkend="functions-similarto-regexp"/>. (Same as
+ <literal>substring(string text SIMILAR pattern text ESCAPE escape text)</literal>.)
</para>
<para>
<literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
@@ -5593,8 +5596,8 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
</indexterm>
<synopsis>
-<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT LIKE <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
</synopsis>
<para>
@@ -5766,8 +5769,8 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
</indexterm>
<synopsis>
-<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
-<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape-character</replaceable></optional>
+<replaceable>string</replaceable> SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
+<replaceable>string</replaceable> NOT SIMILAR TO <replaceable>pattern</replaceable> <optional>ESCAPE <replaceable>escape</replaceable></optional>
</synopsis>
<para>
@@ -5901,15 +5904,11 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
regular expression pattern. The function can be written according
to standard SQL syntax:
<synopsis>
-substring(<replaceable>string</replaceable> similar <replaceable>pattern</replaceable> escape <replaceable>escape-character</replaceable>)
-</synopsis>
- or using the now obsolete SQL:1999 syntax:
-<synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable> for <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable> SIMILAR <replaceable>pattern</replaceable> ESCAPE <replaceable>escape</replaceable>)
</synopsis>
- or as a plain three-argument function:
+ It can also written as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6115,9 +6114,9 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
<para>
The <function>substring</function> function with two parameters provides extraction of a
substring that matches a POSIX regular expression pattern.
- It has syntax:
+ The function can be written according to standard SQL syntax:
<synopsis>
-substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+substring(<replaceable>string</replaceable> FROM <replaceable>pattern</replaceable>)
</synopsis>
It can also written as a plain two-argument function:
<synopsis>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 5ea9d786b6..544b549ae7 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87e3006fef..5267f06aec 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6450,7 +6450,7 @@
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
- proargnames => '{string, pattern, escape_character}',
+ proargnames => '{string, pattern, escape}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
