Message-ID: <1b702de4-86b0-4144-a2d0-38266296894f@postgresfriends.org>
Date: Mon, 28 Jul 2025 12:47:47 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: implement CAST(expr AS type FORMAT 'template')
To: jian he <jian.universality@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
 <4c358bf8-87e8-4760-83d5-a6f9fcd88907@postgresfriends.org>
 <CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
Content-Language: en-US
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: 
 <CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/1b702de4-86b0-4144-a2d0-38266296894f%40postgresfriends.org>
Precedence: bulk


On 28/07/2025 10:41, jian he wrote:
> 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).


This is the same issue I came across when I tried to implement it 
several years ago.


> 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.

-- 

Vik Fearing