Content-Type: multipart/alternative;
 boundary="------------hhbu80YwMFHlsRfP64EKlyID"
Message-ID: <82b4223e-84d4-432d-8167-dd698ce489f5@postgresfriends.org>
Date: Mon, 4 Aug 2025 14:57:47 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: "David G. Johnston" <david.g.johnston@gmail.com>,
 Corey Huinker <corey.huinker@gmail.com>
Cc: jian he <jian.universality@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
 <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org>
 <CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
 <1b702de4-86b0-4144-a2d0-38266296894f@postgresfriends.org>
 <CACJufxEH-8UPdbPoUoqNRaiOePw+s2W2DG4OpXtoSYDaW30oAg@mail.gmail.com>
 <CAKFQuwZXYxPw4wofq+zZ5D7nH0sHDBwr--qku9jWYv7dS+P8RQ@mail.gmail.com>
 <CACJufxGTeSnHn7w8r-qoQrqwCFQSC+CbiEthzpHKUd+bPr_0mA@mail.gmail.com>
 <bfcc5265-f527-4abb-9a20-75a063bc4f28@postgresfriends.org>
 <CACJufxGbsc3g6RZf0rZpKYxffK6vGkc_OsTmX54uS0kFcVBtvA@mail.gmail.com>
 <CAKFQuwaG00tsXc2oSQwjdSnPQ=JuH+T8Oeejb3sO5TeqUHsfUQ@mail.gmail.com>
 <CADkLM=cvhJBrCj3GV+jXfpb5+U1Z33nsvCXo-=ZkDyaSPbS-1g@mail.gmail.com>
 <CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/82b4223e-84d4-432d-8167-dd698ce489f5%40postgresfriends.org>
Precedence: bulk

This is a multi-part message in MIME format.
--------------hhbu80YwMFHlsRfP64EKlyID
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 04/08/2025 07:55, David G. Johnston wrote:
> On Sunday, August 3, 2025, Corey Huinker <corey.huinker@gmail.com> wrote:
>
>     On Sun, Aug 3, 2025 at 11:36 PM David G. Johnston
>     <david.g.johnston@gmail.com> wrote:
>
>
>         I was under the impression that for format to be allowed in
>         the expression one of the two data types involved has to be
>         text and the other must not be text.
>
>
>     I hadn't understood that, but also hadn't thought of a case where
>     it might be wanted until just now. What if someone wanted a cast
>     from JSONB to their custom type, and the format was a specific
>     keypath to extract from the JSONB? It's true that could be
>     accomplished by first extracting the keypath and then CASTing that
>     expression, but the same is true for text->date, regexing a
>     YYYY-MM-DD into the locale default.
>
>
> Feels like the same basic answer.  Create cast has a single (because 
> it’s one-way) function accepting between 1 and 3 arguments.  Change it 
> to accept between 1 and 4 arguments and the 4th is where the format 
> expression gets passed.  If a format expression is present and the 
> function doesn’t have a 4th argument, error.


This is my position as well.

+1

-- 

Vik Fearing

--------------hhbu80YwMFHlsRfP64EKlyID
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 04/08/2025 07:55, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      On Sunday, August 3, 2025, Corey Huinker &lt;<a
        href="mailto:corey.huinker@gmail.com" moz-do-not-send="true"
        class="moz-txt-link-freetext">corey.huinker@gmail.com</a>&gt;
      wrote:<br>
      <blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
        <div dir="ltr">
          <div class="gmail_quote">
            <div dir="ltr" class="gmail_attr">On Sun, Aug 3, 2025 at
              11:36 PM David G. Johnston &lt;<a
                href="mailto:david.g.johnston@gmail.com" target="_blank"
                moz-do-not-send="true" class="moz-txt-link-freetext">david.g.johnston@gmail.com</a>&gt;
              wrote:<br>
            </div>
            <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
              <div dir="ltr"><br>
              </div>
            </blockquote>
            <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
              <div dir="ltr">
                <div class="gmail_quote">
                  <div style="font-family:arial,helvetica,sans-serif">I
                    was under the impression that for format to be
                    allowed in the expression one of the two data types
                    involved has to be text and the other must not be
                    text.</div>
                </div>
              </div>
            </blockquote>
            <div><br>
            </div>
            <div>I hadn't understood that, but also hadn't thought of a
              case where it might be wanted until just now. What if
              someone wanted a cast from JSONB to their custom type, and
              the format was a specific keypath to extract from the
              JSONB? It's true that could be accomplished by first
              extracting the keypath and then CASTing that expression,
              but the same is true for text-&gt;date, regexing a
              YYYY-MM-DD into the locale default.</div>
          </div>
        </div>
      </blockquote>
      <div><br>
      </div>
      <div>Feels like the same basic answer.  Create cast has a single
        (because it’s one-way) function accepting between 1 and 3
        arguments.  Change it to accept between 1 and 4 arguments and
        the 4th is where the format expression gets passed.  If a format
        expression is present and the function doesn’t have a 4th
        argument, error.</div>
    </blockquote>
    <p><br>
    </p>
    <p>This is my position as well.</p>
    <p>+1</p>
    <p>-- </p>
    <p>Vik Fearing</p>
  </body>
</html>

--------------hhbu80YwMFHlsRfP64EKlyID--