Обсуждение: substring synopsis section, third argument is optional doc didn't show that

hi.
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.
then we may also need to rephrase
"or as a plain three-argument function:"

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.
""
?