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

On Tue, Mar 31, 2026 at 1:48 PM Corey Huinker <corey(dot)huinker(at)gmail(dot)com> wrote:
> Everything's passing, moving the tests out of citext, etc into cast.sql is good.
>
> I think the next step it to bring each TODO and FIXME into the thread, and explain the factors that prevent you from being certain about what to do in those situations.

IMHO, this patch as currently written is basically dead on arrival.
The v7 patch still works by constructing calls to
pg_catalog.{to_date,to_number,to_timestamp,to_char} based on the input
data type. But I think it's been clearly stated on this thread that we
need some kind of more generic infrastructure. Vik said it best: "we
need to find a way to make this generic so that custom types can
define formatting rules for themselves." I completely agree. The
extensible type system in PostgreSQL is one of the project's greatest
triumphs, and I do not think anyone is going to be enthusiastic about
committing a patch that purports to implement a flavor of casting but
is completely unextensible by out-of-core types and doesn't even cover
all the in-core types for which it might be interesting. Even if
someone is, -1 from me.

I suggest backing up to David G. Johnston's comment here: "How about
changing the specification for create type. Right now input functions
must declare either 1 or 3 arguments. Let’s also allow for 2 and
4-argument functions where the 2nd or 4th is where the format is
passed. If a data type input function lacks one of those signatures
it is a runtime error if a format clause is attached to its cast
expression. For output, we go from having zero input arguments to
zero or one, with the same resolution behavior." I'm not sure that
David's proposal here is really the best thing, but it's the kind of
thing that *could* be right, i.e. a generic infrastructure that can
work for any choice of data type.

The reason I somewhat hesitate to endorse that specific proposal is
that I'm not convinced that we should actually treat this as a form of
casting. Casts can be set to IMPLICIT or ASSIGNMENT or EXPLICIT, and
they can be WITHOUT FUNCTION or WITH INOUT, and none of that can be
relevant here. A CAST with FORMAT always needs to be implemented by a
function, is always explicit from a syntax point of view, and the code
to implement probably looks pretty different from the code needed for
a non-FORMAT cast. I am somewhat inclined to think we want something
that's like a cast function but actually a wholly separate mechanism,
e.g. a new pg_formatter catalog. I note that Jian He proposed putting
something in pg_type but I don't see how that can work, since there
are two types involved.

I don't accept the argument that we should start with this and extend
it later. The patch as proposed is just syntactic sugar. Said another
way, there is existing syntax that already delivers the functionality.
So I don't see why we would rush out support for a bit of new syntax;
anyone who wants to use this functionality can already do so. Getting
the infrastructure right is, IMHO, the interesting part of the
project, and I think that work needs to be done first.

--
Robert Haas
EDB: http://www.enterprisedb.com