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.
From 835dc9364f9f986445b77af8fa761e7577cbbdff Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
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=ptmqu0...@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
