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

On Mon, Jul 28, 2025 at 6:47 PM Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
> > 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.
>
>
> I had been imagining another castcontext that would only specify the
> castfunc when the FORMAT claused is used, otherwise the current method
> of passing through IO would be used.
>
>
> > so I tend to think adding castformatfunc to pg_cast will not work.
>
>
> Perhaps not, but we need to find a way to make this generic so that
> custom types can define formatting rules for themselves.

We can introduce another column in pg_proc, proformat
hope it's not crazy as it is.

select proname, prosrc, proformat from pg_proc where proformat;
proname | prosrc | proformat
--------------+---------------------+-----------
to_char | timestamptz_to_char | t
to_char | numeric_to_char | t
to_char | int4_to_char | t
to_char | int8_to_char | t
to_char | float4_to_char | t
to_char | float8_to_char | t
to_number | numeric_to_number | t
to_timestamp | to_timestamp | t
to_date | to_date | t
to_char | interval_to_char | t
to_char | timestamp_to_char | t

proformat is true means this function is a formatter function.
formatter function requirement:
* first argument or the return type must be TEXT.
* the second argument must be a type of TEXT.
* function should not return a set.
* keyword FORMAT must be specified while CREATE FUNCTION.
* prokind should be PROKIND_FUNCTION, normal function.
* input argument should be two. because I am not sure how to handle
multiple format templates.
like, CAST('A' AS TEXT FORMAT format1 format2).

for example:
CREATE FUNCTION test(TEXT, TEXT) RETURNS JSON AS $$ BEGIN RETURN '1';
END; $$ LANGUAGE plpgsql VOLATILE FORMAT;
this function "test" format text based on second argument(template)
and return json type.

POC attached.
what do you think?