implement CAST(expr AS type FORMAT 'template')

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: implement CAST(expr AS type FORMAT 'template')
Date: 2025-07-27 15:43:48
Message-ID: CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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

The implementation is pretty straightforward.
CAST(val AS type FORMAT 'template')
internally, it will be transformed into a FuncExpr node whose funcid
corresponds to
function name as one of (to_number, to_date, to_timestamp, to_char).
template as a Const node will make life easier.

select proname, prosrc, proallargtypes, proargtypes,
prorettype::regtype, proargnames
from pg_proc
where proname in ('to_number', 'to_date', 'to_timestamp', 'to_char');

based on the query results, only a limited set of type casts are supported with
formatted casts. so error out early if the source or target type doesn't meet
these conditions. for example, if the source or target is a composite, array,
or polymorphic type.

demo:
select cast('2018-13-12' as date format 'YYYY-MM-DD'); --error
select cast('2018-13-12' as date format 'YYYY-DD-MM'); --no error
select to_char(cast('2018-13-12' as date format 'YYYY-DD-MM'), 'YYYY-Mon-DD');
returns
2018-Dec-13

[1]: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Major_features_simply_not_implemented_yet

Attachment Content-Type Size
v1-0001-CAST-val-AS-type-FORMAT-template.patch text/x-patch 42.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Patrick Stählin 2025-07-27 15:45:35 Re: Non-blocking archiver process
Previous Message Greg Burd 2025-07-27 14:12:11 Re: [PATCH] Let's get rid of the freelist and the buffer_strategy_lock