agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedsubstring synopsis section, third argument is optional doc didn't show that
5+ messages / 3 participants
[nested] [flat]
* substring synopsis section, third argument is optional doc didn't show that
@ 2025-01-21 08:00 jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: jian he @ 2025-01-21 08:00 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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:"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substring synopsis section, third argument is optional doc didn't show that
@ 2025-01-21 16:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-01-21 16:53 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substring synopsis section, third argument is optional doc didn't show that
@ 2025-01-22 06:28 jian he <jian.universality@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: jian he @ 2025-01-22 06:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs@lists.postgresql.org
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.
""
?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substring synopsis section, third argument is optional doc didn't show that
@ 2025-02-17 23:05 David G. Johnston <david.g.johnston@gmail.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2025-02-17 23:05 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substring synopsis section, third argument is optional doc didn't show that
@ 2025-02-23 06:27 jian he <jian.universality@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: jian he @ 2025-02-23 06:27 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs@lists.postgresql.org
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.
"
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-23 06:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-21 08:00 substring synopsis section, third argument is optional doc didn't show that jian he <jian.universality@gmail.com>
2025-01-21 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-22 06:28 ` jian he <jian.universality@gmail.com>
2025-02-17 23:05 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-23 06:27 ` jian he <jian.universality@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox