Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuh9J-00AnfS-OT for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Mar 2025 00:19:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tuh9H-008S2A-TR for pgsql-hackers@arkaria.postgresql.org; Wed, 19 Mar 2025 00:19:07 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tuh9H-008S22-FD for pgsql-hackers@lists.postgresql.org; Wed, 19 Mar 2025 00:19:07 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tuh9F-003bDY-0k for pgsql-hackers@postgresql.org; Wed, 19 Mar 2025 00:19:06 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-2bcceee7b40so5251866fac.3 for ; Tue, 18 Mar 2025 17:19:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1742343544; x=1742948344; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=RDA9/zNZtMUZ+cbqLf8/t406sYYd585rcNMOstXHXO4=; b=WLijthxjOlHgBDwKVS+OXHci61VRR3Lbr2yUGb5a2Gty7eBYMJb4Rx/xtHg6WYfsTx XMAJpHRQyqOcsm4vUA6dC0WJcSqrdTaj+Fguion1COCfUeE7bHIFcXkPNCMYWLzpVKR2 Oury/1IiC+GZq1cwg2C9viANmP2mlKmMjgOAZYHuOzNoO582E99hcdw3rUOq3v7WZcFK ts2zKmgEWxyA/5XIvzR1SYH6cnRnedHgc9g7t3TdQ7Xorjz0w0SNL1OhOjMq795qUPCS hfaabDKPPbN/VAJDQxOC7plP7PQkuO7XlMtEjHfNXW9oJ0fG56OvHOluikH6nGVXPqMS 5DIg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742343544; x=1742948344; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=RDA9/zNZtMUZ+cbqLf8/t406sYYd585rcNMOstXHXO4=; b=ja9/ABy7NHQtQvUm0Qie7PxOP6GxKX8VlATuTCKkG/bK/ULPwpFerYW5J+5nPK1xza zq0rgZRiILOPzSg7Zwu/hJGOWb3ZN/5dpVf0EKmKsUFF+1T6o18tutwIqcQX3OXXSXyU qLOJv46r8oVdvYQ+K89Vhat9JjvgdAlL6fe45jFwjsyHK0KjKRED7XnEXiE/v2ufsW3p M+4Y5UHh9UCvn8wc0ZggpmSQJNrSHge9uP80iZK58IVOxc8BgCVPsacey+JGSzx5kNPd 3UZrIBdAz3QybYNQSvzQfQCPC1rY47Wy3xhamd8t+nVnvxxwX4LeS7/br2Hpmu4yXIg5 uHLQ== X-Gm-Message-State: AOJu0Ywd++vu5qwVNCNvT+LdT0SP7faM7G9TvAEtxJaa5BgF3EL2Mvr2 LYNGmLUiXljCIThkHWa/Sq2dEvyVbCZMtnQkP4P4nl5cwRHug2DP1Djn0xXwksXZC3DKM+RE+3N tqLMK9QRRpF00m+tWlqELRyxnUrA= X-Gm-Gg: ASbGncuN2bHkYjY0WCNYcLlHQlzHAYUs0/1W58uaI7FEOPaq1OUKczSonBgtWlzANPR l6WYf3M45FVSvJnDAcQneZWXrZ2V9p164pmNID8Y4BSW/qMZkzUnwldry1XxYMvv4CSWWwBucEo Z4cI3vKQdI7Wy+g5YlEwEnGeUWK2dC/NpBfSc= X-Google-Smtp-Source: AGHT+IEDgvccmgcipurzNhwqyljkUblmQ7FnJ2R7YTop+ecqEqNWYzgymIHYnpuuUgUUeU9QqSzLA/OpIkJSrzRpAE8= X-Received: by 2002:a05:6870:479d:b0:2bc:7c3d:1918 with SMTP id 586e51a60fabf-2c7455a7707mr343021fac.21.1742343544452; Tue, 18 Mar 2025 17:19:04 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Tue, 18 Mar 2025 17:18:28 -0700 X-Gm-Features: AQ5f1JpI41kf6eRUIhqjQu2j5_JXr_w4FbP3ipOmZnFD2tqV3ICK0Cz2TF1av0M Message-ID: Subject: Re: add function argument name to substring and substr To: jian he Cc: PostgreSQL-development Content-Type: multipart/alternative; boundary="0000000000000b403d0630a6f868" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000b403d0630a6f868 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Mar 17, 2025 at 8:20=E2=80=AFPM jian he wrote: > On Tue, Feb 18, 2025 at 6:13=E2=80=AFAM David G. Johnston > wrote: > > > > Table 9.9 limits itself to those functions defined in the SQL standard; > which are basically the ones that use keywords instead of commas. > > > > The substring(string, start, count) function you note is already covere= d > in Table 9.10 but we spell it substr(...) > > > > I don't think adding yet more spellings of this same function is > warranted or desirable at this point. > > ok. > > > I'd maybe add a note if substring(,,,) works to substr saying that > substring is a valid alias. I could be convinced to just document though= . > > > it seems already in the doc. > > substr ( string text, start integer [, count integer ] ) =E2=86=92 text > Extracts the substring of string starting at the start'th character, > and extending for count characters if that is specified. (Same as > substring(string from start for count).) > > substr ( bytes bytea, start integer [, count integer ] ) =E2=86=92 bytea > Extracts the substring of bytes starting at the start'th byte, and > extending for count bytes if that is specified. (Same as > substring(bytes from start for count).) > > > new patch attached. > main changes: > 1. change 3 argument func argument from > (string text, pattern text, escape_character text) > to > (string text, pattern text, escape text) > Why? It can only be one character so that existing name seems well chosen. postgres=3D# select substring('123^^,123',',','^^'); ERROR: invalid escape string HINT: Escape string must be empty or one character. CONTEXT: SQL function "substring" statement 1 > 2. add synopsis section in 9.7.3. POSIX Regular Expressions for > function substring. > we only have the synopsis section for function substring in 9.7.2 > section, now add it to 9.7.3. > I'd probably try and resolve that the other way...point the reader to the reference page for the function if they want to see syntax. I'm mixed on (leaning against) whether choosing this place to demonstrate all the possible spellings is the best. also add an example about using named natation call substring: > substring(string=3D>'foobar', pattern=3D> 'o.b') > I'm already pulling my hair out at this showing all the insanity that exists without adding this to the mix. The vast majority of examples throughout the manual use traditional function call syntax func_name(arg1, arg2, etc.); I'd rather keep with convention than start to scatter about alternative syntax choices just to give the random reader who happens upon this fairly esoteric part of the manual the benefit of seeing their options. If that is a goal, then I'd suggest spending some time in our Tutorial adding some more examples with these alternative forms to people looking to be exposed to new things in the place they'd go to look for them. They probably won't learn about them from the Syntax section. On the plus side, I agree now we should add: substring(string text, pattern text[, escape-character text]) to Table 9.10 I'd also rename escape to escape-character in the other SQL substring function synopses. The RegEx page got that part correct. Do as little or as much with the RegEx section as you'd like, though it seems like separate material from $subject. The page seems to already use replaceable names instead of data types so on that score it should be unaffected if we've chosen the same names. Food for thought, it seems a bit redundant to name the first argument basically the same as the data type. I was thinking that "content" would be a better choice. This is basically a polymorphic function where all the inputs are the same thing just having different types - and that thing is being "content". David J. --0000000000000b403d0630a6f868 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Mar 17, 2025 at 8:20=E2=80=AFPM jian he <jian.universality@gmail.com>= ; wrote:
On Tue, Feb 18, 2025 at = 6:13=E2=80=AFAM David G. Johnston
<david.g= .johnston@gmail.com> wrote:
>
> Table 9.9 limits itself to those functions defined in the SQL standard= ; which are basically the ones that use keywords instead of commas.
>
> The substring(string, start, count) function you note is already cover= ed in Table 9.10 but we spell it substr(...)
>
> I don't think adding yet more spellings of this same function is w= arranted or desirable at this point.

