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

On Mon, Jul 28, 2025 at 2:31 AM Vik Fearing <vik@postgresfriends.org> wrote:
>
>
> On 27/07/2025 17:43, jian he wrote:
> > hi.
> >
> > while working on CAST(... DEFAULT ON ERROR), I came across link[1].  I don't
> > have access to the SQL standard, but based on the information in link[1], for
> > CAST(val AS type FORMAT 'template'), I make the <cast template> as an A_Const
> > node in gram.y.
>
>
> Why does it have to be an A_const?  Shouldn't any a_expr work there?
>

you are right. a_expr should work.
the attached patch changed accordingly.

so now
select cast(NULL as date format NULL::date); ---error
select cast(NULL as date format lower('a')); --no error, returns NULL

>
> > so the attached patch is to implement
> >       CAST <left paren>
> >           <cast operand> AS <cast target>
> >           [ FORMAT <cast template> ]
> >           <right paren>
>

> This is correct syntax.  Thanks for working on it!
>

>
> This doesn't seem very postgres-y to me.  Wouldn't it be better to add
> something like castformatfuncid to pg_cast?  That way any types that
> have that would just call that.  It would allow extensions to add
> formatted casting to their types, for example.
>

select oid, castsource::regtype, casttarget::regtype,
castfunc::regproc, castcontext, castmethod
from pg_cast
where casttarget::regtype::text in ('text') or
castsource::regtype::text in ('text');

As you can see from the query output, cast from other type to text or
cast from text to other type is not in the pg_cast catalog entry.
there are in type input/output functions. it will be represented as a
CoerceViaIO node.
see function find_coercion_pathway (src/backend/parser/parse_coerce.c
line:3577).

adding these pg_cast entries seems tricky.
for example:
(assume castsource as numeric, casttarget as text)
will
(castsource as numeric, casttarget as text, castfunc as numeric_out,
castformatfunc as numeric_to_char)
ever work?
but numeric_out' result type is cstring.
so I tend to think adding castformatfunc to pg_cast will not work.