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 1tk9Mz-000dcR-3o for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Feb 2025 22:13:41 +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 1tk9Mw-00421d-VP for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Feb 2025 22:13:38 +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 1tk9Mw-0041zj-Gi for pgsql-hackers@lists.postgresql.org; Mon, 17 Feb 2025 22:13:38 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tk9Mu-001Og0-1l for pgsql-hackers@postgresql.org; Mon, 17 Feb 2025 22:13:37 +0000 Received: by mail-oi1-x22b.google.com with SMTP id 5614622812f47-3f3e8e959d5so1482282b6e.0 for ; Mon, 17 Feb 2025 14:13:36 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1739830416; x=1740435216; 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=qeJXKRtY86Tjmd9lC7cEo3kom69YCPXcBJacE7Bpx5A=; b=nX6FQsj1uIA1KMPYVpuLxWFinbCxM/4dkGATByW74C9ptdYvp1Cy2NB5MqNBFmOvQH +3C6lagEyRKsf+RvVQXAIQoYAAViooHFjMsK/ObIiAAbHipzSLLjIRPLHrn9SZd1oIYm F9c1GtfvxRw4EvB7ZXtpz+Y6XPDIO1OD9L7NS19CQN9+MZj6LO0ZiLvmMPikPVvsCIxR JdIQ5Z3VLldylAFjP+UFaRhrgE09fNVcE0CwsksY4/H3s7cMKqRCGGZKJ33UJnYQGaM6 qa/jdr2GQ41BPFb0jRzTCfQfPuzOKFEf7ZRT1U9sIeEnkuqWLhMI25grfjkdQOMwhFTX 3Icw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1739830416; x=1740435216; 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=qeJXKRtY86Tjmd9lC7cEo3kom69YCPXcBJacE7Bpx5A=; b=bOAJooZe4ZMHX0+MX18AnEpk8cb7aT8A4sEg0Uvc2UDqLIf1E08p4GrDREaTSTm7Qg XN4F7K4wKflgcG9AzB0b8gjOxfsbZBEw5ZQH/gayhB+j5PucNqYCRedK/bL/zCj3vbiv wLCycfCYIrisrK9FcCofqqcQdekqalA6aHEoqfyQCjWRfNJ6ZaF76rfOspxNgaDmQriv 9/hLxD4N0G9mB5QyMntzlvh9EnAkUor8eCMlRVXezsBkJCJadThHwVQmqkeR9mX3zuXn YtU1oeFHTfI18CXmuG/PYAMH+UBcbq5lSurYWCe6pkvJjvYzI+WmIWXUh+qaE7VGqwNv dg9g== X-Gm-Message-State: AOJu0YzYVH8VeQk7BzFkYShxWKhAv3j6zX05GyBKqb0MYLpd0x3GXC2A vXlLFXqRfkOvoHDSihwn0SW6K+mw2TrEX3gxNn0xkvdqPJxAotbSm/ACVa/i/Hcz90P729fxhUr 52ctUg069EVwrHkbN8qZKQ7BNhTI= X-Gm-Gg: ASbGncs3OOS6PsbmWQGr7lSfLygPTrCl3rwwBLMN1NKFpo9eXs62I0Y5zbsoocpzKiz aE8spTxNUUFKG9Lq57KhGcsAZ9p4WAEd+DBuZS0Ch+8z48JuHCIQ0OYH5COlekAmmPGDYZ6s= X-Google-Smtp-Source: AGHT+IEt4VJKEPgJx4tlG5g4ikXz2dtjVyj2SYUF5KqhEqONLcWg5QVkmVB9VTayydTpCsWqTVn3so8ZWDvKxXbenYE= X-Received: by 2002:a05:6808:1a0f:b0:3f4:12a:8ca0 with SMTP id 5614622812f47-3f4012a8dd3mr2200344b6e.4.1739830415604; Mon, 17 Feb 2025 14:13:35 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Mon, 17 Feb 2025 15:12:59 -0700 X-Gm-Features: AWEUYZkTES0Y32FzyCudZSWaoqxYdSDB103BjMOS7sWt_TaYLTEmJoIGh5QgrKo Message-ID: Subject: Re: add function argument name to substring and substr To: jian he Cc: PostgreSQL-development Content-Type: multipart/alternative; boundary="000000000000e4456e062e5dd57c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e4456e062e5dd57c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jan 21, 2025 at 11:21=E2=80=AFPM jian he wrote: > hi. > > attached patch add function argument name to function substring and subst= r > you can see the visual changes. > mater behavior: > > \df substring > List of functions > Schema | Name | Result data type | Argument data types | Ty= pe > > ------------+-----------+------------------+-------------------------+---= --- > pg_catalog | substring | bit | bit, integer | fu= nc > pg_catalog | substring | bit | bit, integer, integer | fu= nc > pg_catalog | substring | bytea | bytea, integer | func > pg_catalog | substring | bytea | bytea, integer, integer | func > pg_catalog | substring | text | text, integer | fu= nc > pg_catalog | substring | text | text, integer, integer | fu= nc > pg_catalog | substring | text | text, text | fu= nc > pg_catalog | substring | text | text, text, text | fu= nc > > with patch > List of functions > Schema | Name | Result data type | Argument > data types | Type > > ------------+-----------+------------------+-----------------------------= ---------------------+------ > pg_catalog | substring | bit | bits bit, start integer > | func > pg_catalog | substring | bit | bits bit, start integer, > count integer | func > pg_catalog | substring | bytea | bytes bytea, start integer > | func > pg_catalog | substring | bytea | bytes bytea, start integer, > count integer | func > pg_catalog | substring | text | string text, pattern text > | func > pg_catalog | substring | text | string text, pattern > text, escape_character text | func > pg_catalog | substring | text | string text, start > integer | func > pg_catalog | substring | text | string text, start > integer, count integer | func > > I did the same change to the function substr. > since 9.7.2. SIMILAR TO Regular Expressions we use > substring(string, pattern, escape-character) > so i refactor the substring function argument name to > substring(string text, pattern text, escape_character text). > > we can make it as ``substring(string text, pattern text, escape text).`` > then in 9.7.2, we need to change the substring synopsis section. > > > > one thing I am not sure is about > 9.4. String Functions and Operators > Table 9.9. SQL String Functions and Operators. > Do we need entries for substring related functions? > > current signature > substring ( string text [ FROM start integer ] [ FOR count integer ] ) > is kind of different from > substring(string text, start integer, count integer). > since the previous one can allow keywords "FROM", "FOR", the latter one > won't. > > 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 covered 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. 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. David J. --000000000000e4456e062e5dd57c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jan 21, 2025 at 11:21=E2=80=AFPM jian he <jian.universality@gmail.com&g= t; wrote:
hi.

