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

On Mon, Jul 6, 2026 at 1:27 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Jul 6, 2026 at 4:15 PM Haibo Yan <tristan(dot)yim(at)gmail(dot)com> wrote:
> > The examples I listed are mostly conversions between a typed value and a
> > textual or binary representation, not arbitrary conversions between unrelated
> > types. So I agree that the current pg_format_cast design may be too broad if
> > it allows arbitrary source/target pairs.
>
> Well, I don't think the issue here is really about pg_format_cast. If
> the syntax supports arbitrary pairs of types, the catalog
> representation should do. But there's also the question of whether we
> want to support the syntax.
>
> > Maybe the better boundary is that a formatted conversion should be between a
> > data type and an external representation type, probably text or bytea.
> >
> > That would allow cases such as text -> date, numeric -> text, and
> > bytea -> some_type, and also the existing-style text <-> bytea encoding
> > case. But it should not become a generic text -> text or bytea -> bytea
> > transformation mechanism, nor a second cast system for arbitrary type pairs.
>
> But if this is in the SQL standard -- is it? -- then that ship has
> already sailed. We're just left to wonder why they did it like that...
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com

Hi all,

I looked more closely at the standard and at the concerns raised here.

I now think my v2 patch is too broad. It tries to make CAST(... FORMAT ...)
a generic user-defined format-cast mechanism, but SQL feature T839 is much
narrower: formatted casts between datetime types and character strings.

So I think the next version should move back closer to Jian’s original scope:
support T839 first, rather than introduce pg_format_cast and
CREATE FORMAT CAST. I still think we should avoid ad-hoc parser rewrites to
specific function names, but the feature scope should be the standard
datetime/string cases, not arbitrary source/target pairs.

I also think it would be useful to retitle this thread around the standard
feature, for example:

Support SQL feature T839: formatted casts between datetime and
character strings

That seems to describe the real target better than a generic format-cast
facility.

Regards,
Haibo