From f0b49ccd27d41ff1bd1619284656efcb1d3c3e0f Mon Sep 17 00:00:00 2001 From: jian he 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(string from pattern or as a plain three-argument function: -substring(string, pattern, escape-character) +substring(string, pattern, escape) As with SIMILAR TO, the specified pattern must match the entire data string, or else the @@ -6020,11 +6020,17 @@ substring('foobar' similar '#"o_b#"%' escape '#') NULL - The substring function with two parameters, - substring(string from - pattern), provides extraction of a - substring - that matches a POSIX regular expression pattern. It returns null if + The substring function with two parameters provides extraction of a + substring that matches a POSIX regular expression pattern. + It has syntax: + +substring(string from pattern) + + It can also written as a plain two-argument function: + +substring(string, pattern) + + 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 '#') NULL Some examples: -substring('foobar' from 'o.b') oob -substring('foobar' from 'o(.)b') o +substring('foobar' from 'o.b') oob +substring(string=>'foobar', pattern=> 'o.b') oob +substring('foobar' from 'o(.)b') o 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