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
