From 182f0b447d7860fe5db62863da0d7f582a0abfb9 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" 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 Extracts the first substring matching POSIX regular expression; see - . + . (Same as + substring(string text, pattern text).) substring('Thomas' from '...$') @@ -2850,20 +2851,20 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in substring ( string text SIMILAR pattern text ESCAPE escape text ) text - - substring ( string text FROM pattern text FOR escape text ) - text - Extracts the first substring matching SQL regular expression; - see . The first form has - been specified since SQL:2003; the second form was only in SQL:1999 - and should be considered obsolete. + see . (Same as + substring(string text, pattern text, escape text).) substring('Thomas' similar '%#"o_a#"_' escape '#') oma - + + + Obsolescence note: SQL:1999 introduced this function with FROM + and FOR as the keywords but switched to this in SQL:2003. + + @@ -3814,7 +3815,8 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in Extracts the first substring matching POSIX regular expression; see - . + . (Same as + substring(string text FROM pattern text).) substring('Thomas', '...$') @@ -3824,12 +3826,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in - substring ( string text, pattern text, escape_character text) + substring ( string text, pattern text, escape text) text Extracts the first substring matching SQL regular expression; - see . + see . (Same as + substring(string text SIMILAR pattern text ESCAPE escape text).) substring('Thomas', '%#"o_a#"_', '#') @@ -5593,8 +5596,8 @@ cast(-44 as bit(12)) 111111010100 -string LIKE pattern ESCAPE escape-character -string NOT LIKE pattern ESCAPE escape-character +string LIKE pattern ESCAPE escape +string NOT LIKE pattern ESCAPE escape @@ -5766,8 +5769,8 @@ cast(-44 as bit(12)) 111111010100 -string SIMILAR TO pattern ESCAPE escape-character -string NOT SIMILAR TO pattern ESCAPE escape-character +string SIMILAR TO pattern ESCAPE escape +string NOT SIMILAR TO pattern ESCAPE escape @@ -5901,15 +5904,11 @@ cast(-44 as bit(12)) 111111010100 regular expression pattern. The function can be written according to standard SQL syntax: -substring(string similar pattern escape escape-character) - - or using the now obsolete SQL:1999 syntax: - -substring(string from pattern for escape-character) +substring(string SIMILAR pattern ESCAPE escape) - or as a plain three-argument function: + It can also written 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 @@ -6115,9 +6114,9 @@ substring('foobar' similar '#"o_b#"%' escape '#') NULL The substring 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: -substring(string from pattern) +substring(string FROM pattern) It can also written as a plain two-argument function: 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