Re: implement CAST(expr AS type FORMAT 'template')

On Sunday, August 3, 2025, Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:

> On Sun, Aug 3, 2025 at 11:36 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Sun, Aug 3, 2025 at 8:10 PM jian he <jian(dot)universality(at)gmail(dot)com>
>> wrote:
>>
>>> hi.
>>> one more question:
>>>
>>> For binary coercible type casts, no formatted related function for it,
>>> should we error out?
>>> For example, should the following error out or return text '1'.
>>>
>>> select cast('1'::text as text format 'YYYY'::text);
>>>
>>
>> I'm hoping the standard says (or allows us to) error out here.
>>
>
> We have some influence in that, I believe.
>
>
>>
>> text as a type has no semantics on which to associate a format so it
>> should be an error to attempt to do so. Not a silent no-op.
>>
>
> +1
>
>
>
>> 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.

But, once this is implemented, does it then make sense to then parse
> to_char() and to_date() into casts?
>

I have no principled reason but I wouldn’t bother to turn these calls into
casts nor do I think turning casts into these specific function calls by
name is a good idea. Leave the legacy stuff in place for compatibility,
unchanged from its present form, and do the new stuff anew.

David J.