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

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.

>
> IME we are actually implementing a formatting option for text
> serialization and deserialization here, not a cast (we are just borrowing
> existing syntax that is serviceable). Hence the absence of these entries
> in pg_cast and why the fit into pg_type seems so reasonable.
>
> The existence of the various "to_char" and "to_date" functions reflects
> the historical lack of a dedicated syntax for this kind of
> (de-)serialization. But it seems unwise to bias ourselves to how the new
> syntax/feature should be implemented just because these functions exist.
> At least one design should be done pretending they don't and see what comes
> out of it. Their code can always be moved or reused in whatever we come up
> with; forcing them to be used directly, as-is, within the new solution adds
> an unnecessary constraint.
>

I agree. I'd like the more generic solution, but I don't want to get in the
way of getting it done, especially if we can change the internals later
with no user impact.

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