attached patch add function argument name to function substring and substr<= br> you can see the visual changes.
mater behavior:

\df substring
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0List of functions
=C2=A0 =C2=A0Schema=C2=A0 =C2=A0|=C2=A0 =C2=A0Name=C2=A0 =C2=A0 | Result da= ta type |=C2=A0 =C2=A0Argument data types=C2=A0 =C2=A0| Type
------------+-----------+------------------+-------------------------+-----= -
=C2=A0pg_catalog | substring | bit=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | bit, integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | func
=C2=A0pg_catalog | substring | bit=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | bit, integer, integer=C2=A0 =C2=A0| func
=C2=A0pg_catalog | substring | bytea=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| byt= ea, integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | func
=C2=A0pg_catalog | substring | bytea=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| byt= ea, integer, integer | func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| text, integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| func =C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| text, integer, integer=C2=A0 | func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| text, text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | fu= nc
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| text, text, text=C2=A0 =C2=A0 =C2=A0 =C2=A0 | func

with patch
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 L= ist of functions
=C2=A0 =C2=A0Schema=C2=A0 =C2=A0|=C2=A0 =C2=A0Name=C2=A0 =C2=A0 | Result da= ta type |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Argument data types=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Type ------------+-----------+------------------+-------------------------------= -------------------+------
=C2=A0pg_catalog | substring | bit=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | bits bit, start integer
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| func
=C2=A0pg_catalog | substring | bit=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 | bits bit, start integer,
count integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| func
=C2=A0pg_catalog | substring | bytea=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| byt= es bytea, start integer
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | fun= c
=C2=A0pg_catalog | substring | bytea=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| byt= es bytea, start integer,
count integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 | func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| string text, pattern text
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| string text, pattern
text, escape_character text | func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| string text, start
integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0| func
=C2=A0pg_catalog | substring | text=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| string text, start
integer, count integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 | func

I did the same change to the function substr.
since 9.7.2. SIMILAR TO Regular Expressions we use
substring(string, pattern, escape-character)
so i refactor the substring function argument name to
substring(string text, pattern text, escape_character text).

we can make it as ``substring(string text, pattern text, escape text).`` then in 9.7.2, we need to change the substring synopsis section.


=C2=A0
one thing I am not sure is about
9.4. String Functions and Operators
Table 9.9. SQL String Functions and Operators.
Do we need entries for substring related functions?

current signature
substring ( string text [ FROM start integer ] [ FOR count integer ] )
is kind of different from
substring(string text, start integer, count integer).
since the previous one can allow keywords "FROM", "FOR"= , the latter one won't.


Table 9.9 limits itself to those fu= nctions 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 covered in Table 9.10 but we spell it s= ubstr(...)

I don't think adding yet more spellings= of this same function is warranted or desirable at this point.=C2=A0 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 though.

David J.

--000000000000e4456e062e5dd57c--