Date: Tue, 5 Mar 2024 22:24:47 -0500
From: Bruce Momjian <bruce@momjian.us>
To: dansonlinepresence@gmail.com
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: substring start position behavior
Message-ID: <Zefh_2-e9ILgmCjW@momjian.us>
References: <170905442373.643.11536838320909376197@wrigleys.postgresql.org>
 <Zee2K8FeRyT4NY5O@momjian.us>
 <CAK5Hk6=hUM=M_rRQThjZNBVhc=KuHoCs=+n0OjLeDdspChFo9A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="eIfhde6b4jPE7TMz"
Content-Disposition: inline
In-Reply-To: 
 <CAK5Hk6=hUM=M_rRQThjZNBVhc=KuHoCs=+n0OjLeDdspChFo9A@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/Zefh_2-e9ILgmCjW%40momjian.us>
Precedence: bulk


--eIfhde6b4jPE7TMz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Mar  5, 2024 at 09:47:54PM -0500, dansonlinepresence@gmail.com wrote:
> Thanks for all that context and diff! The odd behavior is indeed in
> the SQL spec. At least I was convinced of that on postgres IRC by
> someone who seemed to have quoted from the spec. I don't think the
> feature ought to be hidden because it's odd.
> 
> If I may suggest some specificity to the wording here, I think "start
> values less than 1" would avoid confusion about whether 0 is
> nonpositive or not, and bring attention to the function being
> 1-indexed rather than 0-indexed.

Sure, updated patch attached.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--eIfhde6b4jPE7TMz
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="substring.diff"

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c161..f981793f00 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -2783,6 +2783,13 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         specified.  Provide at least one of <parameter>start</parameter>
         and <parameter>count</parameter>.
        </para>
+       <para>
+        <parameter>start</parameter> values less then one specify
+        a start position before the first character, and therefore
+        <parameter>count</parameter> must be two greater than the absolute
+        value of <parameter>start</parameter> to begin returning characters
+        from the start of the string.
+       </para>
        <para>
         <literal>substring('Thomas' from 2 for 3)</literal>
         <returnvalue>hom</returnvalue>
@@ -2794,6 +2801,10 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
        <para>
         <literal>substring('Thomas' for 2)</literal>
         <returnvalue>Th</returnvalue>
+       </para>
+       <para>
+        <literal>substring('Thomas' from -4 for 8)</literal>
+        <returnvalue>Tho</returnvalue>
        </para></entry>
       </row>
 

--eIfhde6b4jPE7TMz--