ok.

> I'd maybe add a note if substring(,,,) works to substr saying that= substring is a valid alias.=C2=A0 I could be convinced to just document th= ough.
>
it seems already in the doc.

substr ( string text, start integer [, count integer ] ) =E2=86=92 text
Extracts the substring of string starting at the start'th character, and extending for count characters if that is specified. (Same as
substring(string from start for count).)

substr ( bytes bytea, start integer [, count integer ] ) =E2=86=92 bytea Extracts the substring of bytes starting at the start'th byte, and
extending for count bytes if that is specified. (Same as
substring(bytes from start for count).)


new patch attached.
main changes:
1. change 3 argument func argument from
(string text, pattern text, escape_character text)
to
(string text, pattern text, escape text)

= Why?=C2=A0 It can only be one character so that existing name seems well ch= osen.

postgres=3D# select substring('123^^,123'= ;,',','^^');
ERROR: =C2=A0invalid escape string
HINT:= =C2=A0Escape string must be empty or one character.
CONTEXT: =C2=A0SQL = function "substring" statement 1


2. add synopsis section in 9.7.3. POSIX Regular Expressions for
function substring.
we only have the synopsis section for function substring in 9.7.2
section, now add it to 9.7.3.

I'd pro= bably try and resolve that the other way...point the reader to the referenc= e page for the function if they want to see syntax.=C2=A0 I'm mixed on = (leaning against) whether choosing this place to demonstrate all the possib= le spellings is the best.

also add an example about using named natation call substring:
substring(string=3D>'foobar', pattern=3D> 'o.b')
<= /blockquote>

I'm already pulling my hair out at this s= howing all the insanity that exists without adding this to the mix.

The vast majority of examples throughout the manual use tradi= tional function call syntax=C2=A0 func_name(arg1, arg2, etc.);=C2=A0 I'= d rather keep with convention than start to scatter about alternative synta= x choices just to give the random reader who happens upon this fairly esote= ric part of the manual the benefit of seeing their options.=C2=A0 If that i= s a goal, then I'd suggest spending some time in our Tutorial adding so= me more examples with these alternative forms to people looking to be expos= ed to new things in the place they'd go to look for them.=C2=A0 They pr= obably won't learn about them from the Syntax section.

On the plus side, I agree now we should add:
substring(string text, pattern = text[, escape-character=C2=A0text])
to Table 9.10

I'd also rename escape to escape-character in = the other SQL substring function synopses.=C2=A0 The RegEx page got that pa= rt correct.

Do a= s little or as much with the RegEx section as you'd like, though it see= ms like separate material from $subject.=C2=A0 The page seems to already us= e replaceable names instead of data types so on that score it should be una= ffected if we've chosen the same names.


=
Food for thought, it seems a bit redundant to name the first argument ba= sically the same as the data type.=C2=A0 I was thinking that "content&= quot; would be a better choice.=C2=A0 This is basically a polymorphic funct= ion where all the inputs are the same thing just having different types - a= nd that thing is being "content".

David J.
--0000000000000b403d0630a6f868--