On Wed, Jul 1, 2026 at 9:45 AM Haibo Yan <[email protected]> wrote:
> Hi Jian, > > Thanks for looking at this. > > On Wed, Jul 1, 2026 at 6:45 AM jian he <[email protected]> > wrote: > > > > On Wed, Jul 1, 2026 at 1:39 AM Haibo Yan <[email protected]> wrote: > > > > > > Thanks for looking at the patches. > > > I liked your suggestion to call these “format casts” rather than > > > “formatters”, so I changed the series in that direction. The DDL is > > > now: > > > ------------------------------------------------------ > > > CREATE FORMAT CAST (...) > > > DROP FORMAT CAST (...) > > > ------------------------------------------------------ > > > rather than CREATE/DROP FORMATTER, so the patch no longer adds > > > FORMATTER as a keyword. > > > I also renamed the related catalog and node names, so this now uses > > > pg_format_cast and CoerceViaFormatCast. The > > > pg_dump/object-address/comment/extension code and the regression tests > > > have been updated to use the new terminology as well. > > > The rest of the design is the same as before: format casts are still > > > catalog-driven, and both built-in and user-defined cases go through > > > the same lookup path. > > > > > > > In an earlier thread [1], [2], I proposed introducing: > > > > CREATE CAST (source_type AS target_type) > > WITH [SAFE] FUNCTION function_name [ (argument_type [, ...]) ] > > > > That drew some pushback over non-standard conformance. > > Here, we are introducing > > > > CREATE FORMAT CAST (source_type AS target_type) > > WITH FUNCTION function_name [ (argument_type [, ...]) ] > > > > This syntax is quite close to CREATE CAST. > > per https://www.postgresql.org/docs/devel/sql-createcast.html > > CREATE FORMAT CAST would be an extension to the standard, though we > > already have non-standard precedent: AS IMPLICIT. > > > > Before proceeding, do we need to reach consensus on the syntax itself? > > yes, I agree that we should get agreement on the DDL syntax before going > much further. > > I see the analogy with the earlier SAFE discussion, but I think this case > is a > little different. This patch creates a separate object kind rather than > adding > another option to ordinary CREATE CAST > > > > > Does the following alternative syntax make sense for CAST FORMAT? > > My reason for preferring CREATE FORMAT CAST is that a format cast is not > really an ordinary cast with one more attribute. Ordinary casts live in > pg_cast and are tied to things like cast context and cast method. A format > cast is only considered for > > CAST(expr AS type FORMAT fmt) > and not for ordinary casts without a FORMAT clause, assignment casts, or > implicit casts. It also always needs a function whose second argument is > the > FORMAT expression, not the destination typmod argument used by ordinary > cast > functions. > So I think > > CREATE CAST (...) WITH FUNCTION ... AS FORMAT > > would make this look more like a pg_cast entry than it really is. I think the fact the standard put this inside the 'cast(...)' means it's quite reasonable to consider the aspect part of a cast definition as opposed to something wholly different. When we issue "create table" both pg_class and pg_attribute are modified. It seems quite reasonable that executing "create cast" causes both pg_cast and pg_cast_format to be populated. With two separate commands a cast could exist on pg_cast_format that doesn't exist in pg_cast. I'm unsure whether this is allowed or desired...but seems a bit unexpected to me at least. > > > > CREATE CAST (source_type AS target_type) > > WITH FUNCTION function_name [ (argument_type [, ...]) ] > > AS FORMAT > > > ---------------------------------------------------------------------------------------- > > As I mentioned previously [3], there's an open question around this case: > > > > CAST('{2022-01-01, 2022-21-01}' AS DATE[] FORMAT 'YYYY-DD-MM'); > > > > does this mean the format template ('YYYY-DD-MM') should be applied to > each > > element (2022-01-01, 2022-21-01) individually via the cast format > function? > Yes, the internal array formatting structure used by PostgreSQL isn't (needn't be) user-configurable; the useful thing for the format to apply to is the value of the elements of the array. IMO the only change the addition of a format clause should make when performing the cast is allowing a cast that would fail due to a syntax error to succeed. postgres=# select cast(cast('{"2025-01-01","2026-02-02","2027-03-03"}'::text as text[]) as date[]); date ------------------------------------ {2025-01-01,2026-02-02,2027-03-03} (1 row) postgres=# select cast(cast('{"2025T01-01","2026T02-02","2027T03-03"}'::text as text[]) as date[]); ERROR: invalid input syntax for type date: "2025T01-01" A format clause would make the second query not error with an appropriate format specification - in which case it then behaves identically to the first query. If the SQL Standard somehow contradicts this intent I'd be curious to understand what it does intend. David J.
