substring synopsis section, third argument is optional doc didn't show that

jian he <jian.universality@gmail.com> writes:

https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

"""
or as a plain three-argument function:
substring(string, pattern, escape-character)
"""

but here "escape-character" is optional.

substring(string, pattern [,escape-character])
would be more accurate.

No, the text is correct as written. substring(text, text) is a
completely different function that implements POSIX regular
expressions, not SQL regular expressions. It's described in
the next section (9.7.3). For example,

regression=# select substring('foobar', 'o.b');
substring
-----------
oob
(1 row)

regression=# select substring('foobar', 'o.b', '');
substring
-----------

(1 row)

because '.' is a metacharacter in POSIX but not SQL regexps.

regards, tom lane

On Wed, Jan 22, 2025 at 12:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

jian he <jian.universality@gmail.com> writes:

https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

"""
or as a plain three-argument function:
substring(string, pattern, escape-character)
"""

but here "escape-character" is optional.

substring(string, pattern [,escape-character])
would be more accurate.

No, the text is correct as written. substring(text, text) is a
completely different function that implements POSIX regular
expressions, not SQL regular expressions. It's described in
the next section (9.7.3). For example,

regression=# select substring('foobar', 'o.b');
substring
-----------
oob
(1 row)

regression=# select substring('foobar', 'o.b', '');
substring
-----------

(1 row)

because '.' is a metacharacter in POSIX but not SQL regexps.

Thanks for the explanation.

in section 9.7.2,
substring(string, pattern, escape-character)
the pattern must match the entire data string. (SQL standard)

in section 9.7.3.
substring(string, pattern)
the pattern only needs part of the data string. (POSIX)

I think the above is the main/big difference?

in 9.7.2 do you think it's worthwhile changing it to
""
As with SIMILAR TO, substring(string, pattern, escape-character)
the specified pattern must match the entire data string, or else the
function fails and returns null.
""
?

On Tue, Jan 21, 2025 at 11:29 PM jian he <jian.universality@gmail.com>
wrote:

in 9.7.2 do you think it's worthwhile changing it to

""

As with SIMILAR TO, substring(string, pattern, escape-character)
the specified pattern must match the entire data string, or else the
function fails and returns null.
""
?

Making reference to any one of the three listed function signatures here
doesn't seem to provide value. If anything I'd write:

"As with SIMILAR TO, substring matches the specified pattern to the entire
data string, returning null otherwise."

I would avoid saying that the function fails in any situation that doesn't
produce an actual error. The transition of "match everything or returns
null" can be bike-shedded though.

David J.

On Tue, Feb 18, 2025 at 7:06 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Jan 21, 2025 at 11:29 PM jian he <jian.universality@gmail.com> wrote:

in 9.7.2 do you think it's worthwhile changing it to

""
As with SIMILAR TO, substring(string, pattern, escape-character)
the specified pattern must match the entire data string, or else the
function fails and returns null.
""
?

Making reference to any one of the three listed function signatures here doesn't seem to provide value. If anything I'd write:

"As with SIMILAR TO, substring matches the specified pattern to the entire data string, returning null otherwise."

I would avoid saying that the function fails in any situation that doesn't produce an actual error. The transition of "match everything or returns null" can be bike-shedded though.

thinking about it.
I think the current wording
"As with SIMILAR TO, the specified pattern must match the entire data
string, or else the function fails and returns null"
is fine.

I guess my complaint is that the above sentence is not as explicit as
the 9.7.3 section description.
"
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 there is no match, otherwise the first portion of
the text that matched the pattern.
"