On Tue, Feb 18, 2025 at 6:13 AM David G. Johnston
<david.g.johns...@gmail.com> wrote:
>
> Table 9.9 limits itself to those functions defined in the SQL standard; which
> are basically the ones that use keywords instead of commas.
>
> The substring(string, start, count) function you note is already covered in
> Table 9.10 but we spell it substr(...)
>
> I don't think adding yet more spellings of this same function is warranted or
> desirable at this point.
ok.
> I'd maybe add a note if substring(,,,) works to substr saying that substring
> is a valid alias. I could be convinced to just document though.
>
it seems already in the doc.
substr ( string text, start integer [, count integer ] ) → text
Extracts the substring of string starting at the start'th character,
and extending for count characters if that is specified. (Same as
substring(string from start for count).)
substr ( bytes bytea, start integer [, count integer ] ) → bytea
Extracts the substring of bytes starting at the start'th byte, and
extending for count bytes if that is specified. (Same as
substring(bytes from start for count).)
new patch attached.
main changes:
1. change 3 argument func argument from
(string text, pattern text, escape_character text)
to
(string text, pattern text, escape text)
2. add synopsis section in 9.7.3. POSIX Regular Expressions for
function substring.
we only have the synopsis section for function substring in 9.7.2
section, now add it to 9.7.3.
also add an example about using named natation call substring:
substring(string=>'foobar', pattern=> 'o.b')
the patch is small, I just put the
\df substring
\df substr
(before and after patch) output into the commit message.
From f0b49ccd27d41ff1bd1619284656efcb1d3c3e0f Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Tue, 18 Mar 2025 11:13:11 +0800
Subject: [PATCH v2 1/1] add argument name to function substring and substr
visual changes.
before
\df substr
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+-------------------------+------
pg_catalog | substr | bytea | bytea, integer | func
pg_catalog | substr | bytea | bytea, integer, integer | func
pg_catalog | substr | text | text, integer | func
pg_catalog | substr | text | text, integer, integer | func
(4 rows)
now
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+-------------------------------------------+------
pg_catalog | substr | bytea | bytes bytea, start integer | func
pg_catalog | substr | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substr | text | string text, start integer | func
pg_catalog | substr | text | string text, start integer, count integer | func
(4 rows)
before
\df substring
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+-------------------------+------
pg_catalog | substring | bit | bit, integer | func
pg_catalog | substring | bit | bit, integer, integer | func
pg_catalog | substring | bytea | bytea, integer | func
pg_catalog | substring | bytea | bytea, integer, integer | func
pg_catalog | substring | text | text, integer | func
pg_catalog | substring | text | text, integer, integer | func
pg_catalog | substring | text | text, text | func
pg_catalog | substring | text | text, text, text | func
(8 rows)
now
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------+------------------+-------------------------------------------+------
pg_catalog | substring | bit | bits bit, start integer | func
pg_catalog | substring | bit | bits bit, start integer, count integer | func
pg_catalog | substring | bytea | bytes bytea, start integer | func
pg_catalog | substring | bytea | bytes bytea, start integer, count integer | func
pg_catalog | substring | text | string text, pattern text | func
pg_catalog | substring | text | string text, pattern text, escape text | func
pg_catalog | substring | text | string text, start integer | func
pg_catalog | substring | text | string text, start integer, count integer | func
(8 rows)
---
doc/src/sgml/func.sgml | 23 +++++++++++++++--------
src/backend/catalog/system_functions.sql | 2 +-
src/include/catalog/pg_proc.dat | 12 ++++++++++++
3 files changed, 28 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1c3810e1a04..be47e06727d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5816,7 +5816,7 @@ substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceab
</synopsis>
or as a plain three-argument function:
<synopsis>
-substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape-character</replaceable>)
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>, <replaceable>escape</replaceable>)
</synopsis>
As with <literal>SIMILAR TO</literal>, the
specified pattern must match the entire data string, or else the
@@ -6020,11 +6020,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
</para>
<para>
- The <function>substring</function> function with two parameters,
- <function>substring(<replaceable>string</replaceable> from
- <replaceable>pattern</replaceable>)</function>, provides extraction of a
- substring
- that matches a POSIX regular expression pattern. It returns null if
+ The <function>substring</function> function with two parameters provides extraction of a
+ substring that matches a POSIX regular expression pattern.
+ It has syntax:
+<synopsis>
+substring(<replaceable>string</replaceable> from <replaceable>pattern</replaceable>)
+</synopsis>
+ It can also written as a plain two-argument function:
+<synopsis>
+substring(<replaceable>string</replaceable>, <replaceable>pattern</replaceable>)
+</synopsis>
+ It returns null if
there is no match, otherwise the first portion of the text that matched the
pattern. But if the pattern contains any parentheses, the portion
of the text that matched the first parenthesized subexpression (the
@@ -6039,8 +6045,9 @@ substring('foobar' similar '#"o_b#"%' escape '#') <lineannotation>NULL</linea
<para>
Some examples:
<programlisting>
-substring('foobar' from 'o.b') <lineannotation>oob</lineannotation>
-substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
+substring('foobar' from 'o.b') <lineannotation>oob</lineannotation>
+substring(string=>'foobar', pattern=> 'o.b') <lineannotation>oob</lineannotation>
+substring('foobar' from 'o(.)b') <lineannotation>o</lineannotation>
</programlisting>
</para>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..544b549ae74 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -42,7 +42,7 @@ CREATE OR REPLACE FUNCTION rpad(text, integer)
IMMUTABLE PARALLEL SAFE STRICT COST 1
RETURN rpad($1, $2, ' ');
-CREATE OR REPLACE FUNCTION "substring"(text, text, text)
+CREATE OR REPLACE FUNCTION "substring"(string text, pattern text, escape text)
RETURNS text
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT COST 1
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 890822eaf79..1e023c6465b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3668,6 +3668,7 @@
prosrc => 'rtrim' },
{ oid => '877', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '878', descr => 'map a set of characters appearing in string',
proname => 'translate', prorettype => 'text', proargtypes => 'text text text',
@@ -3686,6 +3687,7 @@
prosrc => 'rtrim1' },
{ oid => '883', descr => 'extract portion of string',
proname => 'substr', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '884', descr => 'trim selected characters from both ends of string',
proname => 'btrim', prorettype => 'text', proargtypes => 'text text',
@@ -3696,9 +3698,11 @@
{ oid => '936', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4 int4',
+ proargnames => '{string, start, count}',
prosrc => 'text_substr' },
{ oid => '937', descr => 'extract portion of string',
proname => 'substring', prorettype => 'text', proargtypes => 'text int4',
+ proargnames => '{string, start}',
prosrc => 'text_substr_no_len' },
{ oid => '2087',
descr => 'replace all occurrences in string of old_substr with new_substr',
@@ -4116,6 +4120,7 @@
prosrc => 'bitcat' },
{ oid => '1680', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4 int4',
+ proargnames => '{bits, start, count}',
prosrc => 'bitsubstr' },
{ oid => '1681', descr => 'bitstring length',
proname => 'length', prorettype => 'int4', proargtypes => 'bit',
@@ -4145,6 +4150,7 @@
prosrc => 'bitposition' },
{ oid => '1699', descr => 'extract portion of bitstring',
proname => 'substring', prorettype => 'bit', proargtypes => 'bit int4',
+ proargnames => '{bits, start}',
prosrc => 'bitsubstr_no_len' },
{ oid => '3030', descr => 'substitute portion of bitstring',
@@ -6239,15 +6245,19 @@
prosrc => 'byteacat' },
{ oid => '2012', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea',
+ proargnames => '{bytes, start, count}',
proargtypes => 'bytea int4 int4', prosrc => 'bytea_substr' },
{ oid => '2013', descr => 'extract portion of string',
proname => 'substring', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2085', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4 int4',
+ proargnames => '{bytes, start, count}',
prosrc => 'bytea_substr' },
{ oid => '2086', descr => 'extract portion of string',
proname => 'substr', prorettype => 'bytea', proargtypes => 'bytea int4',
+ proargnames => '{bytes, start}',
prosrc => 'bytea_substr_no_len' },
{ oid => '2014', descr => 'position of substring',
proname => 'position', prorettype => 'int4', proargtypes => 'bytea bytea',
@@ -6436,9 +6446,11 @@
{ oid => '2073', descr => 'extract text matching regular expression',
proname => 'substring', prorettype => 'text', proargtypes => 'text text',
+ proargnames => '{string, pattern}',
prosrc => 'textregexsubstr' },
{ oid => '2074', descr => 'extract text matching SQL regular expression',
proname => 'substring', prolang => 'sql', prorettype => 'text',
+ proargnames => '{string, pattern, escape}',
proargtypes => 'text text text', prosrc => 'see system_functions.sql' },
{ oid => '2075', descr => 'convert int8 to bitstring',
--
2.34.1
