public inbox for [email protected]
help / color / mirror / Atom feedadd function argument name to substring and substr
12+ messages / 3 participants
[nested] [flat]
* add function argument name to substring and substr
@ 2025-01-22 06:21 jian he <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: jian he @ 2025-01-22 06:21 UTC (permalink / raw)
To: pgsql-hackers
hi.
attached patch add function argument name to function substring and substr
you can see the visual changes.
mater behavior:
\df substring
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+-------------------------+------
pg_catalog | substring | bit | bit, integer | func
pg_catalog | substring | bit | bit, integer, integer | func
pg_catalog | substring | bytea | bytea, integer | func
pg_catalog | substring | bytea | bytea, integer, integer | func
pg_catalog | substring | text | text, integer | func
pg_catalog | substring | text | text, integer, integer | func
pg_catalog | substring | text | text, text | func
pg_catalog | substring | text | text, text, text | func
with patch
List of functions
Schema | Name | Result data type | Argument
data types | Type
------------+-----------+------------------+--------------------------------------------------+------
pg_catalog | substring | bit | bits bit, start integer
| func
pg_catalog | substring | bit | bits bit, start integer,
count integer | func
pg_catalog | substring | bytea | bytes bytea, start integer
| func
pg_catalog | substring | bytea | bytes bytea, start integer,
count integer | func
pg_catalog | substring | text | string text, pattern text
| func
pg_catalog | substring | text | string text, pattern
text, escape_character text | func
pg_catalog | substring | text | string text, start
integer | func
pg_catalog | substring | text | string text, start
integer, count integer | func
I did the same change to the function substr.
since 9.7.2. SIMILAR TO Regular Expressions we use
substring(string, pattern, escape-character)
so i refactor the substring function argument name to
substring(string text, pattern text, escape_character text).
we can make it as ``substring(string text, pattern text, escape text).``
then in 9.7.2, we need to change the substring synopsis section.
one thing I am not sure is about
9.4. String Functions and Operators
Table 9.9. SQL String Functions and Operators.
Do we need entries for substring related functions?
current signature
substring ( string text [ FROM start integer ] [ FOR count integer ] )
is kind of different from
substring(string text, start integer, count integer).
since the previous one can allow keywords "FROM", "FOR", the latter one won't.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-01-27 02:20 jian he <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: jian he @ 2025-01-27 02:20 UTC (permalink / raw)
To: pgsql-hackers
hi.
I forgot to attach the patch.
here we are.
Attachments:
[text/x-patch] v1-0001-add-function-argument-name-to-function-substring-.patch (4.7K, 2-v1-0001-add-function-argument-name-to-function-substring-.patch)
download | inline diff:
From 249a083627b0d8ad15c698601f7b7649be5262a1 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 22 Jan 2025 14:19:15 +0800
Subject: [PATCH v1 1/1] add function argument name to function substring and
substr
---
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 ++++++++++++
2 files changed, 13 insertions(+), 1 deletion(-)
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 591157b1d1..0fc1d9a4a5 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 18560755d2..b2ebccb5b6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3637,6 +3637,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3655,6 +3656,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3665,9 +3667,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4085,6 +4089,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4114,6 +4119,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6185,15 +6191,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6382,9 +6392,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape_character}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-02-17 22:12 David G. Johnston <[email protected]>
parent: jian he <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2025-02-17 22:12 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Tue, Jan 21, 2025 at 11:21 PM jian he <[email protected]>
wrote:
> hi.
>
> attached patch add function argument name to function substring and substr
> you can see the visual changes.
> mater behavior:
>
> \df substring
> List of functions
> Schema | Name | Result data type | Argument data types | Type
>
> ------------+-----------+------------------+-------------------------+------
> pg_catalog | substring | bit | bit, integer | func
> pg_catalog | substring | bit | bit, integer, integer | func
> pg_catalog | substring | bytea | bytea, integer | func
> pg_catalog | substring | bytea | bytea, integer, integer | func
> pg_catalog | substring | text | text, integer | func
> pg_catalog | substring | text | text, integer, integer | func
> pg_catalog | substring | text | text, text | func
> pg_catalog | substring | text | text, text, text | func
>
> with patch
> List of functions
> Schema | Name | Result data type | Argument
> data types | Type
>
> ------------+-----------+------------------+--------------------------------------------------+------
> pg_catalog | substring | bit | bits bit, start integer
> | func
> pg_catalog | substring | bit | bits bit, start integer,
> count integer | func
> pg_catalog | substring | bytea | bytes bytea, start integer
> | func
> pg_catalog | substring | bytea | bytes bytea, start integer,
> count integer | func
> pg_catalog | substring | text | string text, pattern text
> | func
> pg_catalog | substring | text | string text, pattern
> text, escape_character text | func
> pg_catalog | substring | text | string text, start
> integer | func
> pg_catalog | substring | text | string text, start
> integer, count integer | func
>
> I did the same change to the function substr.
> since 9.7.2. SIMILAR TO Regular Expressions we use
> substring(string, pattern, escape-character)
> so i refactor the substring function argument name to
> substring(string text, pattern text, escape_character text).
>
> we can make it as ``substring(string text, pattern text, escape text).``
> then in 9.7.2, we need to change the substring synopsis section.
>
>
>
> one thing I am not sure is about
> 9.4. String Functions and Operators
> Table 9.9. SQL String Functions and Operators.
> Do we need entries for substring related functions?
>
> current signature
> substring ( string text [ FROM start integer ] [ FOR count integer ] )
> is kind of different from
> substring(string text, start integer, count integer).
> since the previous one can allow keywords "FROM", "FOR", the latter one
> won't.
>
>
Table 9.9 limits itself to those functions defined in the SQL standard;
which are basically the ones that use keywords instead of commas.
The substring(string, start, count) function you note is already covered in
Table 9.10 but we spell it substr(...)
I don't think adding yet more spellings of this same function is warranted
or desirable at this point. I'd maybe add a note if substring(,,,) works
to substr saying that substring is a valid alias. I could be convinced to
just document though.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-17 06:48 vignesh C <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: vignesh C @ 2025-03-17 06:48 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Mon, 27 Jan 2025 at 07:51, jian he <[email protected]> wrote:
>
> hi.
>
> I forgot to attach the patch.
> here we are.
I noticed that David's comments from [1] have not yet been addressed,
I have changed the status of commitfest entry to "Waiting on Author",
please address them and change the status to "Needs review".
[1] - https://www.postgresql.org/message-id/[email protected]...
Regards,
Vignesh
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-18 03:20 jian he <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-03-18 03:20 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-hackers
On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
<[email protected]> wrote:
>
> Table 9.9 limits itself to those functions defined in the SQL standard; which are basically the ones that use keywords instead of commas.
>
> The substring(string, start, count) function you note is already covered in Table 9.10 but we spell it substr(...)
>
> I don't think adding yet more spellings of this same function is warranted or desirable at this point.
ok.
> I'd maybe add a note if substring(,,,) works to substr saying that substring is a valid alias. I could be convinced to just document though.
>
it seems already in the doc.
substr ( string text, start integer [, count integer ] ) → text
Extracts the substring of string starting at the start'th character,
and extending for count characters if that is specified. (Same as
substring(string from start for count).)
substr ( bytes bytea, start integer [, count integer ] ) → bytea
Extracts the substring of bytes starting at the start'th byte, and
extending for count bytes if that is specified. (Same as
substring(bytes from start for count).)
new patch attached.
main changes:
1. change 3 argument func argument from
(string text, pattern text, escape_character text)
to
(string text, pattern text, escape text)
2. add synopsis section in 9.7.3. POSIX Regular Expressions for
function substring.
we only have the synopsis section for function substring in 9.7.2
section, now add it to 9.7.3.
also add an example about using named natation call substring:
substring(string=>'foobar', pattern=> 'o.b')
the patch is small, I just put the
\df substring
\df substr
(before and after patch) output into the commit message.
Attachments:
[text/x-patch] v2-0001-add-argument-name-to-function-substring-and-subst.patch (10.1K, 2-v2-0001-add-argument-name-to-function-substring-and-subst.patch)
download | inline diff:
From f0b49ccd27d41ff1bd1619284656efcb1d3c3e0f Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 18 Mar 2025 11:13:11 +0800
Subject: [PATCH v2 1/1] add argument name to function substring and substr
visual changes.
before
\df substr
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+-------------------------+------
pg_catalog | substr | bytea | bytea, integer | func
pg_catalog | substr | bytea | bytea, integer, integer | func
pg_catalog | substr | text | text, integer | func
pg_catalog | substr | text | text, integer, integer | func
(4 rows)
now
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+-------------------------------------------+------
pg_catalog | substr | bytea | bytes bytea, start integer | func
pg_catalog | substr | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substr | text | string text, start integer | func
pg_catalog | substr | text | string text, start integer, count integer | func
(4 rows)
before
\df substring
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+-------------------------+------
pg_catalog | substring | bit | bit, integer | func
pg_catalog | substring | bit | bit, integer, integer | func
pg_catalog | substring | bytea | bytea, integer | func
pg_catalog | substring | bytea | bytea, integer, integer | func
pg_catalog | substring | text | text, integer | func
pg_catalog | substring | text | text, integer, integer | func
pg_catalog | substring | text | text, text | func
pg_catalog | substring | text | text, text, text | func
(8 rows)
now
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+-------------------------------------------+------
pg_catalog | substring | bit | bits bit, start integer | func
pg_catalog | substring | bit | bits bit, start integer, count integer | func
pg_catalog | substring | bytea | bytes bytea, start integer | func
pg_catalog | substring | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substring | text | string text, pattern text | func
pg_catalog | substring | text | string text, pattern text, escape text | func
pg_catalog | substring | text | string text, start integer | func
pg_catalog | substring | text | string text, start integer, count integer | func
(8 rows)
---
doc/src/sgml/func.sgml | 23 +++++++++++++++--------
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 ++++++++++++
3 files changed, 28 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1c3810e1a04..be47e06727d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5816,7 +5816,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
</synopsis>
or as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6020,11 +6020,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
</para>
<para>
- The <function>substring</function> function with two parameters,
- <function>substring(<replaceable>string</replaceable> from
- <replaceable>pattern</replaceable>)</function>, provides extraction of a
- substring
- that matches a POSIX regular expression pattern. It returns null if
+ The <function>substring</function> function with two parameters provides extraction of a
+ substring that matches a POSIX regular expression pattern.
+ It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+ It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+ It returns null if
there is no match, otherwise the first portion of the text that matched the
pattern. But if the pattern contains any parentheses, the portion
of the text that matched the first parenthesized subexpression (the
@@ -6039,8 +6045,9 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
<para>
Some examples:
<programlisting>
-substring('foobar' from 'o.b') <lineannotation>oob</lineannotation>
-substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
+substring('foobar' from 'o.b') <lineannotation>oob</lineannotation>
+substring(string=>'foobar', pattern=> 'o.b') <lineannotation>oob</lineannotation>
+substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
</programlisting>
</para>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..544b549ae74 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 890822eaf79..1e023c6465b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3686,6 +3687,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3696,9 +3698,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4116,6 +4120,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4145,6 +4150,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6239,15 +6245,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6436,9 +6446,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-19 00:18 David G. Johnston <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2025-03-19 00:18 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Mon, Mar 17, 2025 at 8:20 PM jian he <[email protected]> wrote:
> On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
> <[email protected]> wrote:
> >
> > Table 9.9 limits itself to those functions defined in the SQL standard;
> which are basically the ones that use keywords instead of commas.
> >
> > The substring(string, start, count) function you note is already covered
> in Table 9.10 but we spell it substr(...)
> >
> > I don't think adding yet more spellings of this same function is
> warranted or desirable at this point.
>
> ok.
>
> > I'd maybe add a note if substring(,,,) works to substr saying that
> substring is a valid alias. I could be convinced to just document though.
> >
> it seems already in the doc.
>
> substr ( string text, start integer [, count integer ] ) → text
> Extracts the substring of string starting at the start'th character,
> and extending for count characters if that is specified. (Same as
> substring(string from start for count).)
>
> substr ( bytes bytea, start integer [, count integer ] ) → bytea
> Extracts the substring of bytes starting at the start'th byte, and
> extending for count bytes if that is specified. (Same as
> substring(bytes from start for count).)
>
>
> new patch attached.
> main changes:
> 1. change 3 argument func argument from
> (string text, pattern text, escape_character text)
> to
> (string text, pattern text, escape text)
>
Why? It can only be one character so that existing name seems well chosen.
postgres=# select substring('123^^,123',',','^^');
ERROR: invalid escape string
HINT: Escape string must be empty or one character.
CONTEXT: SQL function "substring" statement 1
> 2. add synopsis section in 9.7.3. POSIX Regular Expressions for
> function substring.
> we only have the synopsis section for function substring in 9.7.2
> section, now add it to 9.7.3.
>
I'd probably try and resolve that the other way...point the reader to the
reference page for the function if they want to see syntax. I'm mixed on
(leaning against) whether choosing this place to demonstrate all the
possible spellings is the best.
also add an example about using named natation call substring:
> substring(string=>'foobar', pattern=> 'o.b')
>
I'm already pulling my hair out at this showing all the insanity that
exists without adding this to the mix.
The vast majority of examples throughout the manual use traditional
function call syntax func_name(arg1, arg2, etc.); I'd rather keep with
convention than start to scatter about alternative syntax choices just to
give the random reader who happens upon this fairly esoteric part of the
manual the benefit of seeing their options. If that is a goal, then I'd
suggest spending some time in our Tutorial adding some more examples with
these alternative forms to people looking to be exposed to new things in
the place they'd go to look for them. They probably won't learn about them
from the Syntax section.
On the plus side, I agree now we should add:
substring(string text, pattern text[, escape-character text])
to Table 9.10
I'd also rename escape to escape-character in the other SQL substring
function synopses. The RegEx page got that part correct.
Do as little or as much with the RegEx section as you'd like, though it
seems like separate material from $subject. The page seems to already use
replaceable names instead of data types so on that score it should be
unaffected if we've chosen the same names.
Food for thought, it seems a bit redundant to name the first argument
basically the same as the data type. I was thinking that "content" would
be a better choice. This is basically a polymorphic function where all the
inputs are the same thing just having different types - and that thing is
being "content".
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-19 01:19 jian he <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-03-19 01:19 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-hackers
On Wed, Mar 19, 2025 at 8:19 AM David G. Johnston
<[email protected]> wrote:
>
> The vast majority of examples throughout the manual use traditional function call syntax func_name(arg1, arg2, etc.); I'd rather keep with convention than start to scatter about alternative syntax choices just to give the random reader who happens upon this fairly esoteric part of the manual the benefit of seeing their options. If that is a goal, then I'd suggest spending some time in our Tutorial adding some more examples with these alternative forms to people looking to be exposed to new things in the place they'd go to look for them. They probably won't learn about them from the Syntax section.
>
> On the plus side, I agree now we should add:
> substring(string text, pattern text[, escape-character text])
> to Table 9.10
>
in Table Table 9.9 we have
```
substring ( string text FROM pattern text ) → text
Extracts the first substring matching POSIX regular expression; see
Section 9.7.3.
substring('Thomas' from '...$') → mas
```
can we change to
substring ( string text FROM pattern text ) → text
substring ( string text, pattern text ) → text
Extracts the first substring matching POSIX regular expression;
the second format is not standardized. see Section 9.7.3.
substring('Thomas' from '...$') → mas
if we add to
``substring ( string text, pattern text ) → text``
Table 9.10,
then maybe it feels like duplication?
(same function in Table 9.9, Table 9.10, then we also need some words
saying that they are the same)
I do realized we have brief explanation about Table 9.9 and Table 9.10 in
second paragraph of
https://www.postgresql.org/docs/current/functions-string.html
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-19 01:35 David G. Johnston <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2025-03-19 01:35 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Tue, Mar 18, 2025 at 6:20 PM jian he <[email protected]> wrote:
> On Wed, Mar 19, 2025 at 8:19 AM David G. Johnston
> <[email protected]> wrote:
> >
> > The vast majority of examples throughout the manual use traditional
> function call syntax func_name(arg1, arg2, etc.); I'd rather keep with
> convention than start to scatter about alternative syntax choices just to
> give the random reader who happens upon this fairly esoteric part of the
> manual the benefit of seeing their options. If that is a goal, then I'd
> suggest spending some time in our Tutorial adding some more examples with
> these alternative forms to people looking to be exposed to new things in
> the place they'd go to look for them. They probably won't learn about them
> from the Syntax section.
> >
> > On the plus side, I agree now we should add:
> > substring(string text, pattern text[, escape-character text])
> > to Table 9.10
> >
> in Table Table 9.9 we have
> ```
> substring ( string text FROM pattern text ) → text
> Extracts the first substring matching POSIX regular expression; see
> Section 9.7.3.
> substring('Thomas' from '...$') → mas
> ```
>
> can we change to
> substring ( string text FROM pattern text ) → text
> substring ( string text, pattern text ) → text
> Extracts the first substring matching POSIX regular expression;
> the second format is not standardized. see Section 9.7.3.
> substring('Thomas' from '...$') → mas
>
>
No, based on the (I presume) fact that the substring(string, pattern)
variant is not defined in the SQL standard and Table 9.9 is reserved for
those functions.
It would be a different, but probably worth considering, patch to simply
combine Tables 9.9 and 9.10 and just denote which entries are standard and
which are not. The decision to split the tables along that property came
well before our current table format which seems much more amenable to
merging them together.
> if we add to
> ``substring ( string text, pattern text ) → text``
> Table 9.10,
> then maybe it feels like duplication?
> (same function in Table 9.9, Table 9.10, then we also need some words
> saying that they are the same)
>
We can/should add substring(string, pattern) to Table 9.10 for the same
reason and the same general wording that substr(string, start) exists on
that table.
I would be in favor of adding a similar "same as" comment to the functions
in Table 9.9
I just now processed the cross references in Table 9.9 to the POSIX
section. The new entry in Table 9.10 would want that too.
David J.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-19 04:04 jian he <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-03-19 04:04 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-hackers
hi.
new patch attached.
now \df substring looks like:
List of functions
Schema | Name | Result data type | Argument
data types | Type
------------+-----------+------------------+--------------------------------------------------+------
pg_catalog | substring | bit | bits bit, start integer
| func
pg_catalog | substring | bit | bits bit, start integer,
count integer | func
pg_catalog | substring | bytea | bytes bytea, start
integer | func
pg_catalog | substring | bytea | bytes bytea, start
integer, count integer | func
pg_catalog | substring | text | string text, pattern text
| func
pg_catalog | substring | text | string text, pattern
text, escape_character text | func
pg_catalog | substring | text | string text, start
integer | func
pg_catalog | substring | text | string text, start
integer, count integer | func
Table 9.10 also have doc entry for substring.
in Table 9.10, i have:
substring ( string text, pattern text ) → text
substring ( string text, pattern text, escape_character text) → text
we can put it into one,
but one is based on POSIX regular expression, another one is based on SQL,
the difference is big, that's why put it into two.
Attachments:
[text/x-patch] v3-0001-add-argument-name-to-function-substring-and-subst.patch (13.0K, 2-v3-0001-add-argument-name-to-function-substring-and-subst.patch)
download | inline diff:
From 835dc9364f9f986445b77af8fa761e7577cbbdff Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 19 Mar 2025 11:58:55 +0800
Subject: [PATCH v3 1/1] add argument name to function substring and substr
also add substr and substring (no keywords) functions entry to doc.
Table 9.9 is about keywords substring entry,
now Table 9.10 have function argument doc entry of substring.
now psql looks like:
\df substring
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+--------------------------------------------------+------
pg_catalog | substring | bit | bits bit, start integer | func
pg_catalog | substring | bit | bits bit, start integer, count integer | func
pg_catalog | substring | bytea | bytes bytea, start integer | func
pg_catalog | substring | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substring | text | string text, pattern text | func
pg_catalog | substring | text | string text, pattern text, escape_character text | func
pg_catalog | substring | text | string text, start integer | func
pg_catalog | substring | text | string text, start integer, count integer | func
(8 rows)
\df substr
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+-------------------------------------------+------
pg_catalog | substr | bytea | bytes bytea, start integer | func
pg_catalog | substr | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substr | text | string text, start integer | func
pg_catalog | substr | text | string text, start integer, count integer | func
(4 rows)
discussion: https://postgr.es/m/CACJufxHTBkymh06D4mGKNe1YfRNFN+gFBybmygWk=PtMqu00LQ@mail.gmail.com
---
doc/src/sgml/func.sgml | 111 +++++++++++++++++++++--
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 +++
3 files changed, 118 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2ab5661602c..88ca1ef3f54 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3806,6 +3806,58 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching POSIX regular expression; see
+ <xref linkend="functions-posix-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '...$')</literal>
+ <returnvalue>mas</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>pattern</parameter> <type>text</type>, <parameter>escape_character </parameter> <type>text</type>)
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the first substring matching <acronym>SQL</acronym> regular expression;
+ see <xref linkend="functions-similarto-regexp"/>.
+ </para>
+ <para>
+ <literal>substring('Thomas', '%#"o_a#"_', '#')</literal>
+ <returnvalue>oma</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>string</parameter> <type>text</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>string</parameter> starting at
+ the <parameter>start</parameter>'th character,
+ and stopping after <parameter>count</parameter> characters if that is
+ specified.
+ </para>
+
+ <para>
+ <literal>substring('Thomas', 2, 3)</literal>
+ <returnvalue>hom</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -4811,6 +4863,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<returnvalue>\x5678</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bytes</parameter> <type>bytea</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bytea</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bytes</parameter> starting at
+ the <parameter>start</parameter>'th byte,
+ and stopping after <parameter>count</parameter> bytes if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring('\x1234567890'::bytea, 3, 2)</literal>
+ <returnvalue>\x5678</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -5353,6 +5426,26 @@ cast(-1234 as bytea) <lineannotation>\xfffffb2e</lineannotation>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>substring</primary>
+ </indexterm>
+ <function>substring</function> ( <parameter>bits</parameter> <type>bit</type>, <parameter>start</parameter> <type>integer</type> <optional>, <parameter>count</parameter> <type>integer</type> </optional> )
+ <returnvalue>bit</returnvalue>
+ </para>
+ <para>
+ Extracts the substring of <parameter>bits</parameter> starting at
+ the <parameter>start</parameter>'th bit,
+ and stopping after <parameter>count</parameter> bits if that is
+ specified.
+ </para>
+ <para>
+ <literal>substring(B'110010111111', 3, 2)</literal>
+ <returnvalue>00</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -5816,7 +5909,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
</synopsis>
or as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape_character</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6020,11 +6113,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
</para>
<para>
- The <function>substring</function> function with two parameters,
- <function>substring(<replaceable>string</replaceable> from
- <replaceable>pattern</replaceable>)</function>, provides extraction of a
- substring
- that matches a POSIX regular expression pattern. It returns null if
+ The <function>substring</function> function with two parameters provides extraction of a
+ substring that matches a POSIX regular expression pattern.
+ It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+ It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+ It returns null if
there is no match, otherwise the first portion of the text that matched the
pattern. But if the pattern contains any parentheses, the portion
of the text that matched the first parenthesized subexpression (the
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..5ea9d786b60 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape_character text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 890822eaf79..e63a8279e16 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3686,6 +3687,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3696,9 +3698,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4116,6 +4120,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4145,6 +4150,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6239,15 +6245,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6436,9 +6446,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape_character}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-03-31 22:22 David G. Johnston <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: David G. Johnston @ 2025-03-31 22:22 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Tue, Mar 18, 2025 at 9:04 PM jian he <[email protected]> wrote:
>
> new patch attached.
>
>
I've done v4 with a delta patch.
Decided to standardize on calling the SQL Similar To regular expression
escape replaceable "escape" everywhere.
Instead of fully documenting the obsolete syntax I added a note explaining
the keyword choice difference. Removed mention of it completely from the
Pattern Matching portion of the documentation - that section has enough
going on.
I also add "Same as" references for the two pairs of entries. Not married
to them but they do seem warranted; having Pattern Matching be required
reading to make that connection seems undesirable.
David J.
Attachments:
[text/x-patch] v4-0001-v3-0001-substring.patch (11.9K, 3-v4-0001-v3-0001-substring.patch)
download | inline diff:
From b2f64615da9522427a2e2662b1d060ffed97088c Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
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
[text/x-patch] v4-0002-v3-0002-delta.patch (8.6K, 4-v4-0002-v3-0002-delta.patch)
download | inline diff:
From 182f0b447d7860fe5db62863da0d7f582a0abfb9 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-04-01 04:11 jian he <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: jian he @ 2025-04-01 04:11 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-hackers
On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email protected]> wrote:
>
> On Tue, Mar 18, 2025 at 9:04 PM jian he <[email protected]> wrote:
>>
>>
>> new patch attached.
>>
>
> I've done v4 with a delta patch.
>
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference. Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries. Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>
your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch
for example:
- <para role="func_signature">
+ <para role="func_signature"><s>
<function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
- <returnvalue>text</returnvalue>
+ <returnvalue>text</returnvalue></s>
can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch.
because I tried
git am
On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email protected]> wrote:
>
> On Tue, Mar 18, 2025 at 9:04 PM jian he <[email protected]> wrote:
>>
>>
>> new patch attached.
>>
>
> I've done v4 with a delta patch.
>
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference. Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries. Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>
your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch
for example:
- <para role="func_signature">
+ <para role="func_signature"><s>
<function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
- <returnvalue>text</returnvalue>
+ <returnvalue>text</returnvalue></s>
can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch.
because I tried git am
On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
<[email protected]> wrote:
>
> On Tue, Mar 18, 2025 at 9:04 PM jian he <[email protected]> wrote:
>>
>>
>> new patch attached.
>>
>
> I've done v4 with a delta patch.
>
> Decided to standardize on calling the SQL Similar To regular expression escape replaceable "escape" everywhere.
>
> Instead of fully documenting the obsolete syntax I added a note explaining the keyword choice difference. Removed mention of it completely from the Pattern Matching portion of the documentation - that section has enough going on.
>
> I also add "Same as" references for the two pairs of entries. Not married to them but they do seem warranted; having Pattern Matching be required reading to make that connection seems undesirable.
>
can not build docs based on your v4-0001.
your v4-0001-v3-0001-substring.patch is not the same as my
v3-0001-add-argument-name-to-function-substring-and-subst.patch
for example:
- <para role="func_signature">
+ <para role="func_signature"><s>
<function>substring</function> (
<parameter>string</parameter> <type>text</type>
<literal>FROM</literal> <parameter>pattern</parameter>
<type>text</type> <literal>FOR</literal> <parameter>escape</parameter>
<type>text</type> )
- <returnvalue>text</returnvalue>
+ <returnvalue>text</returnvalue></s>
because I tried
git am v3-0001-add-argument-name-to-function-substring-and-subst.patch.
patch -p1 < v4-0002-v3-0002-delta.patch
Then there are several places that differ, it doesn't seem easy to
resolve the difference.
Can you make sure v4-0001-v3-0001-substring.patch the same as
v3-0001-add-argument-name-to-function-substring-and-subst.patch,
then I can review your delta patch.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: add function argument name to substring and substr
@ 2025-04-01 04:59 David G. Johnston <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2025-04-01 04:59 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
On Mon, Mar 31, 2025 at 9:12 PM jian he <[email protected]> wrote:
> On Tue, Apr 1, 2025 at 6:22 AM David G. Johnston
> <[email protected]> wrote:
> >
> > On Tue, Mar 18, 2025 at 9:04 PM jian he <[email protected]>
> wrote:
> >>
> >>
> >> new patch attached.
> >>
> >
> > I've done v4 with a delta patch.
>
> your v4-0001-v3-0001-substring.patch is not the same as my
> v3-0001-add-argument-name-to-function-substring-and-subst.patch
>
>
Sorry about that. v5 attached. Confirmed with diff the v3 and v5 0001 so
we should be good.
David J.
Attachments:
[text/x-patch] v5-0001-v3-0001-substring.patch (11.0K, 3-v5-0001-v3-0001-substring.patch)
download | inline diff:
From 507064e643b54d2bebd7689acd3dde60d230e328 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
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
[text/x-patch] v5-0002-v3-delta.patch (8.5K, 4-v5-0002-v3-delta.patch)
download | inline diff:
From 6ef3ff43f133dd6be423c4a567afda10f1c64988 Mon Sep 17 00:00:00 2001
From: "David G. Johnston" <[email protected]>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-04-01 04:59 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-22 06:21 add function argument name to substring and substr jian he <[email protected]>
2025-01-27 02:20 ` jian he <[email protected]>
2025-03-17 06:48 ` vignesh C <[email protected]>
2025-02-17 22:12 ` David G. Johnston <[email protected]>
2025-03-18 03:20 ` jian he <[email protected]>
2025-03-19 00:18 ` David G. Johnston <[email protected]>
2025-03-19 01:19 ` jian he <[email protected]>
2025-03-19 01:35 ` David G. Johnston <[email protected]>
2025-03-19 04:04 ` jian he <[email protected]>
2025-03-31 22:22 ` David G. Johnston <[email protected]>
2025-04-01 04:11 ` jian he <[email protected]>
2025-04-01 04:59 ` David G. Johnston <[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