public inbox for [email protected]
help / color / mirror / Atom feedFrom: Haibo Yan <[email protected]>
To: Robert Haas <[email protected]>
Cc: Corey Huinker <[email protected]>
Cc: jian he <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: implement CAST(expr AS type FORMAT 'template')
Date: Sun, 28 Jun 2026 22:19:53 -0700
Message-ID: <CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmoaUzwAxa_qHSp9ybCkAhMjQRDuEAZW2BrSb1DTPiz4ZzA@mail.gmail.com>
References: <CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com>
<[email protected]>
<CACJufxF4OW=x2rCwa+ZmcgopDwGKDXha09qTfTpCj3QSTG6Y9Q@mail.gmail.com>
<[email protected]>
<CACJufxEH-8UPdbPoUoqNRaiOePw+s2W2DG4OpXtoSYDaW30oAg@mail.gmail.com>
<CAKFQuwZXYxPw4wofq+zZ5D7nH0sHDBwr--qku9jWYv7dS+P8RQ@mail.gmail.com>
<CACJufxGTeSnHn7w8r-qoQrqwCFQSC+CbiEthzpHKUd+bPr_0mA@mail.gmail.com>
<[email protected]>
<CACJufxGbsc3g6RZf0rZpKYxffK6vGkc_OsTmX54uS0kFcVBtvA@mail.gmail.com>
<CAKFQuwaG00tsXc2oSQwjdSnPQ=JuH+T8Oeejb3sO5TeqUHsfUQ@mail.gmail.com>
<CADkLM=cvhJBrCj3GV+jXfpb5+U1Z33nsvCXo-=ZkDyaSPbS-1g@mail.gmail.com>
<CAKFQuwYrwM64YnvZ5aL1DEWG50uW-1SrXesSKkmvpxXXrxTW-g@mail.gmail.com>
<[email protected]>
<CACJufxH+soqLj_AuMQj-_jxunVQKX-HBQA_3_3vmV1jTRyZ1hA@mail.gmail.com>
<CACJufxGhOOYnPtmsuVocXVNyx2gB3K68ze67CM4LUqQeW--S8g@mail.gmail.com>
<CAN4CZFNnqWbyk3UNr3XAwtKJooH8LnbDUg-f2JD9z_rG=feT7w@mail.gmail.com>
<CACJufxFVMYuVz63DteEjbMdvx2neoYcwSRb5vhO-03Tzx=B8Jw@mail.gmail.com>
<CAN4CZFNeCfLzt5g90vf_Dw5BKW2u-QhVKo369XbO-vXYq4ci5g@mail.gmail.com>
<CACJufxH4ELUjdGNi7vkHEA6L=G0SfE5abnFs5vjvEm3G-Jqsbw@mail.gmail.com>
<CADkLM=dGxwVCp3y8=LU+8hQUPw61JELx2jh02i729YCex8UqWg@mail.gmail.com>
<CA+TgmoaUzwAxa_qHSp9ybCkAhMjQRDuEAZW2BrSb1DTPiz4ZzA@mail.gmail.com>
On Thu, Jun 18, 2026 at 7:52 AM Robert Haas <[email protected]> wrote:
>
> On Tue, Mar 31, 2026 at 1:48 PM Corey Huinker <[email protected]> wrote:
> > Everything's passing, moving the tests out of citext, etc into cast.sql is good.
> >
> > I think the next step it to bring each TODO and FIXME into the thread, and explain the factors that prevent you from being certain about what to do in those situations.
>
> IMHO, this patch as currently written is basically dead on arrival.
> The v7 patch still works by constructing calls to
> pg_catalog.{to_date,to_number,to_timestamp,to_char} based on the input
> data type. But I think it's been clearly stated on this thread that we
> need some kind of more generic infrastructure. Vik said it best: "we
> need to find a way to make this generic so that custom types can
> define formatting rules for themselves." I completely agree. The
> extensible type system in PostgreSQL is one of the project's greatest
> triumphs, and I do not think anyone is going to be enthusiastic about
> committing a patch that purports to implement a flavor of casting but
> is completely unextensible by out-of-core types and doesn't even cover
> all the in-core types for which it might be interesting. Even if
> someone is, -1 from me.
>
> I suggest backing up to David G. Johnston's comment here: "How about
> changing the specification for create type. Right now input functions
> must declare either 1 or 3 arguments. Let’s also allow for 2 and
> 4-argument functions where the 2nd or 4th is where the format is
> passed. If a data type input function lacks one of those signatures
> it is a runtime error if a format clause is attached to its cast
> expression. For output, we go from having zero input arguments to
> zero or one, with the same resolution behavior." I'm not sure that
> David's proposal here is really the best thing, but it's the kind of
> thing that *could* be right, i.e. a generic infrastructure that can
> work for any choice of data type.
>
> The reason I somewhat hesitate to endorse that specific proposal is
> that I'm not convinced that we should actually treat this as a form of
> casting. Casts can be set to IMPLICIT or ASSIGNMENT or EXPLICIT, and
> they can be WITHOUT FUNCTION or WITH INOUT, and none of that can be
> relevant here. A CAST with FORMAT always needs to be implemented by a
> function, is always explicit from a syntax point of view, and the code
> to implement probably looks pretty different from the code needed for
> a non-FORMAT cast. I am somewhat inclined to think we want something
> that's like a cast function but actually a wholly separate mechanism,
> e.g. a new pg_formatter catalog. I note that Jian He proposed putting
> something in pg_type but I don't see how that can work, since there
> are two types involved.
>
> I don't accept the argument that we should start with this and extend
> it later. The patch as proposed is just syntactic sugar. Said another
> way, there is existing syntax that already delivers the functionality.
> So I don't see why we would rush out support for a bit of new syntax;
> anyone who wants to use this functionality can already do so. Getting
> the infrastructure right is, IMHO, the interesting part of the
> project, and I think that work needs to be done first.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com
>
>
Robert pointed out earlier that hard-coding specific functions like
to_date and to_char in the parser would make the feature feel like a
set of special cases rather than a general mechanism. I agree, and this
version moves to a catalog-driven design.
I added a new pg_formatter catalog. A row in this catalog represents
one formatted conversion from a source type to a target type, and points
to a formatter function with this signature:
formatter(source_type, text) returns target_type
The second argument is the FORMAT expression coerced to text.
I kept this separate from pg_cast because formatted casts have different
semantics from ordinary casts. Ordinary casts have concepts like implicit
casts, assignment casts, binary-compatible casts, and I/O casts. A
formatted cast is different: it is always explicit, it has an extra format
expression, and it needs a function that receives both the source value and
the format string. Putting this into pg_cast seemed likely to add special
cases there, while a separate catalog keeps the model simpler.
Another important point in this version is that built-in and user-defined
formatters use the same mechanism. The parser and analyzer do not know
about to_date, to_char, or to_timestamp. Built-in datetime/string
formatters are registered as catalog rows, and user-defined formatters are
registered the same way. If no formatter exists for the exact source and
target type pair, the formatted cast fails. It does not fall back to
ordinary cast resolution.
I split the work into four patches.
Patch 1 only adds parser support for the syntax:
CAST(expr AS type FORMAT format_expr)
It extends the raw TypeCast node with an optional format field and
updates the grammar and raw expression walker. Parse analysis still
rejects formatted casts in this patch, so this is only the syntax and raw
parse-tree representation.
Patch 2 adds the catalog and DDL infrastructure. It introduces
pg_formatter, syscache support, CREATE FORMATTER, DROP FORMATTER,
dependency handling, object-address support, and pg_dump support. At this
point CAST ... FORMAT still does not execute; the patch only adds the
catalog object model.
Patch 3 is where formatted casts are resolved. During parse analysis, the
source expression is transformed, the FORMAT expression is transformed and
coerced to text, and pg_formatter is searched by exact source and target
type. I also treat unknown source literals as text, so cases like this
resolve naturally:
CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD')
I added a CoerceViaFormatter node rather than using a plain FuncExpr.
This preserves CAST(... FORMAT ...) syntax in ruleutils and pg_dump, and
allows the dependency walker to record a dependency on the pg_formatter
row itself. Execution still uses the normal function-call machinery, so no
new executor opcode is needed.
Patch 4 adds an initial set of built-in formatter functions and bootstrap
pg_formatter rows for common datetime/string cases:
text/varchar/bpchar <-> date
text/varchar/bpchar <-> timestamp
text/varchar/bpchar <-> timestamptz
These built-ins are reached through the same catalog lookup path as
user-defined formatters. They are not special-cased in parse analysis. The
wrappers reuse PostgreSQL’s existing formatting code and are marked STRICT,
so NULL input or NULL FORMAT returns NULL.
This gives us SQL-standard-style CAST ... FORMAT support for the common
datetime/string cases, but the series intentionally does not claim complete
SQL-standard datetime-template conformance. The built-ins reuse
PostgreSQL’s existing formatting template behavior. Additional types such
as time, timetz, numeric, and interval, or stricter standard-template
coverage, can be added later as formatter functions without changing the
parser or the CoerceViaFormatter representation.
The main goal of this redesign is to make CAST ... FORMAT a real
catalog-driven facility rather than a parser rewrite to a few built-in
functions. I think this addresses Robert’s concern better and gives us a
path for both built-in and extension-defined formatters.
I have attached the full patch series for review. Comments are welcome.
Regards,
Haibo
Attachments:
[application/octet-stream] 0004-Add-built-in-formatters-for-CAST-FORMAT.patch (45.2K, ../CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com/2-0004-Add-built-in-formatters-for-CAST-FORMAT.patch)
download | inline diff:
From d98fffd98a5cb9046117555d1b88147621311d8b Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Sun, 28 Jun 2026 13:28:19 -0700
Subject: [PATCH] Add built-in formatters for CAST ... FORMAT
Add built-in formatter functions and pg_formatter bootstrap rows for the
initial datetime/string formatted casts:
text/varchar/bpchar <-> date
text/varchar/bpchar <-> timestamp
text/varchar/bpchar <-> timestamptz
The wrappers reuse PostgreSQL's existing formatting code and are reached
through pg_formatter, not by parser or analyzer special cases. They are
STRICT, so NULL input or NULL FORMAT returns NULL.
This provides SQL-standard-style CAST ... FORMAT support for the common
datetime/string cases, while keeping PostgreSQL extensions from the
generic infrastructure: FORMAT may be any expression coercible to text,
and formatter lookup remains catalog-driven. The patch does not claim
complete SQL-standard datetime-template conformance; time, timetz,
numeric, interval, and fuller template coverage are left for future work.
Built-in formatter rows are treated as system objects and are not dumped
by pg_dump.
---
src/backend/utils/adt/formatting.c | 325 ++++++++++++++++++++++
src/bin/pg_dump/pg_dump.c | 25 +-
src/include/catalog/pg_formatter.dat | 71 +++++
src/include/catalog/pg_proc.dat | 77 +++++
src/test/regress/expected/expressions.out | 25 +-
src/test/regress/expected/formatters.out | 282 ++++++++++++++++++-
src/test/regress/sql/expressions.sql | 17 +-
src/test/regress/sql/formatters.sql | 131 ++++++++-
8 files changed, 930 insertions(+), 23 deletions(-)
create mode 100644 src/include/catalog/pg_formatter.dat
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index d52d71b0a8c..b1c0524a7ac 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -4138,6 +4138,331 @@ to_date(PG_FUNCTION_ARGS)
PG_RETURN_DATEADT(result);
}
+/* ----------
+ * Built-in formatters for CAST ( <operand> AS <type> FORMAT <template> )
+ *
+ * These thin wrappers provide the initial datetime/string CAST ... FORMAT
+ * built-ins. They are registered in pg_formatter (see pg_formatter.dat) and
+ * reached only through a CoerceViaFormatter node; they are not a primary
+ * user-facing API (to_char()/to_date()/to_timestamp() serve that purpose).
+ * They reuse PostgreSQL's existing formatting code rather than introducing a
+ * second template engine: to_date()/to_timestamp()/parse_datetime() for the
+ * string -> datetime direction and timestamp_to_char()/timestamptz_to_char()
+ * for the datetime -> string direction. The accepted template tokens are thus
+ * exactly those of to_char()/to_date().
+ *
+ * A separate wrapper per source/target type is needed because pg_formatter
+ * lookup matches the exact source and target types: text, varchar and bpchar
+ * are distinct on each side. On the source side text and varchar share a C
+ * symbol (identical varlena representation and return type), while bpchar gets
+ * its own symbol because the blank padding must be trimmed first. On the
+ * target side each of text, varchar and bpchar gets its own exported symbol
+ * (so no two built-in functions share a prosrc with a different return type),
+ * all delegating to a common static helper. The returned text varlena is a
+ * valid varchar/bpchar value; any declared length/typmod is enforced afterward
+ * by the ordinary coercion layered above the CoerceViaFormatter node.
+ *
+ * All of these functions are STRICT (proisstrict in pg_proc.dat), so a NULL
+ * source value or NULL FORMAT expression yields NULL without the C code ever
+ * being called, matching ordinary strict cast-function behavior.
+ * ----------
+ */
+
+/*
+ * Strip blank padding from a bpchar argument, returning a plain text value.
+ * Standard-style character casts must not fail merely because a fixed-length
+ * CHAR(n) source carries trailing spaces, so bpchar sources are right-trimmed
+ * before the format template is applied.
+ *
+ * bpchar padding uses ordinary ASCII space bytes, so we trim only trailing
+ * ' ' bytes here. This is deliberately not a general Unicode whitespace trim.
+ */
+static text *
+formatcast_rtrim_blanks(text *src)
+{
+ char *data = VARDATA_ANY(src);
+ int len = VARSIZE_ANY_EXHDR(src);
+ int orig_len = len;
+
+ while (len > 0 && data[len - 1] == ' ')
+ len--;
+
+ /* Avoid a copy when there was no trailing padding to trim. */
+ if (len == orig_len)
+ return src;
+
+ return cstring_to_text_with_len(data, len);
+}
+
+/*
+ * string -> date: reuse to_date() verbatim, so behavior matches the
+ * SQL-callable to_date(text, text).
+ */
+static Datum
+formatcast_in_date(text *src, text *fmt, Oid collid)
+{
+ return DirectFunctionCall2Coll(to_date, collid,
+ PointerGetDatum(src), PointerGetDatum(fmt));
+}
+
+/*
+ * string -> timestamp without time zone. There is no SQL-callable function
+ * for this (to_timestamp() returns timestamptz), so we parse with
+ * parse_datetime() and build the result from the local datetime fields without
+ * consulting the session time zone. A date-only template is widened to
+ * timestamp; a zoned template is rejected, since the result type carries no
+ * zone and we must not silently fold a time zone through the session setting.
+ */
+static Datum
+formatcast_in_timestamp(text *src, text *fmt, Oid collid)
+{
+ Oid typid;
+ int32 typmod;
+ int tz;
+ Datum d;
+
+ d = parse_datetime(src, fmt, collid, false, &typid, &typmod, &tz, NULL);
+
+ switch (typid)
+ {
+ case TIMESTAMPOID:
+ return d;
+ case DATEOID:
+ return TimestampGetDatum(date2timestamp_safe(DatumGetDateADT(d),
+ NULL));
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+ errmsg("FORMAT template for a cast to timestamp without time zone must not specify a time zone")));
+ return (Datum) 0; /* keep compiler quiet */
+ }
+}
+
+/*
+ * string -> timestamp with time zone: reuse to_timestamp() verbatim, matching
+ * the SQL-callable to_timestamp(text, text).
+ */
+static Datum
+formatcast_in_timestamptz(text *src, text *fmt, Oid collid)
+{
+ return DirectFunctionCall2Coll(to_timestamp, collid,
+ PointerGetDatum(src), PointerGetDatum(fmt));
+}
+
+/* string -> date (text and varchar sources) */
+Datum
+formatcast_str_to_date(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_date(PG_GETARG_TEXT_PP(0), PG_GETARG_TEXT_PP(1),
+ PG_GET_COLLATION());
+}
+
+/* string -> date (bpchar source; trailing blanks trimmed) */
+Datum
+formatcast_bpchar_to_date(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_date(formatcast_rtrim_blanks(PG_GETARG_TEXT_PP(0)),
+ PG_GETARG_TEXT_PP(1), PG_GET_COLLATION());
+}
+
+/* string -> timestamp (text and varchar sources) */
+Datum
+formatcast_str_to_timestamp(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_timestamp(PG_GETARG_TEXT_PP(0), PG_GETARG_TEXT_PP(1),
+ PG_GET_COLLATION());
+}
+
+/* string -> timestamp (bpchar source; trailing blanks trimmed) */
+Datum
+formatcast_bpchar_to_timestamp(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_timestamp(formatcast_rtrim_blanks(PG_GETARG_TEXT_PP(0)),
+ PG_GETARG_TEXT_PP(1), PG_GET_COLLATION());
+}
+
+/* string -> timestamptz (text and varchar sources) */
+Datum
+formatcast_str_to_timestamptz(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_timestamptz(PG_GETARG_TEXT_PP(0), PG_GETARG_TEXT_PP(1),
+ PG_GET_COLLATION());
+}
+
+/* string -> timestamptz (bpchar source; trailing blanks trimmed) */
+Datum
+formatcast_bpchar_to_timestamptz(PG_FUNCTION_ARGS)
+{
+ return formatcast_in_timestamptz(formatcast_rtrim_blanks(PG_GETARG_TEXT_PP(0)),
+ PG_GETARG_TEXT_PP(1), PG_GET_COLLATION());
+}
+
+/*
+ * Call a collation-aware (datetime, text) -> text formatting function,
+ * propagating a SQL NULL result. DirectFunctionCall cannot handle a SQL NULL
+ * result, so build a local FunctionCallInfo. Pass the caller's flinfo to
+ * provide a normal fmgr context; the callees used here do not depend on
+ * function-specific flinfo state.
+ */
+static Datum
+formatcast_to_char_call(FunctionCallInfo caller_fcinfo, PGFunction fn,
+ Oid collid, Datum value, Datum fmt, bool *resultnull)
+{
+ LOCAL_FCINFO(fcinfo, 2);
+ Datum result;
+
+ InitFunctionCallInfoData(*fcinfo, caller_fcinfo->flinfo, 2, collid,
+ NULL, NULL);
+ fcinfo->args[0].value = value;
+ fcinfo->args[0].isnull = false;
+ fcinfo->args[1].value = fmt;
+ fcinfo->args[1].isnull = false;
+
+ result = (*fn) (fcinfo);
+ *resultnull = fcinfo->isnull;
+ return result;
+}
+
+/*
+ * Per-source-type helpers shared by the datetime -> string wrappers below.
+ * Each formats the source value with the given template, returning the result
+ * text (with *resultnull set on an empty template, as for to_char()).
+ */
+static Datum
+formatcast_date_out(FunctionCallInfo fcinfo, bool *resultnull)
+{
+ DateADT dateVal = PG_GETARG_DATEADT(0);
+ text *fmt = PG_GETARG_TEXT_PP(1);
+ Timestamp ts = date2timestamp_safe(dateVal, NULL);
+
+ return formatcast_to_char_call(fcinfo, timestamp_to_char, PG_GET_COLLATION(),
+ TimestampGetDatum(ts), PointerGetDatum(fmt),
+ resultnull);
+}
+
+static Datum
+formatcast_timestamp_out(FunctionCallInfo fcinfo, bool *resultnull)
+{
+ return formatcast_to_char_call(fcinfo, timestamp_to_char, PG_GET_COLLATION(),
+ PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
+ resultnull);
+}
+
+static Datum
+formatcast_timestamptz_out(FunctionCallInfo fcinfo, bool *resultnull)
+{
+ return formatcast_to_char_call(fcinfo, timestamptz_to_char, PG_GET_COLLATION(),
+ PG_GETARG_DATUM(0), PG_GETARG_DATUM(1),
+ resultnull);
+}
+
+/*
+ * Exported datetime -> string wrappers. The text, varchar and bpchar targets
+ * each get a distinct C symbol so that no two built-in functions share a
+ * prosrc with a different return type; they all delegate to the per-source
+ * helper above, so there is no duplicated logic. The returned text varlena is
+ * a valid varchar/bpchar value; any declared length/typmod is enforced by the
+ * ordinary coercion layered above the CoerceViaFormatter node.
+ */
+Datum
+formatcast_date_to_text(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_date_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_date_to_varchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_date_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_date_to_bpchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_date_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamp_to_text(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamp_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamp_to_varchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamp_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamp_to_bpchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamp_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamptz_to_text(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamptz_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamptz_to_varchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamptz_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
+Datum
+formatcast_timestamptz_to_bpchar(PG_FUNCTION_ARGS)
+{
+ bool resultnull;
+ Datum result = formatcast_timestamptz_out(fcinfo, &resultnull);
+
+ if (resultnull)
+ PG_RETURN_NULL();
+ PG_RETURN_DATUM(result);
+}
+
/*
* Convert the 'date_txt' input to a datetime type using argument 'fmt'
* as a format string. The collation 'collid' may be used for case-folding
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 92883740743..eef3da00cf9 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2188,6 +2188,29 @@ selectDumpableCast(CastInfo *cast, Archive *fout)
DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
}
+/*
+ * selectDumpableFormatter: policy-setting subroutine
+ * Mark a formatter as to be dumped or not
+ *
+ * Like casts, formatters have no namespace and no identifiable owner, so we
+ * distinguish the built-in (initdb-created) formatters from user-defined ones
+ * by checking whether the formatter's OID is in the range reserved for initdb.
+ * Built-in formatters are part of the system catalogs and must not be dumped
+ * as CREATE FORMATTER commands.
+ */
+static void
+selectDumpableFormatter(FormatterInfo *formatter, Archive *fout)
+{
+ if (checkExtensionMembership(&formatter->dobj, fout))
+ return; /* extension membership overrides all else */
+
+ if (formatter->dobj.catId.oid <= g_last_builtin_oid)
+ formatter->dobj.dump = DUMP_COMPONENT_NONE;
+ else
+ formatter->dobj.dump = fout->dopt->include_everything ?
+ DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
+}
+
/*
* selectDumpableProcLang: policy-setting subroutine
* Mark a procedural language as to be dumped or not
@@ -9390,7 +9413,7 @@ getFormatters(Archive *fout)
formatterinfo[i].dobj.name = namebuf.data;
/* Decide whether we want to dump it */
- selectDumpableObject(&(formatterinfo[i].dobj), fout);
+ selectDumpableFormatter(&(formatterinfo[i]), fout);
}
PQclear(res);
diff --git a/src/include/catalog/pg_formatter.dat b/src/include/catalog/pg_formatter.dat
new file mode 100644
index 00000000000..4518071039e
--- /dev/null
+++ b/src/include/catalog/pg_formatter.dat
@@ -0,0 +1,71 @@
+#----------------------------------------------------------------------
+#
+# pg_formatter.dat
+# Initial contents of the pg_formatter system catalog.
+#
+# These are the built-in formatters for CAST ( <operand> AS <type>
+# FORMAT <template> ). Each row maps an exact (source type, target type) pair
+# to a wrapper function with the signature
+# formatter(source_type, text) returns target_type
+# Lookup is by exact source and target type, so text, varchar and bpchar are
+# registered separately on each side. The wrapper functions live in
+# formatting.c and reuse PostgreSQL's existing formatting code.
+#
+# Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/include/catalog/pg_formatter.dat
+#
+#----------------------------------------------------------------------
+
+[
+
+# string -> date
+{ oid => '8668', fmtsource => 'text', fmttarget => 'date',
+ fmtfunc => 'pg_format_text_to_date(text,text)' },
+{ oid => '8669', fmtsource => 'varchar', fmttarget => 'date',
+ fmtfunc => 'pg_format_varchar_to_date(varchar,text)' },
+{ oid => '8670', fmtsource => 'bpchar', fmttarget => 'date',
+ fmtfunc => 'pg_format_bpchar_to_date(bpchar,text)' },
+
+# string -> timestamp without time zone
+{ oid => '8671', fmtsource => 'text', fmttarget => 'timestamp',
+ fmtfunc => 'pg_format_text_to_timestamp(text,text)' },
+{ oid => '8672', fmtsource => 'varchar', fmttarget => 'timestamp',
+ fmtfunc => 'pg_format_varchar_to_timestamp(varchar,text)' },
+{ oid => '8673', fmtsource => 'bpchar', fmttarget => 'timestamp',
+ fmtfunc => 'pg_format_bpchar_to_timestamp(bpchar,text)' },
+
+# string -> timestamp with time zone
+{ oid => '8674', fmtsource => 'text', fmttarget => 'timestamptz',
+ fmtfunc => 'pg_format_text_to_timestamptz(text,text)' },
+{ oid => '8675', fmtsource => 'varchar', fmttarget => 'timestamptz',
+ fmtfunc => 'pg_format_varchar_to_timestamptz(varchar,text)' },
+{ oid => '8676', fmtsource => 'bpchar', fmttarget => 'timestamptz',
+ fmtfunc => 'pg_format_bpchar_to_timestamptz(bpchar,text)' },
+
+# date -> string
+{ oid => '8677', fmtsource => 'date', fmttarget => 'text',
+ fmtfunc => 'pg_format_date_to_text(date,text)' },
+{ oid => '8678', fmtsource => 'date', fmttarget => 'varchar',
+ fmtfunc => 'pg_format_date_to_varchar(date,text)' },
+{ oid => '8679', fmtsource => 'date', fmttarget => 'bpchar',
+ fmtfunc => 'pg_format_date_to_bpchar(date,text)' },
+
+# timestamp without time zone -> string
+{ oid => '8680', fmtsource => 'timestamp', fmttarget => 'text',
+ fmtfunc => 'pg_format_timestamp_to_text(timestamp,text)' },
+{ oid => '8681', fmtsource => 'timestamp', fmttarget => 'varchar',
+ fmtfunc => 'pg_format_timestamp_to_varchar(timestamp,text)' },
+{ oid => '8682', fmtsource => 'timestamp', fmttarget => 'bpchar',
+ fmtfunc => 'pg_format_timestamp_to_bpchar(timestamp,text)' },
+
+# timestamp with time zone -> string
+{ oid => '8683', fmtsource => 'timestamptz', fmttarget => 'text',
+ fmtfunc => 'pg_format_timestamptz_to_text(timestamptz,text)' },
+{ oid => '8684', fmtsource => 'timestamptz', fmttarget => 'varchar',
+ fmtfunc => 'pg_format_timestamptz_to_varchar(timestamptz,text)' },
+{ oid => '8685', fmtsource => 'timestamptz', fmttarget => 'bpchar',
+ fmtfunc => 'pg_format_timestamptz_to_bpchar(timestamptz,text)' },
+
+]
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 402d869710b..40d8f33497f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4847,6 +4847,83 @@
{ oid => '1780', descr => 'convert text to date',
proname => 'to_date', provolatile => 's', prorettype => 'date',
proargtypes => 'text text', prosrc => 'to_date' },
+
+# Built-in formatters for CAST ( ... FORMAT ... ). These are thin wrappers
+# around the existing to_char/to_date/to_timestamp/parse_datetime machinery,
+# registered in pg_formatter (see pg_formatter.dat) and reached only through a
+# CoerceViaFormatter node; they are not a primary user-facing API.
+{ oid => '8650', descr => 'formatted cast: text to date',
+ proname => 'pg_format_text_to_date', proisstrict => 't', provolatile => 's',
+ prorettype => 'date', proargtypes => 'text text',
+ prosrc => 'formatcast_str_to_date' },
+{ oid => '8651', descr => 'formatted cast: varchar to date',
+ proname => 'pg_format_varchar_to_date', proisstrict => 't', provolatile => 's',
+ prorettype => 'date', proargtypes => 'varchar text',
+ prosrc => 'formatcast_str_to_date' },
+{ oid => '8652', descr => 'formatted cast: bpchar to date',
+ proname => 'pg_format_bpchar_to_date', proisstrict => 't', provolatile => 's',
+ prorettype => 'date', proargtypes => 'bpchar text',
+ prosrc => 'formatcast_bpchar_to_date' },
+{ oid => '8653', descr => 'formatted cast: text to timestamp',
+ proname => 'pg_format_text_to_timestamp', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamp', proargtypes => 'text text',
+ prosrc => 'formatcast_str_to_timestamp' },
+{ oid => '8654', descr => 'formatted cast: varchar to timestamp',
+ proname => 'pg_format_varchar_to_timestamp', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamp', proargtypes => 'varchar text',
+ prosrc => 'formatcast_str_to_timestamp' },
+{ oid => '8655', descr => 'formatted cast: bpchar to timestamp',
+ proname => 'pg_format_bpchar_to_timestamp', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamp', proargtypes => 'bpchar text',
+ prosrc => 'formatcast_bpchar_to_timestamp' },
+{ oid => '8656', descr => 'formatted cast: text to timestamptz',
+ proname => 'pg_format_text_to_timestamptz', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamptz', proargtypes => 'text text',
+ prosrc => 'formatcast_str_to_timestamptz' },
+{ oid => '8657', descr => 'formatted cast: varchar to timestamptz',
+ proname => 'pg_format_varchar_to_timestamptz', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamptz', proargtypes => 'varchar text',
+ prosrc => 'formatcast_str_to_timestamptz' },
+{ oid => '8658', descr => 'formatted cast: bpchar to timestamptz',
+ proname => 'pg_format_bpchar_to_timestamptz', proisstrict => 't', provolatile => 's',
+ prorettype => 'timestamptz', proargtypes => 'bpchar text',
+ prosrc => 'formatcast_bpchar_to_timestamptz' },
+{ oid => '8659', descr => 'formatted cast: date to text',
+ proname => 'pg_format_date_to_text', proisstrict => 't', provolatile => 's',
+ prorettype => 'text', proargtypes => 'date text',
+ prosrc => 'formatcast_date_to_text' },
+{ oid => '8660', descr => 'formatted cast: date to varchar',
+ proname => 'pg_format_date_to_varchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'varchar', proargtypes => 'date text',
+ prosrc => 'formatcast_date_to_varchar' },
+{ oid => '8661', descr => 'formatted cast: date to bpchar',
+ proname => 'pg_format_date_to_bpchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'bpchar', proargtypes => 'date text',
+ prosrc => 'formatcast_date_to_bpchar' },
+{ oid => '8662', descr => 'formatted cast: timestamp to text',
+ proname => 'pg_format_timestamp_to_text', proisstrict => 't', provolatile => 's',
+ prorettype => 'text', proargtypes => 'timestamp text',
+ prosrc => 'formatcast_timestamp_to_text' },
+{ oid => '8663', descr => 'formatted cast: timestamp to varchar',
+ proname => 'pg_format_timestamp_to_varchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'varchar', proargtypes => 'timestamp text',
+ prosrc => 'formatcast_timestamp_to_varchar' },
+{ oid => '8664', descr => 'formatted cast: timestamp to bpchar',
+ proname => 'pg_format_timestamp_to_bpchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'bpchar', proargtypes => 'timestamp text',
+ prosrc => 'formatcast_timestamp_to_bpchar' },
+{ oid => '8665', descr => 'formatted cast: timestamptz to text',
+ proname => 'pg_format_timestamptz_to_text', proisstrict => 't', provolatile => 's',
+ prorettype => 'text', proargtypes => 'timestamptz text',
+ prosrc => 'formatcast_timestamptz_to_text' },
+{ oid => '8666', descr => 'formatted cast: timestamptz to varchar',
+ proname => 'pg_format_timestamptz_to_varchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'varchar', proargtypes => 'timestamptz text',
+ prosrc => 'formatcast_timestamptz_to_varchar' },
+{ oid => '8667', descr => 'formatted cast: timestamptz to bpchar',
+ proname => 'pg_format_timestamptz_to_bpchar', proisstrict => 't', provolatile => 's',
+ prorettype => 'bpchar', proargtypes => 'timestamptz text',
+ prosrc => 'formatcast_timestamptz_to_bpchar' },
{ oid => '1768', descr => 'format interval to text',
proname => 'to_char', provolatile => 's', prorettype => 'text',
proargtypes => 'interval text', prosrc => 'interval_to_char' },
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out
index 7d064024b3a..bc5b37f9cc4 100644
--- a/src/test/regress/expected/expressions.out
+++ b/src/test/regress/expected/expressions.out
@@ -565,21 +565,22 @@ rollback;
-- CAST(expr AS type FORMAT format_expr)
--
-- A FORMAT clause is resolved through a registered formatter (see CREATE
--- FORMATTER) and never falls back to an ordinary cast. No formatters are
--- defined in this test, so these casts fail with a missing-formatter error;
--- this confirms the FORMAT clause is neither ignored nor treated as an
--- ordinary cast. An unknown-type source literal is coerced to text first,
--- so the lookup key is (text, target).
--- basic form (looks up a formatter for (text, date))
-SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
-ERROR: formatter for cast from type text to type date does not exist
-LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
+-- FORMATTER) and never falls back to an ordinary cast. The type pairs used
+-- here have no formatter (the built-in formatters cover datetime/string pairs,
+-- not these), so these casts fail with a missing-formatter error even though an
+-- ordinary cast would succeed; this confirms the FORMAT clause is neither
+-- ignored nor treated as an ordinary cast. An unknown-type source literal is
+-- coerced to text first, so the lookup key is (text, target).
+-- basic form (looks up a formatter for (text, integer); none exists)
+SELECT CAST('42' AS integer FORMAT 'whatever');
+ERROR: formatter for cast from type text to type integer does not exist
+LINE 1: SELECT CAST('42' AS integer FORMAT 'whatever');
^
HINT: Use CREATE FORMATTER to define a formatter for this type pair.
-- the format may be a general expression, not just a string literal
-SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
-ERROR: formatter for cast from type text to type date does not exist
-LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
+SELECT CAST('42' AS integer FORMAT 'what' || 'ever');
+ERROR: formatter for cast from type text to type integer does not exist
+LINE 1: SELECT CAST('42' AS integer FORMAT 'what' || 'ever');
^
HINT: Use CREATE FORMATTER to define a formatter for this type pair.
-- a no-op-looking cast must not be relabeled away; it needs a (text, text) formatter
diff --git a/src/test/regress/expected/formatters.out b/src/test/regress/expected/formatters.out
index 6a47f007f12..2d2cd5d01fa 100644
--- a/src/test/regress/expected/formatters.out
+++ b/src/test/regress/expected/formatters.out
@@ -59,7 +59,9 @@ CREATE FUNCTION fmt_bad_set(integer, text) RETURNS SETOF text
CREATE FORMATTER FOR CAST (integer AS text)
WITH FUNCTION fmt_bad_set(integer, text); -- set-returning rejected
ERROR: formatter function must not return a set
--- No pseudo-types
+-- No pseudo-types: a formatter's source/target may not be a pseudo-type, so
+-- CREATE FORMATTER on a polymorphic (anyelement) source is intentionally
+-- rejected.
CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT $2 $$;
CREATE FORMATTER FOR CAST (anyelement AS text)
@@ -357,3 +359,281 @@ SELECT count(*) FROM pg_class WHERE relname = 'formatter_chain_view';
0
(1 row)
+-- ====================================================================
+-- Built-in formatters: SQL-standard-style (SQL:2016/SQL:2023) formatted casts
+-- between character-string and datetime types, registered in pg_formatter and
+-- reached through CoerceViaFormatter (no parser/analyzer hard-coding of
+-- function names, and no fallback to ordinary casts). The built-ins reuse
+-- PostgreSQL's existing to_char/to_date/to_timestamp template engine; they do
+-- not claim byte-for-byte conformance to every SQL-standard template rule.
+-- Included: date, timestamp, timestamptz with text/varchar/bpchar on the other
+-- side. Deferred: time, timetz, numeric, interval, and full standard
+-- template-token coverage.
+-- ====================================================================
+-- Pin the time zone so timestamptz results are stable across machines.
+SET TimeZone = 'UTC';
+-- text -> date (the source is an unknown-type literal coerced to text, so this
+-- also exercises Patch 3's unknown -> text handling).
+SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+-- varchar source -> date
+SELECT CAST('2026-06-28'::varchar AS date FORMAT 'YYYY-MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+-- bpchar source -> date: a CHAR(10) holds the value exactly, and a wider
+-- CHAR(20) pads with blanks that the formatter trims before parsing.
+SELECT CAST('2026-06-28'::char(10) AS date FORMAT 'YYYY-MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+SELECT CAST('2026-06-28'::char(20) AS date FORMAT 'YYYY-MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+-- The built-in formatter functions are STRICT: a NULL source or a NULL FORMAT
+-- expression yields NULL without invoking the formatter.
+SELECT CAST(NULL::text AS date FORMAT 'YYYY-MM-DD') IS NULL AS null_src;
+ null_src
+----------
+ t
+(1 row)
+
+SELECT CAST('2026-06-28' AS date FORMAT NULL::text) IS NULL AS null_fmt;
+ null_fmt
+----------
+ t
+(1 row)
+
+SELECT CAST(NULL::date AS text FORMAT 'YYYY-MM-DD') IS NULL AS null_src_out;
+ null_src_out
+--------------
+ t
+(1 row)
+
+-- date -> text / varchar
+SELECT CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD');
+ text
+------------
+ 2026-06-28
+(1 row)
+
+SELECT CAST(date '2026-06-28' AS varchar FORMAT 'YYYY-MM-DD');
+ varchar
+------------
+ 2026-06-28
+(1 row)
+
+-- date -> varchar with a typmod: the declared length is enforced by the
+-- ordinary coercion layered above the formatter (here it truncates to 7).
+SELECT CAST(date '2026-06-28' AS varchar(7) FORMAT 'YYYY-MM-DD');
+ varchar
+---------
+ 2026-06
+(1 row)
+
+-- date -> bpchar: AS char(12) pads to the declared length; bare AS char is
+-- char(1), so the ordinary coercion truncates to one character. The wrapper
+-- returns a text varlena that is a valid bpchar value.
+SELECT CAST(date '2026-06-28' AS char(12) FORMAT 'YYYY-MM-DD');
+ bpchar
+--------------
+ 2026-06-28
+(1 row)
+
+SELECT CAST(date '2026-06-28' AS char FORMAT 'YYYY-MM-DD');
+ bpchar
+--------
+ 2
+(1 row)
+
+-- Collation: a formatted cast is collated like the function call it executes.
+-- A collatable result type (text) must get a real result collation, not
+-- InvalidOid; "collation for" reports "default" (it would be NULL if the
+-- result collation were unset), and an explicit COLLATE on the result works.
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD'));
+ pg_collation_for
+------------------
+ "default"
+(1 row)
+
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD') COLLATE "C");
+ pg_collation_for
+------------------
+ "C"
+(1 row)
+
+-- An explicit COLLATE on the FORMAT argument flows in as the input collation.
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text
+ FORMAT 'YYYY-MM-DD'::text COLLATE "C"));
+ pg_collation_for
+------------------
+ "C"
+(1 row)
+
+-- timestamp without time zone, both directions
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+ timestamp
+---------------------
+ 2026-06-28 13:45:30
+(1 row)
+
+SELECT CAST(timestamp '2026-06-28 13:45:30' AS text FORMAT 'YYYY-MM-DD HH24:MI:SS');
+ text
+---------------------
+ 2026-06-28 13:45:30
+(1 row)
+
+-- a date-only template is widened to timestamp (midnight)
+SELECT CAST('2026-06-28' AS timestamp FORMAT 'YYYY-MM-DD');
+ timestamp
+---------------------
+ 2026-06-28 00:00:00
+(1 row)
+
+-- The built-ins reuse PostgreSQL's existing formatting parser: a template with
+-- no time-zone field consumes only the fields it names, so a trailing time zone
+-- in the input is simply ignored (existing to_timestamp() behavior, unchanged).
+SELECT CAST('2026-06-28 13:45:30+05' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+ timestamp
+---------------------
+ 2026-06-28 13:45:30
+(1 row)
+
+-- But a template that explicitly contains a time-zone field is rejected for a
+-- timestamp-without-time-zone target (we must not fold a zone into a zoneless
+-- result via the session TimeZone).
+SELECT CAST('2026-06-28 13:45:30+05' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+ERROR: FORMAT template for a cast to timestamp without time zone must not specify a time zone
+-- timestamp WITHOUT time zone must not depend on the session TimeZone: the same
+-- (unzoned) input/template yields the same wall-clock value under any zone.
+SET TimeZone = 'America/Los_Angeles';
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+ timestamp
+---------------------
+ 2026-06-28 13:45:30
+(1 row)
+
+SET TimeZone = 'UTC';
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+ timestamp
+---------------------
+ 2026-06-28 13:45:30
+(1 row)
+
+-- timestamp with time zone, both directions (TimeZone is UTC, set above)
+SELECT CAST('2026-06-28 13:45:30 +00' AS timestamptz FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+ timestamptz
+------------------------
+ 2026-06-28 13:45:30+00
+(1 row)
+
+SELECT CAST(timestamptz '2026-06-28 13:45:30+00' AS text FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+ text
+-------------------------
+ 2026-06-28 13:45:30 +00
+(1 row)
+
+-- A sampling of SQL-standard-style datetime template tokens, exercised through
+-- the existing to_char/to_timestamp engine. TZM and SSSSS are supported here;
+-- this is not exhaustive standard-token coverage.
+SELECT CAST(timestamptz '2026-06-28 13:45:30+00' AS text
+ FORMAT 'YYYY-MM-DD"T"HH24:MI:SS TZH:TZM');
+ text
+----------------------------
+ 2026-06-28T13:45:30 +00:00
+(1 row)
+
+SELECT CAST(timestamp '2026-06-28 13:45:30' AS text FORMAT 'SSSSS');
+ text
+-------
+ 49530
+(1 row)
+
+-- Fractional seconds (FFn) are accepted by to_char in the output direction.
+SELECT CAST(timestamp '2026-06-28 13:45:30.123456' AS text
+ FORMAT 'HH24:MI:SS.FF6');
+ text
+-----------------
+ 13:45:30.123456
+(1 row)
+
+-- Empty FORMAT template: stable, never a crash. In the parse direction the
+-- underlying to_date() engine parses no fields and returns the all-defaults
+-- date; in the format direction the wrappers mirror to_char(value, ''), which
+-- is NULL.
+SELECT CAST('2026-06-28' AS date FORMAT '');
+ date
+---------------
+ 0001-01-01 BC
+(1 row)
+
+SELECT CAST(date '2026-06-28' AS text FORMAT '') IS NULL AS empty_fmt_out;
+ empty_fmt_out
+---------------
+ t
+(1 row)
+
+-- The FORMAT clause remains a PostgreSQL extension: it may be any expression
+-- coercible to text, not just a string literal.
+SELECT CAST('2026-' || '06-28' AS date FORMAT 'YYYY-' || 'MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+-- A FORMAT clause never falls back to an ordinary cast: an integer -> date
+-- pair has no formatter (and no ordinary cast), so it errors.
+SELECT CAST(5 AS date FORMAT 'YYYY');
+ERROR: formatter for cast from type integer to type date does not exist
+LINE 1: SELECT CAST(5 AS date FORMAT 'YYYY');
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
+-- A built-in formatter occupies its (source, target) pair, so a user
+-- CREATE FORMATTER for the same pair fails with the usual duplicate error.
+CREATE FUNCTION my_text_to_date(text, text) RETURNS date
+ LANGUAGE sql IMMUTABLE RETURN to_date($1, $2);
+CREATE FORMATTER FOR CAST (text AS date)
+ WITH FUNCTION my_text_to_date(text, text); -- fails: already exists
+ERROR: formatter for cast from type text to type date already exists
+DROP FUNCTION my_text_to_date(text, text);
+-- Built-in formatter rows are system objects (their OIDs are in the pinned
+-- range), so they cannot be dropped.
+DROP FORMATTER FOR CAST (text AS date); -- fails: pinned system object
+ERROR: cannot drop formatter for cast from text to date because it is required by the database system
+-- A view over a built-in formatted cast deparses back to CAST(... FORMAT ...).
+CREATE VIEW builtin_formatter_view AS
+ SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD') AS d;
+SELECT pg_get_viewdef('builtin_formatter_view'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------------------------------
+ SELECT CAST('2026-06-28'::text AS date FORMAT 'YYYY-MM-DD'::text) AS d;
+(1 row)
+
+SELECT * FROM builtin_formatter_view;
+ d
+------------
+ 2026-06-28
+(1 row)
+
+DROP VIEW builtin_formatter_view;
+-- Patch 3 added a new expression node; make sure query jumbling handles it.
+SET compute_query_id = on;
+SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD');
+ date
+------------
+ 2026-06-28
+(1 row)
+
+RESET compute_query_id;
+RESET TimeZone;
diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sql
index 46575bc410d..67aef429398 100644
--- a/src/test/regress/sql/expressions.sql
+++ b/src/test/regress/sql/expressions.sql
@@ -306,17 +306,18 @@ rollback;
-- CAST(expr AS type FORMAT format_expr)
--
-- A FORMAT clause is resolved through a registered formatter (see CREATE
--- FORMATTER) and never falls back to an ordinary cast. No formatters are
--- defined in this test, so these casts fail with a missing-formatter error;
--- this confirms the FORMAT clause is neither ignored nor treated as an
--- ordinary cast. An unknown-type source literal is coerced to text first,
--- so the lookup key is (text, target).
+-- FORMATTER) and never falls back to an ordinary cast. The type pairs used
+-- here have no formatter (the built-in formatters cover datetime/string pairs,
+-- not these), so these casts fail with a missing-formatter error even though an
+-- ordinary cast would succeed; this confirms the FORMAT clause is neither
+-- ignored nor treated as an ordinary cast. An unknown-type source literal is
+-- coerced to text first, so the lookup key is (text, target).
--- basic form (looks up a formatter for (text, date))
-SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
+-- basic form (looks up a formatter for (text, integer); none exists)
+SELECT CAST('42' AS integer FORMAT 'whatever');
-- the format may be a general expression, not just a string literal
-SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
+SELECT CAST('42' AS integer FORMAT 'what' || 'ever');
-- a no-op-looking cast must not be relabeled away; it needs a (text, text) formatter
SELECT CAST('abc'::text AS text FORMAT 'whatever');
diff --git a/src/test/regress/sql/formatters.sql b/src/test/regress/sql/formatters.sql
index 2c9b4351652..9ad8e92c42b 100644
--- a/src/test/regress/sql/formatters.sql
+++ b/src/test/regress/sql/formatters.sql
@@ -54,7 +54,9 @@ CREATE FUNCTION fmt_bad_set(integer, text) RETURNS SETOF text
CREATE FORMATTER FOR CAST (integer AS text)
WITH FUNCTION fmt_bad_set(integer, text); -- set-returning rejected
--- No pseudo-types
+-- No pseudo-types: a formatter's source/target may not be a pseudo-type, so
+-- CREATE FORMATTER on a polymorphic (anyelement) source is intentionally
+-- rejected.
CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$ SELECT $2 $$;
CREATE FORMATTER FOR CAST (anyelement AS text)
@@ -236,3 +238,130 @@ DROP FUNCTION i2t_chain(integer, text) CASCADE; -- drops formatter and view
SELECT count(*) FROM pg_formatter
WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
SELECT count(*) FROM pg_class WHERE relname = 'formatter_chain_view';
+
+-- ====================================================================
+-- Built-in formatters: SQL-standard-style (SQL:2016/SQL:2023) formatted casts
+-- between character-string and datetime types, registered in pg_formatter and
+-- reached through CoerceViaFormatter (no parser/analyzer hard-coding of
+-- function names, and no fallback to ordinary casts). The built-ins reuse
+-- PostgreSQL's existing to_char/to_date/to_timestamp template engine; they do
+-- not claim byte-for-byte conformance to every SQL-standard template rule.
+-- Included: date, timestamp, timestamptz with text/varchar/bpchar on the other
+-- side. Deferred: time, timetz, numeric, interval, and full standard
+-- template-token coverage.
+-- ====================================================================
+-- Pin the time zone so timestamptz results are stable across machines.
+SET TimeZone = 'UTC';
+
+-- text -> date (the source is an unknown-type literal coerced to text, so this
+-- also exercises Patch 3's unknown -> text handling).
+SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD');
+-- varchar source -> date
+SELECT CAST('2026-06-28'::varchar AS date FORMAT 'YYYY-MM-DD');
+-- bpchar source -> date: a CHAR(10) holds the value exactly, and a wider
+-- CHAR(20) pads with blanks that the formatter trims before parsing.
+SELECT CAST('2026-06-28'::char(10) AS date FORMAT 'YYYY-MM-DD');
+SELECT CAST('2026-06-28'::char(20) AS date FORMAT 'YYYY-MM-DD');
+
+-- The built-in formatter functions are STRICT: a NULL source or a NULL FORMAT
+-- expression yields NULL without invoking the formatter.
+SELECT CAST(NULL::text AS date FORMAT 'YYYY-MM-DD') IS NULL AS null_src;
+SELECT CAST('2026-06-28' AS date FORMAT NULL::text) IS NULL AS null_fmt;
+SELECT CAST(NULL::date AS text FORMAT 'YYYY-MM-DD') IS NULL AS null_src_out;
+
+-- date -> text / varchar
+SELECT CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD');
+SELECT CAST(date '2026-06-28' AS varchar FORMAT 'YYYY-MM-DD');
+-- date -> varchar with a typmod: the declared length is enforced by the
+-- ordinary coercion layered above the formatter (here it truncates to 7).
+SELECT CAST(date '2026-06-28' AS varchar(7) FORMAT 'YYYY-MM-DD');
+-- date -> bpchar: AS char(12) pads to the declared length; bare AS char is
+-- char(1), so the ordinary coercion truncates to one character. The wrapper
+-- returns a text varlena that is a valid bpchar value.
+SELECT CAST(date '2026-06-28' AS char(12) FORMAT 'YYYY-MM-DD');
+SELECT CAST(date '2026-06-28' AS char FORMAT 'YYYY-MM-DD');
+
+-- Collation: a formatted cast is collated like the function call it executes.
+-- A collatable result type (text) must get a real result collation, not
+-- InvalidOid; "collation for" reports "default" (it would be NULL if the
+-- result collation were unset), and an explicit COLLATE on the result works.
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD'));
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text FORMAT 'YYYY-MM-DD') COLLATE "C");
+-- An explicit COLLATE on the FORMAT argument flows in as the input collation.
+SELECT pg_collation_for(CAST(date '2026-06-28' AS text
+ FORMAT 'YYYY-MM-DD'::text COLLATE "C"));
+
+-- timestamp without time zone, both directions
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+SELECT CAST(timestamp '2026-06-28 13:45:30' AS text FORMAT 'YYYY-MM-DD HH24:MI:SS');
+-- a date-only template is widened to timestamp (midnight)
+SELECT CAST('2026-06-28' AS timestamp FORMAT 'YYYY-MM-DD');
+-- The built-ins reuse PostgreSQL's existing formatting parser: a template with
+-- no time-zone field consumes only the fields it names, so a trailing time zone
+-- in the input is simply ignored (existing to_timestamp() behavior, unchanged).
+SELECT CAST('2026-06-28 13:45:30+05' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+-- But a template that explicitly contains a time-zone field is rejected for a
+-- timestamp-without-time-zone target (we must not fold a zone into a zoneless
+-- result via the session TimeZone).
+SELECT CAST('2026-06-28 13:45:30+05' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+-- timestamp WITHOUT time zone must not depend on the session TimeZone: the same
+-- (unzoned) input/template yields the same wall-clock value under any zone.
+SET TimeZone = 'America/Los_Angeles';
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+SET TimeZone = 'UTC';
+SELECT CAST('2026-06-28 13:45:30' AS timestamp FORMAT 'YYYY-MM-DD HH24:MI:SS');
+
+-- timestamp with time zone, both directions (TimeZone is UTC, set above)
+SELECT CAST('2026-06-28 13:45:30 +00' AS timestamptz FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+SELECT CAST(timestamptz '2026-06-28 13:45:30+00' AS text FORMAT 'YYYY-MM-DD HH24:MI:SS TZH');
+
+-- A sampling of SQL-standard-style datetime template tokens, exercised through
+-- the existing to_char/to_timestamp engine. TZM and SSSSS are supported here;
+-- this is not exhaustive standard-token coverage.
+SELECT CAST(timestamptz '2026-06-28 13:45:30+00' AS text
+ FORMAT 'YYYY-MM-DD"T"HH24:MI:SS TZH:TZM');
+SELECT CAST(timestamp '2026-06-28 13:45:30' AS text FORMAT 'SSSSS');
+-- Fractional seconds (FFn) are accepted by to_char in the output direction.
+SELECT CAST(timestamp '2026-06-28 13:45:30.123456' AS text
+ FORMAT 'HH24:MI:SS.FF6');
+
+-- Empty FORMAT template: stable, never a crash. In the parse direction the
+-- underlying to_date() engine parses no fields and returns the all-defaults
+-- date; in the format direction the wrappers mirror to_char(value, ''), which
+-- is NULL.
+SELECT CAST('2026-06-28' AS date FORMAT '');
+SELECT CAST(date '2026-06-28' AS text FORMAT '') IS NULL AS empty_fmt_out;
+
+-- The FORMAT clause remains a PostgreSQL extension: it may be any expression
+-- coercible to text, not just a string literal.
+SELECT CAST('2026-' || '06-28' AS date FORMAT 'YYYY-' || 'MM-DD');
+
+-- A FORMAT clause never falls back to an ordinary cast: an integer -> date
+-- pair has no formatter (and no ordinary cast), so it errors.
+SELECT CAST(5 AS date FORMAT 'YYYY');
+
+-- A built-in formatter occupies its (source, target) pair, so a user
+-- CREATE FORMATTER for the same pair fails with the usual duplicate error.
+CREATE FUNCTION my_text_to_date(text, text) RETURNS date
+ LANGUAGE sql IMMUTABLE RETURN to_date($1, $2);
+CREATE FORMATTER FOR CAST (text AS date)
+ WITH FUNCTION my_text_to_date(text, text); -- fails: already exists
+DROP FUNCTION my_text_to_date(text, text);
+
+-- Built-in formatter rows are system objects (their OIDs are in the pinned
+-- range), so they cannot be dropped.
+DROP FORMATTER FOR CAST (text AS date); -- fails: pinned system object
+
+-- A view over a built-in formatted cast deparses back to CAST(... FORMAT ...).
+CREATE VIEW builtin_formatter_view AS
+ SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD') AS d;
+SELECT pg_get_viewdef('builtin_formatter_view'::regclass, true);
+SELECT * FROM builtin_formatter_view;
+DROP VIEW builtin_formatter_view;
+
+-- Patch 3 added a new expression node; make sure query jumbling handles it.
+SET compute_query_id = on;
+SELECT CAST('2026-06-28' AS date FORMAT 'YYYY-MM-DD');
+RESET compute_query_id;
+
+RESET TimeZone;
--
2.54.0
[application/octet-stream] 0003-Use-CoerceViaFormatter-for-CAST-FORMAT.patch (44.4K, ../CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com/3-0003-Use-CoerceViaFormatter-for-CAST-FORMAT.patch)
download | inline diff:
From de709c69237e3304b7369c897842dc362d73ee4f Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Wed, 24 Jun 2026 20:46:00 -0700
Subject: [PATCH] Resolve CAST ... FORMAT with CoerceViaFormatter
Resolve formatted casts through pg_formatter during parse analysis. The
FORMAT expression is transformed and coerced to text, unknown source
literals are treated as text, and formatter lookup is performed by exact
source and target type. If no formatter exists, the cast fails; a FORMAT
clause never falls back to ordinary cast resolution.
Represent the analyzed expression with a CoerceViaFormatter node. The
node preserves CAST ... FORMAT syntax for ruleutils and pg_dump, and
records a dependency on the pg_formatter row as well as on the formatter
function. Execution still uses the ordinary function-call machinery via
ExecInitFunc, so no new executor opcode or JIT support is needed.
Target typmods and domain checks are enforced by the existing coercion
machinery layered above the node.
---
src/backend/catalog/dependency.c | 23 ++
src/backend/executor/execExpr.c | 18 ++
src/backend/nodes/nodeFuncs.c | 53 +++++
src/backend/parser/parse_expr.c | 136 ++++++++++-
src/backend/utils/adt/ruleutils.c | 19 ++
src/backend/utils/cache/lsyscache.c | 37 +++
src/include/nodes/primnodes.h | 38 ++++
src/include/utils/lsyscache.h | 2 +
src/test/regress/expected/expressions.out | 30 +--
src/test/regress/expected/formatters.out | 263 +++++++++++++++++++++-
src/test/regress/sql/expressions.sql | 14 +-
src/test/regress/sql/formatters.sql | 153 ++++++++++++-
12 files changed, 742 insertions(+), 44 deletions(-)
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7eeac91264f..61e9a28361d 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -2131,6 +2131,29 @@ find_expr_references_walker(Node *node,
add_object_address(CollationRelationId, iocoerce->resultcollid, 0,
context->addrs);
}
+ else if (IsA(node, CoerceViaFormatter))
+ {
+ CoerceViaFormatter *fmt = (CoerceViaFormatter *) node;
+
+ /* depend on the result type */
+ add_object_address(TypeRelationId, fmt->resulttype, 0,
+ context->addrs);
+ /* depend on the formatter function */
+ add_object_address(ProcedureRelationId, fmt->formatterfunc, 0,
+ context->addrs);
+ /*
+ * Also depend on the pg_formatter row itself, so that DROP FORMATTER
+ * is refused (or cascades) while a stored expression uses it.
+ */
+ if (OidIsValid(fmt->formatterid))
+ add_object_address(FormatterRelationId, fmt->formatterid, 0,
+ context->addrs);
+ /* the collation might not be referenced anywhere else, either */
+ if (OidIsValid(fmt->resultcollid) &&
+ fmt->resultcollid != DEFAULT_COLLATION_OID)
+ add_object_address(CollationRelationId, fmt->resultcollid, 0,
+ context->addrs);
+ }
else if (IsA(node, ArrayCoerceExpr))
{
ArrayCoerceExpr *acoerce = (ArrayCoerceExpr *) node;
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index cfea7e160c2..00ef3a8708f 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1200,6 +1200,24 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_CoerceViaFormatter:
+ {
+ CoerceViaFormatter *fmt = (CoerceViaFormatter *) node;
+
+ /*
+ * A formatted cast is executed exactly like a call to its
+ * formatter function: formatterfunc(arg, format). Reuse the
+ * ordinary function-call setup, which also performs the
+ * run-time EXECUTE permission check on the formatter function.
+ */
+ ExecInitFunc(&scratch, node,
+ list_make2(fmt->arg, fmt->format),
+ fmt->formatterfunc, fmt->inputcollid,
+ state);
+ ExprEvalPushStep(state, &scratch);
+ break;
+ }
+
case T_OpExpr:
{
OpExpr *op = (OpExpr *) node;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 66495546179..eac3fee0404 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -182,6 +182,9 @@ exprType(const Node *expr)
case T_CoerceViaIO:
type = ((const CoerceViaIO *) expr)->resulttype;
break;
+ case T_CoerceViaFormatter:
+ type = ((const CoerceViaFormatter *) expr)->resulttype;
+ break;
case T_ArrayCoerceExpr:
type = ((const ArrayCoerceExpr *) expr)->resulttype;
break;
@@ -531,6 +534,8 @@ exprTypmod(const Node *expr)
break;
case T_CoerceToDomain:
return ((const CoerceToDomain *) expr)->resulttypmod;
+ case T_CoerceViaFormatter:
+ return ((const CoerceViaFormatter *) expr)->resulttypmod;
case T_CoerceToDomainValue:
return ((const CoerceToDomainValue *) expr)->typeMod;
case T_SetToDefault:
@@ -943,6 +948,9 @@ exprCollation(const Node *expr)
case T_CoerceViaIO:
coll = ((const CoerceViaIO *) expr)->resultcollid;
break;
+ case T_CoerceViaFormatter:
+ coll = ((const CoerceViaFormatter *) expr)->resultcollid;
+ break;
case T_ArrayCoerceExpr:
coll = ((const ArrayCoerceExpr *) expr)->resultcollid;
break;
@@ -1227,6 +1235,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_CoerceViaIO:
((CoerceViaIO *) expr)->resultcollid = collation;
break;
+ case T_CoerceViaFormatter:
+ ((CoerceViaFormatter *) expr)->resultcollid = collation;
+ break;
case T_ArrayCoerceExpr:
((ArrayCoerceExpr *) expr)->resultcollid = collation;
break;
@@ -1349,6 +1360,9 @@ exprSetInputCollation(Node *expr, Oid inputcollation)
case T_FuncExpr:
((FuncExpr *) expr)->inputcollid = inputcollation;
break;
+ case T_CoerceViaFormatter:
+ ((CoerceViaFormatter *) expr)->inputcollid = inputcollation;
+ break;
case T_OpExpr:
((OpExpr *) expr)->inputcollid = inputcollation;
break;
@@ -1527,6 +1541,15 @@ exprLocation(const Node *expr)
exprLocation((Node *) cexpr->arg));
}
break;
+ case T_CoerceViaFormatter:
+ {
+ const CoerceViaFormatter *cexpr = (const CoerceViaFormatter *) expr;
+
+ /* Much as above */
+ loc = leftmostLoc(cexpr->location,
+ exprLocation((Node *) cexpr->arg));
+ }
+ break;
case T_ArrayCoerceExpr:
{
const ArrayCoerceExpr *cexpr = (const ArrayCoerceExpr *) expr;
@@ -1994,6 +2017,15 @@ check_functions_in_node(Node *node, check_function_callback checker,
return true;
}
break;
+ case T_CoerceViaFormatter:
+ {
+ CoerceViaFormatter *expr = (CoerceViaFormatter *) node;
+
+ /* check the formatter function */
+ if (checker(expr->formatterfunc, context))
+ return true;
+ }
+ break;
case T_RowCompareExpr:
{
RowCompareExpr *rcexpr = (RowCompareExpr *) node;
@@ -2296,6 +2328,16 @@ expression_tree_walker_impl(Node *node,
return WALK(((RelabelType *) node)->arg);
case T_CoerceViaIO:
return WALK(((CoerceViaIO *) node)->arg);
+ case T_CoerceViaFormatter:
+ {
+ CoerceViaFormatter *fmt = (CoerceViaFormatter *) node;
+
+ if (WALK(fmt->arg))
+ return true;
+ if (WALK(fmt->format))
+ return true;
+ }
+ break;
case T_ArrayCoerceExpr:
{
ArrayCoerceExpr *acoerce = (ArrayCoerceExpr *) node;
@@ -3318,6 +3360,17 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_CoerceViaFormatter:
+ {
+ CoerceViaFormatter *fmtcoerce = (CoerceViaFormatter *) node;
+ CoerceViaFormatter *newnode;
+
+ FLATCOPY(newnode, fmtcoerce, CoerceViaFormatter);
+ MUTATE(newnode->arg, fmtcoerce->arg, Expr *);
+ MUTATE(newnode->format, fmtcoerce->format, Expr *);
+ return (Node *) newnode;
+ }
+ break;
case T_ArrayCoerceExpr:
{
ArrayCoerceExpr *acoerce = (ArrayCoerceExpr *) node;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 1729ba56013..243c9121700 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -78,6 +78,7 @@ static Node *transformWholeRowRef(ParseState *pstate,
int sublevels_up, int location);
static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
+static Node *transformFormattedTypeCast(ParseState *pstate, TypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
JsonObjectConstructor *ctor);
@@ -2744,17 +2745,12 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
int location;
/*
- * Formatted casts (CAST(expr AS type FORMAT format_expr)) are parsed and
- * represented in the parse tree, but formatter resolution is not yet
- * implemented. Reject such casts here rather than silently ignoring the
- * FORMAT clause.
+ * A FORMAT clause turns this into a formatted cast, which is resolved
+ * exclusively through the pg_formatter catalog (never through ordinary
+ * cast rules). Handle it in a separate code path.
*/
if (tc->format != NULL)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("formatted casts are not implemented yet"),
- errdetail("No formatter resolution mechanism is available."),
- parser_errposition(pstate, exprLocation(tc->format))));
+ return transformFormattedTypeCast(pstate, tc);
/* Look up the type name first */
typenameTypeIdAndMod(pstate, tc->typeName, &targetType, &targetTypmod);
@@ -2824,6 +2820,128 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
return result;
}
+/*
+ * Handle a formatted cast: CAST(arg AS typeName FORMAT format).
+ *
+ * Resolved exclusively through the pg_formatter catalog, keyed by
+ * (source type, target type). We build a CoerceViaFormatter node, which at
+ * execution time calls the registered formatter function with the source
+ * value and the FORMAT expression (coerced to text). Using a dedicated node
+ * (rather than a bare FuncExpr) lets the expression deparse back to
+ * CAST ... FORMAT and depend on the pg_formatter row.
+ */
+static Node *
+transformFormattedTypeCast(ParseState *pstate, TypeCast *tc)
+{
+ Node *expr;
+ Node *fmt;
+ Oid sourceType;
+ Oid targetType;
+ int32 targetTypmod;
+ Oid formatterid;
+ Oid fmtfuncid = InvalidOid;
+ Oid funcrettype;
+ CoerceViaFormatter *cvf;
+ Node *result;
+ int location;
+
+ /* Resolve the declared target type, exactly as an ordinary cast does. */
+ typenameTypeIdAndMod(pstate, tc->typeName, &targetType, &targetTypmod);
+
+ /* Transform the source expression. */
+ expr = transformExprRecurse(pstate, tc->arg);
+ sourceType = exprType(expr);
+
+ /*
+ * An unknown-type source (typically a bare string literal or untyped
+ * NULL) is coerced to text before the formatter lookup, so that, e.g.,
+ * CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD') uses a formatter
+ * registered for (text, date) rather than requiring one for (unknown,
+ * date).
+ */
+ if (sourceType == UNKNOWNOID)
+ {
+ expr = coerce_to_specific_type(pstate, expr, TEXTOID, "CAST");
+ sourceType = exprType(expr);
+ }
+
+ location = tc->location;
+ if (location < 0)
+ location = tc->typeName->location;
+
+ /*
+ * Transform the FORMAT expression and coerce it to text before the
+ * formatter lookup, so an invalid FORMAT expression reports a normal
+ * error rather than being masked by a missing-formatter error.
+ */
+ fmt = transformExprRecurse(pstate, tc->format);
+ fmt = coerce_to_specific_type(pstate, fmt, TEXTOID, "FORMAT");
+
+ /*
+ * Look up the formatter for this (source, target) pair. A FORMAT clause
+ * never falls back to ordinary cast resolution.
+ */
+ formatterid = get_formatter_for_cast(sourceType, targetType, &fmtfuncid);
+ if (!OidIsValid(formatterid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("formatter for cast from type %s to type %s does not exist",
+ format_type_be(sourceType),
+ format_type_be(targetType)),
+ errhint("Use CREATE FORMATTER to define a formatter for this type pair."),
+ parser_errposition(pstate, location)));
+
+ /*
+ * Build the CoerceViaFormatter node. Its result type is the formatter
+ * function's return type, which CREATE FORMATTER guarantees equals the
+ * target type.
+ *
+ * The collation fields are left unset here; they are assigned later by
+ * parse_collate.c, where CoerceViaFormatter takes the same general n-ary
+ * expression path as FuncExpr (its result/input collation get/set hooks
+ * live in nodeFuncs.c).
+ */
+ funcrettype = get_func_rettype(fmtfuncid);
+ if (!OidIsValid(funcrettype))
+ elog(ERROR, "cache lookup failed for function %u", fmtfuncid);
+ Assert(funcrettype == targetType);
+
+ cvf = makeNode(CoerceViaFormatter);
+ cvf->arg = (Expr *) expr;
+ cvf->format = (Expr *) fmt;
+ cvf->resulttype = funcrettype;
+ cvf->resulttypmod = -1; /* typmod enforced below, if any */
+ cvf->resultcollid = InvalidOid;
+ cvf->inputcollid = InvalidOid;
+ cvf->formatterfunc = fmtfuncid;
+ cvf->formatterid = formatterid;
+ cvf->coercionformat = COERCE_EXPLICIT_CAST;
+ cvf->location = location;
+ result = (Node *) cvf;
+
+ /*
+ * Enforce the declared target type modifier (and any domain constraints)
+ * using the ordinary coercion machinery. In the common case where the
+ * target has no typmod and is not a domain this is a no-op; otherwise it
+ * layers the same length coercion / domain check that an ordinary cast to
+ * the same target would apply.
+ */
+ result = coerce_to_target_type(pstate, result, funcrettype,
+ targetType, targetTypmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ location);
+ if (result == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(funcrettype),
+ format_type_be(targetType)),
+ parser_errposition(pstate, location)));
+
+ return result;
+}
+
/*
* Handle an explicit COLLATE clause.
*
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 88de5c0481c..76f07c85a8f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9447,6 +9447,10 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags)
/* function-like: name(..) or name[..] */
return true;
+ case T_CoerceViaFormatter:
+ /* deparses as CAST(.. FORMAT ..), self-delimiting */
+ return true;
+
/* CASE keywords act as parentheses */
case T_CaseExpr:
return true;
@@ -10310,6 +10314,21 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_CoerceViaFormatter:
+ {
+ CoerceViaFormatter *fmt = (CoerceViaFormatter *) node;
+
+ /* always print the SQL-standard CAST(... FORMAT ...) syntax */
+ appendStringInfoString(buf, "CAST(");
+ get_rule_expr((Node *) fmt->arg, context, false);
+ appendStringInfo(buf, " AS %s FORMAT ",
+ format_type_with_typemod(fmt->resulttype,
+ fmt->resulttypmod));
+ get_rule_expr((Node *) fmt->format, context, false);
+ appendStringInfoChar(buf, ')');
+ }
+ break;
+
case T_ArrayCoerceExpr:
{
ArrayCoerceExpr *acoerce = (ArrayCoerceExpr *) node;
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 036de5f79ef..a58111b431f 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -27,6 +27,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_formatter.h"
#include "catalog/pg_index.h"
#include "catalog/pg_language.h"
#include "catalog/pg_namespace.h"
@@ -1238,6 +1239,42 @@ get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok)
return oid;
}
+/* ---------- PG_FORMATTER CACHE ---------- */
+
+/*
+ * get_formatter_for_cast
+ *
+ * Given source and target type OIDs, look up the formatter registered
+ * for that (source, target) pair. Returns the pg_formatter row OID, or
+ * InvalidOid if none is registered. If found and formatterfunc is not
+ * NULL, *formatterfunc is set to the formatter function OID.
+ */
+Oid
+get_formatter_for_cast(Oid sourcetypeid, Oid targettypeid, Oid *formatterfunc)
+{
+ HeapTuple tp;
+ Oid result;
+
+ tp = SearchSysCache2(FORMATTERSOURCETARGET,
+ ObjectIdGetDatum(sourcetypeid),
+ ObjectIdGetDatum(targettypeid));
+ if (!HeapTupleIsValid(tp))
+ return InvalidOid;
+
+ {
+ Form_pg_formatter fmt = (Form_pg_formatter) GETSTRUCT(tp);
+
+ /* A valid pg_formatter row always names a formatter function. */
+ Assert(OidIsValid(fmt->fmtfunc));
+
+ result = fmt->oid;
+ if (formatterfunc != NULL)
+ *formatterfunc = fmt->fmtfunc;
+ }
+ ReleaseSysCache(tp);
+ return result;
+}
+
/* ---------- COLLATION CACHE ---------- */
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bb05aeebee4..f5752fefee3 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1230,6 +1230,44 @@ typedef struct CoerceViaIO
ParseLoc location; /* token location, or -1 if unknown */
} CoerceViaIO;
+/* ----------------
+ * CoerceViaFormatter
+ *
+ * CoerceViaFormatter represents CAST(arg AS resulttype FORMAT format), a
+ * formatted cast resolved through the pg_formatter catalog. It calls the
+ * registered formatter function with the source value and the FORMAT
+ * expression (already coerced to text), returning the target type:
+ *
+ * formatterfunc(arg, format) returns resulttype
+ *
+ * Unlike a plain FuncExpr, this node remembers that it came from
+ * CAST ... FORMAT (so it deparses back to that syntax) and which
+ * pg_formatter row it resolved to (so a stored expression can depend on the
+ * formatter object). It is executed by reusing ordinary function-call
+ * evaluation, so EXECUTE permission on formatterfunc is checked at run time,
+ * as for an ordinary function-backed cast.
+ * ----------------
+ */
+typedef struct CoerceViaFormatter
+{
+ Expr xpr;
+ Expr *arg; /* source expression */
+ Expr *format; /* FORMAT expression, already coerced to text */
+ Oid resulttype; /* output type of the cast */
+ /* output typmod (usually -1; typmod enforcement is layered above) */
+ int32 resulttypmod pg_node_attr(query_jumble_ignore);
+ /* OID of result collation, or InvalidOid if none */
+ Oid resultcollid pg_node_attr(query_jumble_ignore);
+ /* input collation for the formatter function call */
+ Oid inputcollid pg_node_attr(query_jumble_ignore);
+ Oid formatterfunc; /* pg_formatter.fmtfunc */
+ /* pg_formatter row OID this resolved to (for dependencies) */
+ Oid formatterid pg_node_attr(query_jumble_ignore);
+ /* how to display this node */
+ CoercionForm coercionformat pg_node_attr(query_jumble_ignore);
+ ParseLoc location; /* token location, or -1 if unknown */
+} CoerceViaFormatter;
+
/* ----------------
* ArrayCoerceExpr
*
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 865980cb0f1..c946bf6f59b 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -103,6 +103,8 @@ extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
Oid *typid, int32 *typmod, Oid *collid);
extern Datum get_attoptions(Oid relid, int16 attnum);
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok);
+extern Oid get_formatter_for_cast(Oid sourcetypeid, Oid targettypeid,
+ Oid *formatterfunc);
extern char *get_collation_name(Oid colloid);
extern bool get_collation_isdeterministic(Oid colloid);
extern char *get_constraint_name(Oid conoid);
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out
index b2d71dca4fa..7d064024b3a 100644
--- a/src/test/regress/expected/expressions.out
+++ b/src/test/regress/expected/expressions.out
@@ -564,23 +564,27 @@ rollback;
--
-- CAST(expr AS type FORMAT format_expr)
--
--- The FORMAT clause is parsed and stored, but formatter resolution is not
--- implemented yet, so parse analysis must reject it (not ignore it).
--- basic form
+-- A FORMAT clause is resolved through a registered formatter (see CREATE
+-- FORMATTER) and never falls back to an ordinary cast. No formatters are
+-- defined in this test, so these casts fail with a missing-formatter error;
+-- this confirms the FORMAT clause is neither ignored nor treated as an
+-- ordinary cast. An unknown-type source literal is coerced to text first,
+-- so the lookup key is (text, target).
+-- basic form (looks up a formatter for (text, date))
SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
-ERROR: formatted casts are not implemented yet
+ERROR: formatter for cast from type text to type date does not exist
LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
- ^
-DETAIL: No formatter resolution mechanism is available.
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
-- the format may be a general expression, not just a string literal
SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
-ERROR: formatted casts are not implemented yet
+ERROR: formatter for cast from type text to type date does not exist
LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
- ^
-DETAIL: No formatter resolution mechanism is available.
--- a no-op-looking cast must still be rejected, not relabeled away
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
+-- a no-op-looking cast must not be relabeled away; it needs a (text, text) formatter
SELECT CAST('abc'::text AS text FORMAT 'whatever');
-ERROR: formatted casts are not implemented yet
+ERROR: formatter for cast from type text to type text does not exist
LINE 1: SELECT CAST('abc'::text AS text FORMAT 'whatever');
- ^
-DETAIL: No formatter resolution mechanism is available.
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
diff --git a/src/test/regress/expected/formatters.out b/src/test/regress/expected/formatters.out
index bb1f091a1b8..6a47f007f12 100644
--- a/src/test/regress/expected/formatters.out
+++ b/src/test/regress/expected/formatters.out
@@ -1,9 +1,10 @@
--
-- FORMATTERS
--
--- CREATE/DROP FORMATTER registers formatter metadata in pg_formatter,
--- keyed by a (source type, target type) pair. This is catalog and DDL
--- infrastructure only; it does not transform or execute formatted casts.
+-- A formatter registers a function for a (source type, target type) pair; a
+-- CAST(... AS target FORMAT format_expr) is resolved through pg_formatter and
+-- calls that function. This test covers both the CREATE/DROP FORMATTER
+-- catalog DDL and the execution of formatted casts.
-- A simple formatter function with the required signature
-- formatter(source_type, text) returns target_type
CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
@@ -64,14 +65,124 @@ CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
CREATE FORMATTER FOR CAST (anyelement AS text)
WITH FUNCTION fmt_anyel(anyelement, text);
ERROR: source data type anyelement is a pseudo-type
--- Registering a formatter does not enable a formatted cast: the FORMAT
--- clause must not be silently ignored or rewritten to a built-in function,
--- so CAST(... FORMAT ...) is rejected during parse analysis.
-SELECT CAST(5 AS text FORMAT 'YYYY');
-ERROR: formatted casts are not implemented yet
-LINE 1: SELECT CAST(5 AS text FORMAT 'YYYY');
+-- ====================================================================
+-- Execution: a formatted cast resolves through pg_formatter and calls the
+-- registered formatter function.
+-- ====================================================================
+-- basic execution, using the (integer, text) formatter created above
+SELECT CAST(5 AS text FORMAT 'abc');
+ text
+-------
+ 5:abc
+(1 row)
+
+-- the FORMAT expression may be any expression, coerced to text
+SELECT CAST(5 AS text FORMAT 'a' || 'b');
+ text
+------
+ 5:ab
+(1 row)
+
+SELECT CAST(5 AS text FORMAT 123);
+ text
+-------
+ 5:123
+(1 row)
+
+-- The FORMAT expression is parse-analyzed independently of (and before) the
+-- formatter lookup, so an invalid FORMAT expression reports a normal error.
+SELECT CAST(5 AS text FORMAT no_such_column);
+ERROR: column "no_such_column" does not exist
+LINE 1: SELECT CAST(5 AS text FORMAT no_such_column);
^
-DETAIL: No formatter resolution mechanism is available.
+-- Like an ordinary cast that uses a cast function, a formatted cast checks
+-- EXECUTE on the formatter function at use time.
+REVOKE EXECUTE ON FUNCTION int4_to_text_fmt(integer, text) FROM PUBLIC;
+CREATE ROLE regress_formatter_noexec NOLOGIN;
+SET ROLE regress_formatter_noexec;
+SELECT CAST(5 AS text FORMAT 'p'); -- fails: no EXECUTE on formatter function
+ERROR: permission denied for function int4_to_text_fmt
+RESET ROLE;
+DROP ROLE regress_formatter_noexec;
+GRANT EXECUTE ON FUNCTION int4_to_text_fmt(integer, text) TO PUBLIC;
+-- A FORMAT clause never falls back to an ordinary cast: text -> text is a
+-- trivial ordinary cast, but a formatted cast still requires a formatter.
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
+ERROR: formatter for cast from type text to type text does not exist
+LINE 1: SELECT CAST('abc'::text AS text FORMAT 'whatever');
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
+-- An unknown-type source literal is coerced to text first, so this also looks
+-- up (text, text), not (unknown, text).
+SELECT CAST('abc' AS text FORMAT 'fmt');
+ERROR: formatter for cast from type text to type text does not exist
+LINE 1: SELECT CAST('abc' AS text FORMAT 'fmt');
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
+-- A missing formatter is an error even where an ordinary cast would be valid.
+SELECT CAST(5 AS integer FORMAT 'x');
+ERROR: formatter for cast from type integer to type integer does not exist
+LINE 1: SELECT CAST(5 AS integer FORMAT 'x');
+ ^
+HINT: Use CREATE FORMATTER to define a formatter for this type pair.
+-- Define the (text, text) formatter and re-run the text-source cases.
+CREATE FUNCTION text_to_text_fmt(text, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1 || '/' || $2;
+CREATE FORMATTER FOR CAST (text AS text)
+ WITH FUNCTION text_to_text_fmt(text, text);
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
+ text
+--------------
+ abc/whatever
+(1 row)
+
+SELECT CAST('abc' AS text FORMAT 'fmt');
+ text
+---------
+ abc/fmt
+(1 row)
+
+-- A type modifier on the target is enforced through the ordinary coercion path.
+CREATE FUNCTION int4_to_vc_fmt(integer, text) RETURNS varchar
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS varchar)
+ WITH FUNCTION int4_to_vc_fmt(integer, text);
+SELECT CAST(5 AS varchar FORMAT 'XXXX');
+ varchar
+---------
+ 5XXXX
+(1 row)
+
+SELECT CAST(5 AS varchar(3) FORMAT 'XXXX'); -- length 3 enforced
+ varchar
+---------
+ 5XX
+(1 row)
+
+-- Domain target: the formatter must return the domain type, so the domain's
+-- constraints are enforced by the function's result.
+CREATE DOMAIN nonempty_text AS text CHECK (VALUE <> '');
+CREATE FUNCTION text_to_netext_fmt(text, text) RETURNS nonempty_text
+ LANGUAGE sql IMMUTABLE RETURN $2::nonempty_text;
+CREATE FORMATTER FOR CAST (text AS nonempty_text)
+ WITH FUNCTION text_to_netext_fmt(text, text);
+SELECT CAST('z'::text AS nonempty_text FORMAT 'ok');
+ nonempty_text
+---------------
+ ok
+(1 row)
+
+SELECT CAST('z'::text AS nonempty_text FORMAT ''); -- domain check violation
+ERROR: value for domain nonempty_text violates check constraint "nonempty_text_check"
+CONTEXT: SQL function "text_to_netext_fmt" statement 1
+-- Drop the objects created in this execution section.
+DROP FORMATTER FOR CAST (text AS text);
+DROP FUNCTION text_to_text_fmt(text, text);
+DROP FORMATTER FOR CAST (integer AS varchar);
+DROP FUNCTION int4_to_vc_fmt(integer, text);
+DROP FORMATTER FOR CAST (text AS nonempty_text);
+DROP FUNCTION text_to_netext_fmt(text, text);
+DROP DOMAIN nonempty_text;
-- Dependency behavior: the formatter depends on its function.
DROP FUNCTION int4_to_text_fmt(integer, text); -- fails (RESTRICT)
ERROR: cannot drop function int4_to_text_fmt(integer,text) because other objects depend on it
@@ -114,3 +225,135 @@ DROP FUNCTION fmt_bad_arg1(bigint, text);
DROP FUNCTION fmt_bad_ret(integer, text);
DROP FUNCTION fmt_bad_set(integer, text);
DROP FUNCTION fmt_anyel(anyelement, text);
+-- ====================================================================
+-- CoerceViaFormatter: deparse fidelity and dependency on the formatter row
+-- ====================================================================
+CREATE FUNCTION i2t_view(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_view(integer, text);
+-- A view over a formatted cast deparses back to CAST(... FORMAT ...).
+CREATE VIEW formatter_view AS SELECT CAST(5 AS text FORMAT 'abc') AS x;
+SELECT pg_get_viewdef('formatter_view'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT CAST(5 AS text FORMAT 'abc'::text) AS x;
+(1 row)
+
+SELECT * FROM formatter_view;
+ x
+-------
+ 5:abc
+(1 row)
+
+-- The view depends on the pg_formatter row, so DROP FORMATTER RESTRICT fails.
+DROP FORMATTER FOR CAST (integer AS text); -- fails (RESTRICT, view depends)
+ERROR: cannot drop formatter for cast from integer to text because other objects depend on it
+DETAIL: view formatter_view depends on formatter for cast from integer to text
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- ... and CASCADE drops the dependent view.
+DROP FORMATTER FOR CAST (integer AS text) CASCADE;
+NOTICE: drop cascades to view formatter_view
+SELECT count(*) FROM pg_class WHERE relname = 'formatter_view';
+ count
+-------
+ 0
+(1 row)
+
+DROP FUNCTION i2t_view(integer, text);
+-- ====================================================================
+-- Collation: a formatted cast behaves like calling the formatter function
+-- formatterfunc(arg, format). Its result collation is the result type's
+-- collation, and the input collation is derived from the arg and FORMAT
+-- expressions exactly as for an ordinary two-argument function call.
+-- ====================================================================
+-- text_larger(text, text) returns text and is collation-sensitive at the C
+-- level (it reads PG_GET_COLLATION), so it exercises the input collation.
+CREATE FORMATTER FOR CAST (text AS text)
+ WITH FUNCTION pg_catalog.text_larger(text, text);
+-- The default collation flows into the formatter call; if the input collation
+-- were left unset this would fail with "could not determine which collation".
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text);
+ text
+--------
+ banana
+(1 row)
+
+-- An explicit COLLATE on an operand is honored as the input collation.
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text COLLATE "C");
+ text
+--------
+ banana
+(1 row)
+
+-- The result is collatable, so an explicit COLLATE on the cast itself works.
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text) COLLATE "C" < 'zzz';
+ ?column?
+----------
+ t
+(1 row)
+
+-- Conflicting explicit input collations are rejected, just like a plain
+-- function call text_larger('a' COLLATE "C", 'b' COLLATE "POSIX").
+SELECT CAST('a'::text COLLATE "C" AS text FORMAT 'b'::text COLLATE "POSIX");
+ERROR: collation mismatch between explicit collations "C" and "POSIX"
+LINE 1: ...ST('a'::text COLLATE "C" AS text FORMAT 'b'::text COLLATE "P...
+ ^
+DROP FORMATTER FOR CAST (text AS text);
+-- ====================================================================
+-- ruleutils: arg and FORMAT subexpressions deparse unambiguously inside
+-- CAST(...), needing no extra parentheses, and re-parse to the same thing.
+-- ====================================================================
+CREATE FUNCTION i2t_paren(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_paren(integer, text);
+CREATE VIEW formatter_paren_view AS
+ SELECT CAST((1 + 2) AS text FORMAT ('a' || 'b')) AS x;
+SELECT pg_get_viewdef('formatter_paren_view'::regclass, true);
+ pg_get_viewdef
+-----------------------------------------------------------------
+ SELECT CAST(1 + 2 AS text FORMAT 'a'::text || 'b'::text) AS x;
+(1 row)
+
+SELECT * FROM formatter_paren_view;
+ x
+-----
+ 3ab
+(1 row)
+
+DROP VIEW formatter_paren_view;
+DROP FORMATTER FOR CAST (integer AS text);
+DROP FUNCTION i2t_paren(integer, text);
+-- ====================================================================
+-- Dependency completeness: a view over a formatted cast depends (through the
+-- pg_formatter row) on the formatter function, so dropping the function with
+-- CASCADE removes both the formatter row and the view in one step.
+-- ====================================================================
+CREATE FUNCTION i2t_chain(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_chain(integer, text);
+CREATE VIEW formatter_chain_view AS SELECT CAST(7 AS text FORMAT 'q') AS x;
+DROP FUNCTION i2t_chain(integer, text); -- fails: formatter + view depend
+ERROR: cannot drop function i2t_chain(integer,text) because other objects depend on it
+DETAIL: formatter for cast from integer to text depends on function i2t_chain(integer,text)
+view formatter_chain_view depends on function i2t_chain(integer,text)
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP FUNCTION i2t_chain(integer, text) CASCADE; -- drops formatter and view
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to formatter for cast from integer to text
+drop cascades to view formatter_chain_view
+SELECT count(*) FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_class WHERE relname = 'formatter_chain_view';
+ count
+-------
+ 0
+(1 row)
+
diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sql
index fb05990ed2d..46575bc410d 100644
--- a/src/test/regress/sql/expressions.sql
+++ b/src/test/regress/sql/expressions.sql
@@ -305,14 +305,18 @@ rollback;
--
-- CAST(expr AS type FORMAT format_expr)
--
--- The FORMAT clause is parsed and stored, but formatter resolution is not
--- implemented yet, so parse analysis must reject it (not ignore it).
-
--- basic form
+-- A FORMAT clause is resolved through a registered formatter (see CREATE
+-- FORMATTER) and never falls back to an ordinary cast. No formatters are
+-- defined in this test, so these casts fail with a missing-formatter error;
+-- this confirms the FORMAT clause is neither ignored nor treated as an
+-- ordinary cast. An unknown-type source literal is coerced to text first,
+-- so the lookup key is (text, target).
+
+-- basic form (looks up a formatter for (text, date))
SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
-- the format may be a general expression, not just a string literal
SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
--- a no-op-looking cast must still be rejected, not relabeled away
+-- a no-op-looking cast must not be relabeled away; it needs a (text, text) formatter
SELECT CAST('abc'::text AS text FORMAT 'whatever');
diff --git a/src/test/regress/sql/formatters.sql b/src/test/regress/sql/formatters.sql
index 8050aee6f45..2c9b4351652 100644
--- a/src/test/regress/sql/formatters.sql
+++ b/src/test/regress/sql/formatters.sql
@@ -1,9 +1,10 @@
--
-- FORMATTERS
--
--- CREATE/DROP FORMATTER registers formatter metadata in pg_formatter,
--- keyed by a (source type, target type) pair. This is catalog and DDL
--- infrastructure only; it does not transform or execute formatted casts.
+-- A formatter registers a function for a (source type, target type) pair; a
+-- CAST(... AS target FORMAT format_expr) is resolved through pg_formatter and
+-- calls that function. This test covers both the CREATE/DROP FORMATTER
+-- catalog DDL and the execution of formatted casts.
-- A simple formatter function with the required signature
-- formatter(source_type, text) returns target_type
@@ -59,10 +60,74 @@ CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
CREATE FORMATTER FOR CAST (anyelement AS text)
WITH FUNCTION fmt_anyel(anyelement, text);
--- Registering a formatter does not enable a formatted cast: the FORMAT
--- clause must not be silently ignored or rewritten to a built-in function,
--- so CAST(... FORMAT ...) is rejected during parse analysis.
-SELECT CAST(5 AS text FORMAT 'YYYY');
+-- ====================================================================
+-- Execution: a formatted cast resolves through pg_formatter and calls the
+-- registered formatter function.
+-- ====================================================================
+
+-- basic execution, using the (integer, text) formatter created above
+SELECT CAST(5 AS text FORMAT 'abc');
+-- the FORMAT expression may be any expression, coerced to text
+SELECT CAST(5 AS text FORMAT 'a' || 'b');
+SELECT CAST(5 AS text FORMAT 123);
+
+-- The FORMAT expression is parse-analyzed independently of (and before) the
+-- formatter lookup, so an invalid FORMAT expression reports a normal error.
+SELECT CAST(5 AS text FORMAT no_such_column);
+
+-- Like an ordinary cast that uses a cast function, a formatted cast checks
+-- EXECUTE on the formatter function at use time.
+REVOKE EXECUTE ON FUNCTION int4_to_text_fmt(integer, text) FROM PUBLIC;
+CREATE ROLE regress_formatter_noexec NOLOGIN;
+SET ROLE regress_formatter_noexec;
+SELECT CAST(5 AS text FORMAT 'p'); -- fails: no EXECUTE on formatter function
+RESET ROLE;
+DROP ROLE regress_formatter_noexec;
+GRANT EXECUTE ON FUNCTION int4_to_text_fmt(integer, text) TO PUBLIC;
+
+-- A FORMAT clause never falls back to an ordinary cast: text -> text is a
+-- trivial ordinary cast, but a formatted cast still requires a formatter.
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
+-- An unknown-type source literal is coerced to text first, so this also looks
+-- up (text, text), not (unknown, text).
+SELECT CAST('abc' AS text FORMAT 'fmt');
+-- A missing formatter is an error even where an ordinary cast would be valid.
+SELECT CAST(5 AS integer FORMAT 'x');
+
+-- Define the (text, text) formatter and re-run the text-source cases.
+CREATE FUNCTION text_to_text_fmt(text, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1 || '/' || $2;
+CREATE FORMATTER FOR CAST (text AS text)
+ WITH FUNCTION text_to_text_fmt(text, text);
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
+SELECT CAST('abc' AS text FORMAT 'fmt');
+
+-- A type modifier on the target is enforced through the ordinary coercion path.
+CREATE FUNCTION int4_to_vc_fmt(integer, text) RETURNS varchar
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS varchar)
+ WITH FUNCTION int4_to_vc_fmt(integer, text);
+SELECT CAST(5 AS varchar FORMAT 'XXXX');
+SELECT CAST(5 AS varchar(3) FORMAT 'XXXX'); -- length 3 enforced
+
+-- Domain target: the formatter must return the domain type, so the domain's
+-- constraints are enforced by the function's result.
+CREATE DOMAIN nonempty_text AS text CHECK (VALUE <> '');
+CREATE FUNCTION text_to_netext_fmt(text, text) RETURNS nonempty_text
+ LANGUAGE sql IMMUTABLE RETURN $2::nonempty_text;
+CREATE FORMATTER FOR CAST (text AS nonempty_text)
+ WITH FUNCTION text_to_netext_fmt(text, text);
+SELECT CAST('z'::text AS nonempty_text FORMAT 'ok');
+SELECT CAST('z'::text AS nonempty_text FORMAT ''); -- domain check violation
+
+-- Drop the objects created in this execution section.
+DROP FORMATTER FOR CAST (text AS text);
+DROP FUNCTION text_to_text_fmt(text, text);
+DROP FORMATTER FOR CAST (integer AS varchar);
+DROP FUNCTION int4_to_vc_fmt(integer, text);
+DROP FORMATTER FOR CAST (text AS nonempty_text);
+DROP FUNCTION text_to_netext_fmt(text, text);
+DROP DOMAIN nonempty_text;
-- Dependency behavior: the formatter depends on its function.
DROP FUNCTION int4_to_text_fmt(integer, text); -- fails (RESTRICT)
@@ -97,3 +162,77 @@ DROP FUNCTION fmt_bad_arg1(bigint, text);
DROP FUNCTION fmt_bad_ret(integer, text);
DROP FUNCTION fmt_bad_set(integer, text);
DROP FUNCTION fmt_anyel(anyelement, text);
+
+-- ====================================================================
+-- CoerceViaFormatter: deparse fidelity and dependency on the formatter row
+-- ====================================================================
+CREATE FUNCTION i2t_view(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_view(integer, text);
+
+-- A view over a formatted cast deparses back to CAST(... FORMAT ...).
+CREATE VIEW formatter_view AS SELECT CAST(5 AS text FORMAT 'abc') AS x;
+SELECT pg_get_viewdef('formatter_view'::regclass, true);
+SELECT * FROM formatter_view;
+
+-- The view depends on the pg_formatter row, so DROP FORMATTER RESTRICT fails.
+DROP FORMATTER FOR CAST (integer AS text); -- fails (RESTRICT, view depends)
+-- ... and CASCADE drops the dependent view.
+DROP FORMATTER FOR CAST (integer AS text) CASCADE;
+SELECT count(*) FROM pg_class WHERE relname = 'formatter_view';
+DROP FUNCTION i2t_view(integer, text);
+
+-- ====================================================================
+-- Collation: a formatted cast behaves like calling the formatter function
+-- formatterfunc(arg, format). Its result collation is the result type's
+-- collation, and the input collation is derived from the arg and FORMAT
+-- expressions exactly as for an ordinary two-argument function call.
+-- ====================================================================
+-- text_larger(text, text) returns text and is collation-sensitive at the C
+-- level (it reads PG_GET_COLLATION), so it exercises the input collation.
+CREATE FORMATTER FOR CAST (text AS text)
+ WITH FUNCTION pg_catalog.text_larger(text, text);
+-- The default collation flows into the formatter call; if the input collation
+-- were left unset this would fail with "could not determine which collation".
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text);
+-- An explicit COLLATE on an operand is honored as the input collation.
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text COLLATE "C");
+-- The result is collatable, so an explicit COLLATE on the cast itself works.
+SELECT CAST('apple'::text AS text FORMAT 'banana'::text) COLLATE "C" < 'zzz';
+-- Conflicting explicit input collations are rejected, just like a plain
+-- function call text_larger('a' COLLATE "C", 'b' COLLATE "POSIX").
+SELECT CAST('a'::text COLLATE "C" AS text FORMAT 'b'::text COLLATE "POSIX");
+DROP FORMATTER FOR CAST (text AS text);
+
+-- ====================================================================
+-- ruleutils: arg and FORMAT subexpressions deparse unambiguously inside
+-- CAST(...), needing no extra parentheses, and re-parse to the same thing.
+-- ====================================================================
+CREATE FUNCTION i2t_paren(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_paren(integer, text);
+CREATE VIEW formatter_paren_view AS
+ SELECT CAST((1 + 2) AS text FORMAT ('a' || 'b')) AS x;
+SELECT pg_get_viewdef('formatter_paren_view'::regclass, true);
+SELECT * FROM formatter_paren_view;
+DROP VIEW formatter_paren_view;
+DROP FORMATTER FOR CAST (integer AS text);
+DROP FUNCTION i2t_paren(integer, text);
+
+-- ====================================================================
+-- Dependency completeness: a view over a formatted cast depends (through the
+-- pg_formatter row) on the formatter function, so dropping the function with
+-- CASCADE removes both the formatter row and the view in one step.
+-- ====================================================================
+CREATE FUNCTION i2t_chain(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION i2t_chain(integer, text);
+CREATE VIEW formatter_chain_view AS SELECT CAST(7 AS text FORMAT 'q') AS x;
+DROP FUNCTION i2t_chain(integer, text); -- fails: formatter + view depend
+DROP FUNCTION i2t_chain(integer, text) CASCADE; -- drops formatter and view
+SELECT count(*) FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+SELECT count(*) FROM pg_class WHERE relname = 'formatter_chain_view';
--
2.54.0
[application/octet-stream] 0001-Add-parser-support-for-CAST-FORMAT-syntax.patch (5.6K, ../CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com/4-0001-Add-parser-support-for-CAST-FORMAT-syntax.patch)
download | inline diff:
From 8bb73db3ae0fdec4c0b53d0c3b67a2c3340d91de Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Wed, 24 Jun 2026 12:24:03 -0700
Subject: [PATCH] Add parser support for CAST ... FORMAT
Add grammar and raw parse-tree support for
CAST(expr AS type FORMAT format_expr)
by storing the FORMAT expression in TypeCast. Ordinary casts leave the
new field unset.
Parse analysis still rejects formatted casts in this patch. Formatter
resolution and execution are added later, so this patch only establishes
the syntax and parse-node representation.
---
src/backend/nodes/nodeFuncs.c | 2 ++
src/backend/parser/gram.y | 7 +++++++
src/backend/parser/parse_expr.c | 13 +++++++++++++
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/expressions.out | 23 +++++++++++++++++++++++
src/test/regress/sql/expressions.sql | 15 +++++++++++++++
6 files changed, 61 insertions(+)
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 2a2e00b372e..66495546179 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4581,6 +4581,8 @@ raw_expression_tree_walker_impl(Node *node,
return true;
if (WALK(tc->typeName))
return true;
+ if (WALK(tc->format))
+ return true;
}
break;
case T_CollateClause:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..ef4881efc81 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -16787,6 +16787,13 @@ func_expr_common_subexpr:
}
| CAST '(' a_expr AS Typename ')'
{ $$ = makeTypeCast($3, $5, @1); }
+ | CAST '(' a_expr AS Typename FORMAT a_expr ')'
+ {
+ TypeCast *n = (TypeCast *) makeTypeCast($3, $5, @1);
+
+ n->format = $7;
+ $$ = (Node *) n;
+ }
| EXTRACT '(' extract_list ')'
{
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9adc9d4c0f6..1729ba56013 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2743,6 +2743,19 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
int32 targetTypmod;
int location;
+ /*
+ * Formatted casts (CAST(expr AS type FORMAT format_expr)) are parsed and
+ * represented in the parse tree, but formatter resolution is not yet
+ * implemented. Reject such casts here rather than silently ignoring the
+ * FORMAT clause.
+ */
+ if (tc->format != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("formatted casts are not implemented yet"),
+ errdetail("No formatter resolution mechanism is available."),
+ parser_errposition(pstate, exprLocation(tc->format))));
+
/* Look up the type name first */
typenameTypeIdAndMod(pstate, tc->typeName, &targetType, &targetTypmod);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4133c404a6b..759c6bfae54 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -402,6 +402,7 @@ typedef struct TypeCast
NodeTag type;
Node *arg; /* the expression being casted */
TypeName *typeName; /* the target type */
+ Node *format; /* FORMAT expression, or NULL if none */
ParseLoc location; /* token location, or -1 if unknown */
} TypeCast;
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out
index 730f7bc7eba..b2d71dca4fa 100644
--- a/src/test/regress/expected/expressions.out
+++ b/src/test/regress/expected/expressions.out
@@ -561,3 +561,26 @@ from inttest;
(3 rows)
rollback;
+--
+-- CAST(expr AS type FORMAT format_expr)
+--
+-- The FORMAT clause is parsed and stored, but formatter resolution is not
+-- implemented yet, so parse analysis must reject it (not ignore it).
+-- basic form
+SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
+ERROR: formatted casts are not implemented yet
+LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
+ ^
+DETAIL: No formatter resolution mechanism is available.
+-- the format may be a general expression, not just a string literal
+SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
+ERROR: formatted casts are not implemented yet
+LINE 1: SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
+ ^
+DETAIL: No formatter resolution mechanism is available.
+-- a no-op-looking cast must still be rejected, not relabeled away
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
+ERROR: formatted casts are not implemented yet
+LINE 1: SELECT CAST('abc'::text AS text FORMAT 'whatever');
+ ^
+DETAIL: No formatter resolution mechanism is available.
diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sql
index 3b3048f9731..fb05990ed2d 100644
--- a/src/test/regress/sql/expressions.sql
+++ b/src/test/regress/sql/expressions.sql
@@ -301,3 +301,18 @@ select
from inttest;
rollback;
+
+--
+-- CAST(expr AS type FORMAT format_expr)
+--
+-- The FORMAT clause is parsed and stored, but formatter resolution is not
+-- implemented yet, so parse analysis must reject it (not ignore it).
+
+-- basic form
+SELECT CAST('2026-06-24' AS date FORMAT 'YYYY-MM-DD');
+
+-- the format may be a general expression, not just a string literal
+SELECT CAST('2026-06-24' AS date FORMAT 'YYYY' || '-MM-DD');
+
+-- a no-op-looking cast must still be rejected, not relabeled away
+SELECT CAST('abc'::text AS text FORMAT 'whatever');
--
2.54.0
[application/octet-stream] 0002-Add-pg_formatter-catalog-and-CREATE-DROP-FORMATTER.patch (69.4K, ../CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com/5-0002-Add-pg_formatter-catalog-and-CREATE-DROP-FORMATTER.patch)
download | inline diff:
From 70d36e8fd1387233cc01f5aa130bdc8f8d3ccd13 Mon Sep 17 00:00:00 2001
From: Haibo Yan <[email protected]>
Date: Wed, 24 Jun 2026 14:52:46 -0700
Subject: [PATCH] Add pg_formatter and CREATE/DROP FORMATTER
Add pg_formatter, a catalog for registering formatted conversions by
source and target type. A formatter function has the signature
formatter(source_type, text) returns target_type
where the second argument receives the FORMAT expression coerced to text.
Add CREATE FORMATTER and DROP FORMATTER, syscache support, object-address
and dependency handling, and pg_dump support. Formatter objects are
separate from pg_cast because formatted casts are always explicit,
function-backed, and keyed by a source/target type pair rather than by
ordinary cast semantics such as implicit, assignment, binary, or in/out
casts.
This patch only adds catalog and DDL infrastructure. CAST ... FORMAT
execution is added separately.
---
doc/src/sgml/catalogs.sgml | 83 ++++++++
doc/src/sgml/ref/allfiles.sgml | 2 +
doc/src/sgml/ref/create_formatter.sgml | 131 ++++++++++++
doc/src/sgml/ref/drop_formatter.sgml | 126 ++++++++++++
doc/src/sgml/reference.sgml | 2 +
src/backend/catalog/aclchk.c | 2 +
src/backend/catalog/dependency.c | 2 +
src/backend/catalog/objectaddress.c | 105 ++++++++++
src/backend/commands/Makefile | 1 +
src/backend/commands/dropcmds.c | 22 ++
src/backend/commands/event_trigger.c | 2 +
src/backend/commands/formattercmds.c | 245 +++++++++++++++++++++++
src/backend/commands/meson.build | 1 +
src/backend/commands/seclabel.c | 1 +
src/backend/parser/gram.y | 40 +++-
src/backend/tcop/utility.c | 17 ++
src/bin/pg_dump/common.c | 3 +
src/bin/pg_dump/pg_dump.c | 166 +++++++++++++++
src/bin/pg_dump/pg_dump.h | 10 +
src/bin/pg_dump/pg_dump_sort.c | 9 +
src/include/catalog/Makefile | 1 +
src/include/catalog/catversion.h | 2 +-
src/include/catalog/meson.build | 1 +
src/include/catalog/pg_formatter.h | 62 ++++++
src/include/commands/formatter.h | 24 +++
src/include/nodes/parsenodes.h | 17 ++
src/include/parser/kwlist.h | 1 +
src/include/tcop/cmdtaglist.h | 2 +
src/test/regress/expected/formatters.out | 116 +++++++++++
src/test/regress/expected/oidjoins.out | 3 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/formatters.sql | 99 +++++++++
src/tools/pgindent/typedefs.list | 4 +
33 files changed, 1300 insertions(+), 4 deletions(-)
create mode 100644 doc/src/sgml/ref/create_formatter.sgml
create mode 100644 doc/src/sgml/ref/drop_formatter.sgml
create mode 100644 src/backend/commands/formattercmds.c
create mode 100644 src/include/catalog/pg_formatter.h
create mode 100644 src/include/commands/formatter.h
create mode 100644 src/test/regress/expected/formatters.out
create mode 100644 src/test/regress/sql/formatters.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4b474c13917..75f4e4840fd 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -170,6 +170,11 @@
<entry>additional foreign table information</entry>
</row>
+ <row>
+ <entry><link linkend="catalog-pg-formatter"><structname>pg_formatter</structname></link></entry>
+ <entry>formatter functions for formatted casts</entry>
+ </row>
+
<row>
<entry><link linkend="catalog-pg-index"><structname>pg_index</structname></link></entry>
<entry>additional index information</entry>
@@ -4391,6 +4396,84 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</sect1>
+ <sect1 id="catalog-pg-formatter">
+ <title><structname>pg_formatter</structname></title>
+
+ <indexterm zone="catalog-pg-formatter">
+ <primary>pg_formatter</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_formatter</structname> stores formatting
+ conversion functions for formatted casts. A formatter is identified by a
+ source type and a target type. The associated function is called with the
+ source value and the <literal>FORMAT</literal> expression coerced to
+ <type>text</type>, and returns the target type. At most one formatter
+ exists for any given pair of source and target types. See <xref
+ linkend="sql-createformatter"/> for more information.
+ </para>
+
+ <table>
+ <title><structname>pg_formatter</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>oid</structfield> <type>oid</type>
+ </para>
+ <para>
+ Row identifier
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>fmtsource</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the source data type of the formatted cast
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>fmttarget</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the target data type of the formatted cast
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>fmtfunc</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ OID of the formatter function, which has the signature
+ <replaceable>function</replaceable>(<replaceable>source_type</replaceable>, <type>text</type>)
+ returning <replaceable>target_type</replaceable>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
+
<sect1 id="catalog-pg-index">
<title><structname>pg_index</structname></title>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index e1a56c36221..b0bda54d87b 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -70,6 +70,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createExtension SYSTEM "create_extension.sgml">
<!ENTITY createForeignDataWrapper SYSTEM "create_foreign_data_wrapper.sgml">
<!ENTITY createForeignTable SYSTEM "create_foreign_table.sgml">
+<!ENTITY createFormatter SYSTEM "create_formatter.sgml">
<!ENTITY createFunction SYSTEM "create_function.sgml">
<!ENTITY createGroup SYSTEM "create_group.sgml">
<!ENTITY createIndex SYSTEM "create_index.sgml">
@@ -118,6 +119,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropExtension SYSTEM "drop_extension.sgml">
<!ENTITY dropForeignDataWrapper SYSTEM "drop_foreign_data_wrapper.sgml">
<!ENTITY dropForeignTable SYSTEM "drop_foreign_table.sgml">
+<!ENTITY dropFormatter SYSTEM "drop_formatter.sgml">
<!ENTITY dropFunction SYSTEM "drop_function.sgml">
<!ENTITY dropGroup SYSTEM "drop_group.sgml">
<!ENTITY dropIndex SYSTEM "drop_index.sgml">
diff --git a/doc/src/sgml/ref/create_formatter.sgml b/doc/src/sgml/ref/create_formatter.sgml
new file mode 100644
index 00000000000..268b53e7d2c
--- /dev/null
+++ b/doc/src/sgml/ref/create_formatter.sgml
@@ -0,0 +1,131 @@
+<!--
+doc/src/sgml/ref/create_formatter.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createformatter">
+ <indexterm zone="sql-createformatter">
+ <primary>CREATE FORMATTER</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE FORMATTER</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE FORMATTER</refname>
+ <refpurpose>define a new formatter for a formatted cast</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE FORMATTER FOR CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>)
+ WITH FUNCTION <replaceable>function_name</replaceable> [ (<replaceable>argument_type</replaceable> [, ...]) ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1 id="sql-createformatter-description">
+ <title>Description</title>
+
+ <para>
+ <command>CREATE FORMATTER</command> defines a formatting conversion for
+ <literal>CAST(<replaceable>expr</replaceable> AS <replaceable>target_type</replaceable> FORMAT <replaceable>format_expr</replaceable>)</literal>.
+ A formatter is selected by the source type and target type of the cast.
+ Unlike an ordinary cast (see <xref linkend="sql-createcast"/>), a formatted
+ cast is always explicit and always uses a function, and the function
+ additionally receives the <literal>FORMAT</literal> expression.
+ </para>
+
+ <para>
+ The formatter function must take exactly two arguments and return the
+ target type:
+<synopsis>
+<replaceable>function_name</replaceable>(<replaceable>source_type</replaceable>, text) RETURNS <replaceable>target_type</replaceable>
+</synopsis>
+ The first argument is the value being formatted, and the second argument
+ receives the <literal>FORMAT</literal> expression coerced to
+ <type>text</type>. Only one formatter may be registered for a given
+ <literal>(<replaceable>source_type</replaceable>,
+ <replaceable>target_type</replaceable>)</literal> pair. Neither the source
+ type nor the target type may be a pseudo-type.
+ </para>
+
+ <para>
+ To create a formatter, you must be the owner of the source type or the
+ target type, and you must have <literal>EXECUTE</literal> privilege on the
+ formatter function.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable>source_type</replaceable></term>
+ <listitem>
+ <para>
+ The data type of the value passed to the formatter (the source of the
+ cast).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>target_type</replaceable></term>
+ <listitem>
+ <para>
+ The data type returned by the formatter (the target of the cast).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>function_name</replaceable>[(<replaceable>argument_type</replaceable> [, ...])]</term>
+ <listitem>
+ <para>
+ The function used to perform the formatted cast. The argument list, if
+ given, must name the two argument types
+ (<replaceable>source_type</replaceable> and <type>text</type>).
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1 id="sql-createformatter-examples">
+ <title>Examples</title>
+
+ <para>
+ Register a formatter that converts an <type>integer</type> to
+ <type>text</type> using the supplied format string:
+<programlisting>
+CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+</programlisting></para>
+ </refsect1>
+
+ <refsect1 id="sql-createformatter-compat">
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE FORMATTER</command> is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-dropformatter"/></member>
+ <member><xref linkend="sql-createcast"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_formatter.sgml b/doc/src/sgml/ref/drop_formatter.sgml
new file mode 100644
index 00000000000..b7f367a590e
--- /dev/null
+++ b/doc/src/sgml/ref/drop_formatter.sgml
@@ -0,0 +1,126 @@
+<!--
+doc/src/sgml/ref/drop_formatter.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropformatter">
+ <indexterm zone="sql-dropformatter">
+ <primary>DROP FORMATTER</primary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP FORMATTER</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP FORMATTER</refname>
+ <refpurpose>remove a formatter</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP FORMATTER [ IF EXISTS ] FOR CAST (<replaceable>source_type</replaceable> AS <replaceable>target_type</replaceable>) [ CASCADE | RESTRICT ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1 id="sql-dropformatter-description">
+ <title>Description</title>
+
+ <para>
+ <command>DROP FORMATTER</command> removes a previously defined formatter for
+ the given pair of source and target types.
+ </para>
+
+ <para>
+ Dropping a formatter requires ownership of either the source type or the
+ target type.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the formatter does not exist. A notice is
+ issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>source_type</replaceable></term>
+ <listitem>
+ <para>
+ The source data type of the formatter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable>target_type</replaceable></term>
+ <listitem>
+ <para>
+ The target data type of the formatter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the formatter,
+ and in turn all objects that depend on those objects
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the formatter if any objects depend on it. This is the
+ default.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1 id="sql-dropformatter-examples">
+ <title>Examples</title>
+
+ <para>
+ To drop the formatter for the cast from <type>integer</type> to
+ <type>text</type>:
+<programlisting>
+DROP FORMATTER FOR CAST (integer AS text);
+</programlisting></para>
+ </refsect1>
+
+ <refsect1 id="sql-dropformatter-compat">
+ <title>Compatibility</title>
+
+ <para>
+ <command>DROP FORMATTER</command> is a
+ <productname>PostgreSQL</productname> extension. See <xref
+ linkend="sql-createformatter"/> for details.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createformatter"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 674ac17e82c..a1d055509c8 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -98,6 +98,7 @@
&createExtension;
&createForeignDataWrapper;
&createForeignTable;
+ &createFormatter;
&createFunction;
&createGroup;
&createIndex;
@@ -146,6 +147,7 @@
&dropExtension;
&dropForeignDataWrapper;
&dropForeignTable;
+ &dropFormatter;
&dropFunction;
&dropGroup;
&dropIndex;
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 007ede997c5..0f0211dd6c3 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -2794,6 +2794,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_AMPROC:
case OBJECT_ATTRIBUTE:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_DEFAULT:
case OBJECT_DEFACL:
case OBJECT_DOMCONSTRAINT:
@@ -2937,6 +2938,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
case OBJECT_AMPROC:
case OBJECT_ATTRIBUTE:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_DEFAULT:
case OBJECT_DEFACL:
case OBJECT_DOMCONSTRAINT:
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index c54774b3275..7eeac91264f 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -41,6 +41,7 @@
#include "catalog/pg_extension.h"
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
+#include "catalog/pg_formatter.h"
#include "catalog/pg_init_privs.h"
#include "catalog/pg_language.h"
#include "catalog/pg_largeobject.h"
@@ -1534,6 +1535,7 @@ doDeletion(const ObjectAddress *object, int flags)
case DefaultAclRelationId:
case EventTriggerRelationId:
case TransformRelationId:
+ case FormatterRelationId:
case AuthMemRelationId:
DropObjectById(object);
break;
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index af0e4703616..fcf38db8c55 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -37,6 +37,7 @@
#include "catalog/pg_extension.h"
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
+#include "catalog/pg_formatter.h"
#include "catalog/pg_language.h"
#include "catalog/pg_largeobject.h"
#include "catalog/pg_largeobject_metadata.h"
@@ -70,6 +71,7 @@
#include "commands/defrem.h"
#include "commands/event_trigger.h"
#include "commands/extension.h"
+#include "commands/formatter.h"
#include "commands/policy.h"
#include "commands/proclang.h"
#include "commands/tablespace.h"
@@ -179,6 +181,20 @@ static const ObjectPropertyType ObjectProperty[] =
OBJECT_CAST,
false
},
+ {
+ "formatter",
+ FormatterRelationId,
+ FormatterOidIndexId,
+ FORMATTEROID,
+ SYSCACHEID_INVALID,
+ Anum_pg_formatter_oid,
+ InvalidAttrNumber,
+ InvalidAttrNumber,
+ InvalidAttrNumber,
+ InvalidAttrNumber,
+ OBJECT_FORMATTER,
+ false
+ },
{
"collation",
CollationRelationId,
@@ -796,6 +812,9 @@ static const struct object_type_map
{
"cast", OBJECT_CAST
},
+ {
+ "formatter", OBJECT_FORMATTER
+ },
{
"collation", OBJECT_COLLATION
},
@@ -1165,6 +1184,21 @@ get_object_address(ObjectType objtype, Node *object,
address.objectSubId = 0;
}
break;
+ case OBJECT_FORMATTER:
+ {
+ TypeName *sourcetype = linitial_node(TypeName, castNode(List, object));
+ TypeName *targettype = lsecond_node(TypeName, castNode(List, object));
+ Oid sourcetypeid;
+ Oid targettypeid;
+
+ sourcetypeid = LookupTypeNameOid(NULL, sourcetype, missing_ok);
+ targettypeid = LookupTypeNameOid(NULL, targettype, missing_ok);
+ address.classId = FormatterRelationId;
+ address.objectId =
+ get_formatter_oid(sourcetypeid, targettypeid, missing_ok);
+ address.objectSubId = 0;
+ }
+ break;
case OBJECT_TRANSFORM:
{
TypeName *typename = linitial_node(TypeName, castNode(List, object));
@@ -2239,6 +2273,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
* exceptions.
*/
if (type == OBJECT_TYPE || type == OBJECT_DOMAIN || type == OBJECT_CAST ||
+ type == OBJECT_FORMATTER ||
type == OBJECT_TRANSFORM || type == OBJECT_DOMCONSTRAINT)
{
Datum *elems;
@@ -2291,6 +2326,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
type == OBJECT_ROUTINE ||
type == OBJECT_OPERATOR ||
type == OBJECT_CAST ||
+ type == OBJECT_FORMATTER ||
type == OBJECT_AMOP ||
type == OBJECT_AMPROC)
{
@@ -2336,6 +2372,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
pg_fallthrough;
case OBJECT_DOMCONSTRAINT:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_PUBLICATION_REL:
case OBJECT_DEFACL:
case OBJECT_TRANSFORM:
@@ -2424,6 +2461,7 @@ pg_get_object_address(PG_FUNCTION_ARGS)
objnode = (Node *) typename;
break;
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_DOMCONSTRAINT:
case OBJECT_TRANSFORM:
objnode = (Node *) list_make2(typename, linitial(args));
@@ -2583,6 +2621,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
address.objectId)));
break;
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
{
/* We can only check permissions on the source/target types */
TypeName *sourcetype = linitial_node(TypeName, castNode(List, object));
@@ -3111,6 +3150,31 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
break;
}
+ case FormatterRelationId:
+ {
+ HeapTuple fmtTup;
+ Form_pg_formatter fmtForm;
+
+ fmtTup = SearchSysCache1(FORMATTEROID,
+ ObjectIdGetDatum(object->objectId));
+ if (!HeapTupleIsValid(fmtTup))
+ {
+ if (!missing_ok)
+ elog(ERROR, "could not find tuple for formatter %u",
+ object->objectId);
+ break;
+ }
+
+ fmtForm = (Form_pg_formatter) GETSTRUCT(fmtTup);
+
+ appendStringInfo(&buffer, _("formatter for cast from %s to %s"),
+ format_type_be(fmtForm->fmtsource),
+ format_type_be(fmtForm->fmttarget));
+
+ ReleaseSysCache(fmtTup);
+ break;
+ }
+
case CollationRelationId:
{
HeapTuple collTup;
@@ -4762,6 +4826,10 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok)
appendStringInfoString(&buffer, "cast");
break;
+ case FormatterRelationId:
+ appendStringInfoString(&buffer, "formatter");
+ break;
+
case CollationRelationId:
appendStringInfoString(&buffer, "collation");
break;
@@ -5225,6 +5293,43 @@ getObjectIdentityParts(const ObjectAddress *object,
break;
}
+ case FormatterRelationId:
+ {
+ Relation fmtRel;
+ HeapTuple tup;
+ Form_pg_formatter fmtForm;
+
+ fmtRel = table_open(FormatterRelationId, AccessShareLock);
+
+ tup = get_catalog_object_by_oid(fmtRel, Anum_pg_formatter_oid,
+ object->objectId);
+
+ if (!HeapTupleIsValid(tup))
+ {
+ if (!missing_ok)
+ elog(ERROR, "could not find tuple for formatter %u",
+ object->objectId);
+
+ table_close(fmtRel, AccessShareLock);
+ break;
+ }
+
+ fmtForm = (Form_pg_formatter) GETSTRUCT(tup);
+
+ appendStringInfo(&buffer, "for cast (%s AS %s)",
+ format_type_be_qualified(fmtForm->fmtsource),
+ format_type_be_qualified(fmtForm->fmttarget));
+
+ if (objname)
+ {
+ *objname = list_make1(format_type_be_qualified(fmtForm->fmtsource));
+ *objargs = list_make1(format_type_be_qualified(fmtForm->fmttarget));
+ }
+
+ table_close(fmtRel, AccessShareLock);
+ break;
+ }
+
case CollationRelationId:
{
HeapTuple collTup;
diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile
index 5b9d084977e..7a4066867e0 100644
--- a/src/backend/commands/Makefile
+++ b/src/backend/commands/Makefile
@@ -38,6 +38,7 @@ OBJS = \
explain_state.o \
extension.o \
foreigncmds.o \
+ formattercmds.o \
functioncmds.o \
indexcmds.o \
lockcmds.o \
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index 88a2df65c69..90e250f85ba 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -25,6 +25,7 @@
#include "miscadmin.h"
#include "parser/parse_type.h"
#include "utils/acl.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
@@ -401,6 +402,27 @@ does_not_exist_skipping(ObjectType objtype, Node *object)
}
}
break;
+ case OBJECT_FORMATTER:
+ {
+ if (!type_in_list_does_not_exist_skipping(list_make1(linitial(castNode(List, object))), &msg, &name) &&
+ !type_in_list_does_not_exist_skipping(list_make1(lsecond(castNode(List, object))), &msg, &name))
+ {
+ /*
+ * Both types exist (else the checks above would have
+ * produced a message), so resolve them to OIDs and report
+ * them with format_type_be(). This keeps the wording
+ * consistent with the duplicate-object and undefined-object
+ * errors, which also use format_type_be().
+ */
+ Oid sourcetypeid = typenameTypeId(NULL, linitial_node(TypeName, castNode(List, object)));
+ Oid targettypeid = typenameTypeId(NULL, lsecond_node(TypeName, castNode(List, object)));
+
+ msg = gettext_noop("formatter for cast from type %s to type %s does not exist, skipping");
+ name = format_type_be(sourcetypeid);
+ args = format_type_be(targettypeid);
+ }
+ }
+ break;
case OBJECT_TRANSFORM:
if (!type_in_list_does_not_exist_skipping(list_make1(linitial(castNode(List, object))), &msg, &name))
{
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index adc6eabc0f4..139bdf51c61 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -2301,6 +2301,7 @@ stringify_grant_objtype(ObjectType objtype)
case OBJECT_AMPROC:
case OBJECT_ATTRIBUTE:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_COLLATION:
case OBJECT_CONVERSION:
case OBJECT_DEFAULT:
@@ -2385,6 +2386,7 @@ stringify_adefprivs_objtype(ObjectType objtype)
case OBJECT_AMPROC:
case OBJECT_ATTRIBUTE:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_COLLATION:
case OBJECT_CONVERSION:
case OBJECT_DEFAULT:
diff --git a/src/backend/commands/formattercmds.c b/src/backend/commands/formattercmds.c
new file mode 100644
index 00000000000..2276519f562
--- /dev/null
+++ b/src/backend/commands/formattercmds.c
@@ -0,0 +1,245 @@
+/*-------------------------------------------------------------------------
+ *
+ * formattercmds.c
+ * Routines for SQL commands that manipulate formatters.
+ *
+ * A formatter associates a function with a (source type, target type) pair.
+ * The function implements CAST(expr AS target FORMAT format_expr): it
+ * receives the source value and the FORMAT expression (passed as text) and
+ * returns the target type. Formatters are registered in the pg_formatter
+ * catalog and are intentionally separate from pg_cast (see pg_formatter.h).
+ *
+ * This file provides the catalog registration machinery (CREATE/DROP
+ * FORMATTER). It does not perform expression transformation or execution of
+ * formatted casts.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/commands/formattercmds.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/htup_details.h"
+#include "access/table.h"
+#include "catalog/catalog.h"
+#include "catalog/dependency.h"
+#include "catalog/indexing.h"
+#include "catalog/objectaccess.h"
+#include "catalog/objectaddress.h"
+#include "catalog/pg_formatter.h"
+#include "catalog/pg_proc.h"
+#include "catalog/pg_type.h"
+#include "commands/formatter.h"
+#include "miscadmin.h"
+#include "parser/parse_func.h"
+#include "parser/parse_type.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+
+/*
+ * Validate the signature of a formatter function: it must be a normal
+ * function (not a procedure, aggregate or window function), must not return
+ * a set, and must have the signature
+ *
+ * formatter(source_type, text) returns target_type
+ *
+ * NB: the formatter signature is fixed at two arguments and does not include
+ * the target type modifier; typmod-aware formatter signatures are not
+ * supported.
+ */
+static void
+check_formatter_function(Form_pg_proc procstruct,
+ Oid sourcetypeid, Oid targettypeid)
+{
+ if (procstruct->prokind != PROKIND_FUNCTION)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("formatter function must be a normal function")));
+ if (procstruct->proretset)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("formatter function must not return a set")));
+ if (procstruct->pronargs != 2)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("formatter function must take exactly two arguments")));
+ if (procstruct->proargtypes.values[0] != sourcetypeid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("first argument of formatter function must be type %s",
+ format_type_be(sourcetypeid))));
+ if (procstruct->proargtypes.values[1] != TEXTOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("second argument of formatter function must be type %s",
+ "text")));
+ if (procstruct->prorettype != targettypeid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("return data type of formatter function must be type %s",
+ format_type_be(targettypeid))));
+}
+
+/*
+ * CREATE FORMATTER
+ */
+ObjectAddress
+CreateFormatter(CreateFormatterStmt *stmt)
+{
+ Oid sourcetypeid;
+ Oid targettypeid;
+ char sourcetyptype;
+ char targettyptype;
+ Oid funcid;
+ AclResult aclresult;
+ Form_pg_proc procstruct;
+ HeapTuple tuple;
+ HeapTuple newtuple;
+ Datum values[Natts_pg_formatter];
+ bool nulls[Natts_pg_formatter] = {0};
+ Oid formatterid;
+ Relation relation;
+ ObjectAddress myself,
+ referenced;
+ ObjectAddresses *addrs;
+
+ sourcetypeid = typenameTypeId(NULL, stmt->sourcetype);
+ targettypeid = typenameTypeId(NULL, stmt->targettype);
+ sourcetyptype = get_typtype(sourcetypeid);
+ targettyptype = get_typtype(targettypeid);
+
+ /* No pseudo-types allowed */
+ if (sourcetyptype == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("source data type %s is a pseudo-type",
+ TypeNameToString(stmt->sourcetype))));
+ if (targettyptype == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("target data type %s is a pseudo-type",
+ TypeNameToString(stmt->targettype))));
+
+ /*
+ * Permission check. As for CREATE CAST, the caller must own at least one
+ * of the two types involved; owning a type is what authorizes defining
+ * conversion behavior for it. In addition, and following CREATE OPERATOR
+ * and CREATE AGGREGATE, we require EXECUTE permission on the formatter
+ * function (this will also be checked when the formatter is used, but it
+ * is a good idea to verify it up front). We intentionally do not require
+ * ownership of the function, unlike CREATE TRANSFORM, because a formatter
+ * is a data conversion rather than a procedural-language binding.
+ */
+ if (!object_ownercheck(TypeRelationId, sourcetypeid, GetUserId())
+ && !object_ownercheck(TypeRelationId, targettypeid, GetUserId()))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be owner of type %s or type %s",
+ format_type_be(sourcetypeid),
+ format_type_be(targettypeid))));
+
+ /*
+ * Look up and validate the formatter function.
+ */
+ funcid = LookupFuncWithArgs(OBJECT_FUNCTION, stmt->func, false);
+
+ aclresult = object_aclcheck(ProcedureRelationId, funcid, GetUserId(), ACL_EXECUTE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FUNCTION,
+ NameListToString(stmt->func->objname));
+
+ tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ procstruct = (Form_pg_proc) GETSTRUCT(tuple);
+ check_formatter_function(procstruct, sourcetypeid, targettypeid);
+ ReleaseSysCache(tuple);
+
+ /*
+ * Check for a pre-existing formatter for this (source, target) pair. For
+ * this version only one formatter per pair is allowed.
+ */
+ relation = table_open(FormatterRelationId, RowExclusiveLock);
+
+ if (SearchSysCacheExists2(FORMATTERSOURCETARGET,
+ ObjectIdGetDatum(sourcetypeid),
+ ObjectIdGetDatum(targettypeid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("formatter for cast from type %s to type %s already exists",
+ format_type_be(sourcetypeid),
+ format_type_be(targettypeid))));
+
+ /*
+ * Build and insert the catalog tuple.
+ */
+ formatterid = GetNewOidWithIndex(relation, FormatterOidIndexId,
+ Anum_pg_formatter_oid);
+ values[Anum_pg_formatter_oid - 1] = ObjectIdGetDatum(formatterid);
+ values[Anum_pg_formatter_fmtsource - 1] = ObjectIdGetDatum(sourcetypeid);
+ values[Anum_pg_formatter_fmttarget - 1] = ObjectIdGetDatum(targettypeid);
+ values[Anum_pg_formatter_fmtfunc - 1] = ObjectIdGetDatum(funcid);
+
+ newtuple = heap_form_tuple(RelationGetDescr(relation), values, nulls);
+ CatalogTupleInsert(relation, newtuple);
+
+ addrs = new_object_addresses();
+
+ ObjectAddressSet(myself, FormatterRelationId, formatterid);
+
+ /* dependency on source type */
+ ObjectAddressSet(referenced, TypeRelationId, sourcetypeid);
+ add_exact_object_address(&referenced, addrs);
+
+ /* dependency on target type */
+ ObjectAddressSet(referenced, TypeRelationId, targettypeid);
+ add_exact_object_address(&referenced, addrs);
+
+ /* dependency on the formatter function */
+ ObjectAddressSet(referenced, ProcedureRelationId, funcid);
+ add_exact_object_address(&referenced, addrs);
+
+ record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
+ free_object_addresses(addrs);
+
+ /* dependency on extension */
+ recordDependencyOnCurrentExtension(&myself, false);
+
+ /* Post creation hook for new formatter */
+ InvokeObjectPostCreateHook(FormatterRelationId, formatterid, 0);
+
+ heap_freetuple(newtuple);
+
+ table_close(relation, RowExclusiveLock);
+
+ return myself;
+}
+
+/*
+ * get_formatter_oid - given source and target type OIDs, look up a
+ * formatter OID
+ */
+Oid
+get_formatter_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok)
+{
+ Oid oid;
+
+ oid = GetSysCacheOid2(FORMATTERSOURCETARGET, Anum_pg_formatter_oid,
+ ObjectIdGetDatum(sourcetypeid),
+ ObjectIdGetDatum(targettypeid));
+ if (!OidIsValid(oid) && !missing_ok)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("formatter for cast from type %s to type %s does not exist",
+ format_type_be(sourcetypeid),
+ format_type_be(targettypeid))));
+ return oid;
+}
diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build
index 9f258d566eb..8c4ecb83c3a 100644
--- a/src/backend/commands/meson.build
+++ b/src/backend/commands/meson.build
@@ -26,6 +26,7 @@ backend_sources += files(
'explain_state.c',
'extension.c',
'foreigncmds.c',
+ 'formattercmds.c',
'functioncmds.c',
'indexcmds.c',
'lockcmds.c',
diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c
index 77542d04200..f7d1779dad8 100644
--- a/src/backend/commands/seclabel.c
+++ b/src/backend/commands/seclabel.c
@@ -66,6 +66,7 @@ SecLabelSupportsObjectType(ObjectType objtype)
case OBJECT_AMPROC:
case OBJECT_ATTRIBUTE:
case OBJECT_CAST:
+ case OBJECT_FORMATTER:
case OBJECT_COLLATION:
case OBJECT_CONVERSION:
case OBJECT_DEFAULT:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ef4881efc81..6e82a4e090e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -295,13 +295,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt
CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
+ CreateFormatterStmt
CreatePropGraphStmt AlterPropGraphStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
DropOpClassStmt DropOpFamilyStmt DropStmt
DropCastStmt DropRoleStmt
DropdbStmt DropTableSpaceStmt
- DropTransformStmt
+ DropTransformStmt DropFormatterStmt
DropUserMappingStmt ExplainStmt FetchStmt
GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt
ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt
@@ -769,7 +770,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
EXPLAIN EXPRESSION EXTENSION EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR
- FORCE FOREIGN FORMAT FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS
+ FORCE FOREIGN FORMAT FORMATTER FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS
GENERATED GLOBAL GRANT GRANTED GRAPH GRAPH_TABLE GREATEST GROUP_P GROUPING GROUPS
@@ -1105,6 +1106,7 @@ stmt:
| CreateStatsStmt
| CreateTableSpaceStmt
| CreateTransformStmt
+ | CreateFormatterStmt
| CreateTrigStmt
| CreateEventTrigStmt
| CreateRoleStmt
@@ -1125,6 +1127,7 @@ stmt:
| DropSubscriptionStmt
| DropTableSpaceStmt
| DropTransformStmt
+ | DropFormatterStmt
| DropRoleStmt
| DropUserMappingStmt
| DropdbStmt
@@ -9893,6 +9896,38 @@ DropTransformStmt: DROP TRANSFORM opt_if_exists FOR Typename LANGUAGE name opt_d
;
+/*****************************************************************************
+ *
+ * CREATE FORMATTER / DROP FORMATTER
+ *
+ * A formatter registers the function implementing
+ * CAST(expr AS target FORMAT format_expr) for a (source, target) type pair.
+ *****************************************************************************/
+
+CreateFormatterStmt: CREATE FORMATTER FOR CAST '(' Typename AS Typename ')' WITH FUNCTION function_with_argtypes
+ {
+ CreateFormatterStmt *n = makeNode(CreateFormatterStmt);
+
+ n->sourcetype = $6;
+ n->targettype = $8;
+ n->func = $12;
+ $$ = (Node *) n;
+ }
+ ;
+
+DropFormatterStmt: DROP FORMATTER opt_if_exists FOR CAST '(' Typename AS Typename ')' opt_drop_behavior
+ {
+ DropStmt *n = makeNode(DropStmt);
+
+ n->removeType = OBJECT_FORMATTER;
+ n->objects = list_make1(list_make2($7, $9));
+ n->behavior = $11;
+ n->missing_ok = $3;
+ $$ = (Node *) n;
+ }
+ ;
+
+
/*****************************************************************************
*
* QUERY:
@@ -18933,6 +18968,7 @@ unreserved_keyword:
| FOLLOWING
| FORCE
| FORMAT
+ | FORMATTER
| FORWARD
| FUNCTION
| FUNCTIONS
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 73a56f1df1d..ee66296e0d0 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -37,6 +37,7 @@
#include "commands/event_trigger.h"
#include "commands/explain.h"
#include "commands/extension.h"
+#include "commands/formatter.h"
#include "commands/lockcmds.h"
#include "commands/matview.h"
#include "commands/policy.h"
@@ -193,6 +194,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree)
case T_CreateTableAsStmt:
case T_CreateTableSpaceStmt:
case T_CreateTransformStmt:
+ case T_CreateFormatterStmt:
case T_CreateTrigStmt:
case T_CreateUserMappingStmt:
case T_CreatedbStmt:
@@ -1755,6 +1757,10 @@ ProcessUtilitySlow(ParseState *pstate,
address = CreateTransform((CreateTransformStmt *) parsetree);
break;
+ case T_CreateFormatterStmt:
+ address = CreateFormatter((CreateFormatterStmt *) parsetree);
+ break;
+
case T_AlterOpFamilyStmt:
AlterOpFamily((AlterOpFamilyStmt *) parsetree);
/* commands are stashed in AlterOpFamily */
@@ -2666,6 +2672,9 @@ CreateCommandTag(Node *parsetree)
case OBJECT_TRANSFORM:
tag = CMDTAG_DROP_TRANSFORM;
break;
+ case OBJECT_FORMATTER:
+ tag = CMDTAG_DROP_FORMATTER;
+ break;
case OBJECT_ACCESS_METHOD:
tag = CMDTAG_DROP_ACCESS_METHOD;
break;
@@ -2981,6 +2990,10 @@ CreateCommandTag(Node *parsetree)
tag = CMDTAG_CREATE_TRANSFORM;
break;
+ case T_CreateFormatterStmt:
+ tag = CMDTAG_CREATE_FORMATTER;
+ break;
+
case T_CreateTrigStmt:
tag = CMDTAG_CREATE_TRIGGER;
break;
@@ -3690,6 +3703,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_DDL;
break;
+ case T_CreateFormatterStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_AlterOpFamilyStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index dc98c5c5c09..d70753905eb 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -188,6 +188,9 @@ getSchemaData(Archive *fout, int *numTablesPtr)
pg_log_info("reading transforms");
getTransforms(fout);
+ pg_log_info("reading formatters");
+ getFormatters(fout);
+
pg_log_info("reading table inheritance information");
inhinfo = getInherits(fout, &numInherits);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c56437d6057..92883740743 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -300,6 +300,7 @@ static void dumpProcLang(Archive *fout, const ProcLangInfo *plang);
static void dumpFunc(Archive *fout, const FuncInfo *finfo);
static void dumpCast(Archive *fout, const CastInfo *cast);
static void dumpTransform(Archive *fout, const TransformInfo *transform);
+static void dumpFormatter(Archive *fout, const FormatterInfo *formatter);
static void dumpOpr(Archive *fout, const OprInfo *oprinfo);
static void dumpAccessMethod(Archive *fout, const AccessMethodInfo *aminfo);
static void dumpOpclass(Archive *fout, const OpclassInfo *opcinfo);
@@ -9320,6 +9321,83 @@ getTransforms(Archive *fout)
destroyPQExpBuffer(query);
}
+/*
+ * getFormatters
+ * get basic information about every formatter in the system
+ */
+void
+getFormatters(Archive *fout)
+{
+ PGresult *res;
+ int ntups;
+ int i;
+ PQExpBuffer query;
+ FormatterInfo *formatterinfo;
+ int i_tableoid;
+ int i_oid;
+ int i_fmtsource;
+ int i_fmttarget;
+ int i_fmtfunc;
+
+ /* Formatters were introduced in v19 */
+ if (fout->remoteVersion < 190000)
+ return;
+
+ query = createPQExpBuffer();
+
+ appendPQExpBufferStr(query, "SELECT tableoid, oid, "
+ "fmtsource, fmttarget, fmtfunc "
+ "FROM pg_formatter "
+ "ORDER BY 3,4");
+
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+ ntups = PQntuples(res);
+
+ formatterinfo = pg_malloc_array(FormatterInfo, ntups);
+
+ i_tableoid = PQfnumber(res, "tableoid");
+ i_oid = PQfnumber(res, "oid");
+ i_fmtsource = PQfnumber(res, "fmtsource");
+ i_fmttarget = PQfnumber(res, "fmttarget");
+ i_fmtfunc = PQfnumber(res, "fmtfunc");
+
+ for (i = 0; i < ntups; i++)
+ {
+ PQExpBufferData namebuf;
+ TypeInfo *sTypeInfo;
+ TypeInfo *tTypeInfo;
+
+ formatterinfo[i].dobj.objType = DO_FORMATTER;
+ formatterinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
+ formatterinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
+ AssignDumpId(&formatterinfo[i].dobj);
+ formatterinfo[i].fmtsource = atooid(PQgetvalue(res, i, i_fmtsource));
+ formatterinfo[i].fmttarget = atooid(PQgetvalue(res, i, i_fmttarget));
+ formatterinfo[i].fmtfunc = atooid(PQgetvalue(res, i, i_fmtfunc));
+
+ /*
+ * Try to name the formatter as a concatenation of the type names.
+ * This is only used for purposes of sorting. If we fail to find
+ * either type, the name will be an empty string.
+ */
+ initPQExpBuffer(&namebuf);
+ sTypeInfo = findTypeByOid(formatterinfo[i].fmtsource);
+ tTypeInfo = findTypeByOid(formatterinfo[i].fmttarget);
+ if (sTypeInfo && tTypeInfo)
+ appendPQExpBuffer(&namebuf, "%s %s",
+ sTypeInfo->dobj.name, tTypeInfo->dobj.name);
+ formatterinfo[i].dobj.name = namebuf.data;
+
+ /* Decide whether we want to dump it */
+ selectDumpableObject(&(formatterinfo[i].dobj), fout);
+ }
+
+ PQclear(res);
+
+ destroyPQExpBuffer(query);
+}
+
/*
* getTableAttrs -
* for each interesting table, read info about its attributes
@@ -11913,6 +11991,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
case DO_TRANSFORM:
dumpTransform(fout, (const TransformInfo *) dobj);
break;
+ case DO_FORMATTER:
+ dumpFormatter(fout, (const FormatterInfo *) dobj);
+ break;
case DO_SEQUENCE_SET:
dumpSequenceData(fout, (const TableDataInfo *) dobj);
break;
@@ -14255,6 +14336,90 @@ dumpTransform(Archive *fout, const TransformInfo *transform)
destroyPQExpBuffer(transformargs);
}
+/*
+ * Dump a formatter
+ */
+static void
+dumpFormatter(Archive *fout, const FormatterInfo *formatter)
+{
+ DumpOptions *dopt = fout->dopt;
+ PQExpBuffer defqry;
+ PQExpBuffer delqry;
+ PQExpBuffer labelq;
+ PQExpBuffer formatterargs;
+ FuncInfo *funcInfo;
+ const char *sourceType;
+ const char *targetType;
+
+ /* Do nothing if not dumping schema */
+ if (!dopt->dumpSchema)
+ return;
+
+ /* Cannot dump if we don't have the formatter function's info */
+ funcInfo = findFuncByOid(formatter->fmtfunc);
+ if (funcInfo == NULL)
+ pg_fatal("could not find function definition for function with OID %u",
+ formatter->fmtfunc);
+
+ defqry = createPQExpBuffer();
+ delqry = createPQExpBuffer();
+ labelq = createPQExpBuffer();
+ formatterargs = createPQExpBuffer();
+
+ sourceType = getFormattedTypeName(fout, formatter->fmtsource, zeroAsNone);
+ targetType = getFormattedTypeName(fout, formatter->fmttarget, zeroAsNone);
+
+ appendPQExpBuffer(delqry, "DROP FORMATTER FOR CAST (%s AS %s);\n",
+ sourceType, targetType);
+
+ appendPQExpBuffer(defqry, "CREATE FORMATTER FOR CAST (%s AS %s) ",
+ sourceType, targetType);
+
+ {
+ char *fsig = format_function_signature(fout, funcInfo, true);
+
+ /*
+ * Always qualify the function name (format_function_signature won't
+ * qualify it).
+ */
+ appendPQExpBuffer(defqry, "WITH FUNCTION %s.%s",
+ fmtId(funcInfo->dobj.namespace->dobj.name), fsig);
+ free(fsig);
+ }
+ appendPQExpBufferStr(defqry, ";\n");
+
+ appendPQExpBuffer(labelq, "FORMATTER FOR CAST (%s AS %s)",
+ sourceType, targetType);
+
+ appendPQExpBuffer(formatterargs, "FOR CAST (%s AS %s)",
+ sourceType, targetType);
+
+ if (dopt->binary_upgrade)
+ binary_upgrade_extension_member(defqry, &formatter->dobj,
+ "FORMATTER", formatterargs->data, NULL);
+
+ if (formatter->dobj.dump & DUMP_COMPONENT_DEFINITION)
+ ArchiveEntry(fout, formatter->dobj.catId, formatter->dobj.dumpId,
+ ARCHIVE_OPTS(.tag = labelq->data,
+ .description = "FORMATTER",
+ .section = SECTION_PRE_DATA,
+ .createStmt = defqry->data,
+ .dropStmt = delqry->data,
+ .deps = formatter->dobj.dependencies,
+ .nDeps = formatter->dobj.nDeps));
+
+ /* Dump Formatter Comments */
+ if (formatter->dobj.dump & DUMP_COMPONENT_COMMENT)
+ dumpComment(fout, "FORMATTER", formatterargs->data,
+ NULL, "",
+ formatter->dobj.catId, 0, formatter->dobj.dumpId);
+
+ destroyPQExpBuffer(defqry);
+ destroyPQExpBuffer(delqry);
+ destroyPQExpBuffer(labelq);
+ destroyPQExpBuffer(formatterargs);
+}
+
/*
* dumpOpr
@@ -20696,6 +20861,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
case DO_FDW:
case DO_FOREIGN_SERVER:
case DO_TRANSFORM:
+ case DO_FORMATTER:
/* Pre-data objects: must come before the pre-data boundary */
addObjectDependency(preDataBound, dobj->dumpId);
break;
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 5a6726d8b12..b3adb97b5ba 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -73,6 +73,7 @@ typedef enum
DO_FOREIGN_SERVER,
DO_DEFAULT_ACL,
DO_TRANSFORM,
+ DO_FORMATTER,
DO_LARGE_OBJECT,
DO_LARGE_OBJECT_DATA,
DO_PRE_DATA_BOUNDARY,
@@ -559,6 +560,14 @@ typedef struct _transformInfo
Oid trftosql;
} TransformInfo;
+typedef struct _formatterInfo
+{
+ DumpableObject dobj;
+ Oid fmtsource;
+ Oid fmttarget;
+ Oid fmtfunc;
+} FormatterInfo;
+
/* InhInfo isn't a DumpableObject, just temporary state */
typedef struct _inhInfo
{
@@ -814,6 +823,7 @@ extern void getTriggers(Archive *fout, TableInfo tblinfo[], int numTables);
extern void getProcLangs(Archive *fout);
extern void getCasts(Archive *fout);
extern void getTransforms(Archive *fout);
+extern void getFormatters(Archive *fout);
extern void getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables);
extern bool shouldPrintColumn(const DumpOptions *dopt, const TableInfo *tbinfo, int colno);
extern void getTSParsers(Archive *fout);
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 03e5c1c1116..65df81c9324 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -60,6 +60,7 @@ enum dbObjectTypePriorities
PRIO_EXTENSION,
PRIO_TYPE, /* used for DO_TYPE and DO_SHELL_TYPE */
PRIO_CAST,
+ PRIO_FORMATTER,
PRIO_FUNC,
PRIO_AGG,
PRIO_ACCESS_METHOD,
@@ -128,6 +129,7 @@ static const int dbObjectTypePriority[] =
[DO_FK_CONSTRAINT] = PRIO_FK_CONSTRAINT,
[DO_PROCLANG] = PRIO_PROCLANG,
[DO_CAST] = PRIO_CAST,
+ [DO_FORMATTER] = PRIO_FORMATTER,
[DO_TABLE_DATA] = PRIO_TABLE_DATA,
[DO_SEQUENCE_SET] = PRIO_SEQUENCE_SET,
[DO_DUMMY_TYPE] = PRIO_DUMMY_TYPE,
@@ -1649,6 +1651,13 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
((CastInfo *) obj)->casttarget,
obj->dumpId, obj->catId.oid);
return;
+ case DO_FORMATTER:
+ snprintf(buf, bufsize,
+ "FORMATTER %u to %u (ID %d OID %u)",
+ ((FormatterInfo *) obj)->fmtsource,
+ ((FormatterInfo *) obj)->fmttarget,
+ obj->dumpId, obj->catId.oid);
+ return;
case DO_TRANSFORM:
snprintf(buf, bufsize,
"TRANSFORM %u lang %u (ID %d OID %u)",
diff --git a/src/include/catalog/Makefile b/src/include/catalog/Makefile
index bab57372b88..872f22b3910 100644
--- a/src/include/catalog/Makefile
+++ b/src/include/catalog/Makefile
@@ -75,6 +75,7 @@ CATALOG_HEADERS := \
pg_parameter_acl.h \
pg_partitioned_table.h \
pg_range.h \
+ pg_formatter.h \
pg_transform.h \
pg_sequence.h \
pg_publication.h \
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 635c0d9cb13..6402cc76c87 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202606281
+#define CATALOG_VERSION_NO 202606282
#endif
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index fa836e4ee25..ea7fef5744d 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -62,6 +62,7 @@ catalog_headers = [
'pg_parameter_acl.h',
'pg_partitioned_table.h',
'pg_range.h',
+ 'pg_formatter.h',
'pg_transform.h',
'pg_sequence.h',
'pg_publication.h',
diff --git a/src/include/catalog/pg_formatter.h b/src/include/catalog/pg_formatter.h
new file mode 100644
index 00000000000..e41a1de54ce
--- /dev/null
+++ b/src/include/catalog/pg_formatter.h
@@ -0,0 +1,62 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_formatter.h
+ * definition of the "formatter" system catalog (pg_formatter)
+ *
+ * A formatter registers a function that implements
+ * CAST(expr AS target FORMAT format_expr) for a particular
+ * (source type, target type) pair. The formatter function receives the
+ * source value and the FORMAT expression (as text) and returns the target
+ * type. This is intentionally separate from pg_cast: ordinary casts have
+ * implicit/assignment/explicit contexts and binary-coercible/WITH INOUT/
+ * WITHOUT FUNCTION methods, whereas a formatted cast is always explicit and
+ * always requires a function.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/catalog/pg_formatter.h
+ *
+ * NOTES
+ * The Catalog.pm module reads this file and derives schema
+ * information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_FORMATTER_H
+#define PG_FORMATTER_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_formatter_d.h" /* IWYU pragma: export */
+
+/* ----------------
+ * pg_formatter definition. cpp turns this into
+ * typedef struct FormData_pg_formatter
+ * ----------------
+ */
+BEGIN_CATALOG_STRUCT
+
+CATALOG(pg_formatter,8647,FormatterRelationId)
+{
+ Oid oid; /* oid */
+ Oid fmtsource BKI_LOOKUP(pg_type); /* source type */
+ Oid fmttarget BKI_LOOKUP(pg_type); /* target type */
+ Oid fmtfunc BKI_LOOKUP(pg_proc); /* formatter function */
+} FormData_pg_formatter;
+
+END_CATALOG_STRUCT
+
+/* ----------------
+ * Form_pg_formatter corresponds to a pointer to a tuple with
+ * the format of pg_formatter relation.
+ * ----------------
+ */
+typedef FormData_pg_formatter *Form_pg_formatter;
+
+DECLARE_UNIQUE_INDEX_PKEY(pg_formatter_oid_index, 8648, FormatterOidIndexId, pg_formatter, btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX(pg_formatter_source_target_index, 8649, FormatterSourceTargetIndexId, pg_formatter, btree(fmtsource oid_ops, fmttarget oid_ops));
+
+MAKE_SYSCACHE(FORMATTEROID, pg_formatter_oid_index, 8);
+MAKE_SYSCACHE(FORMATTERSOURCETARGET, pg_formatter_source_target_index, 8);
+
+#endif /* PG_FORMATTER_H */
diff --git a/src/include/commands/formatter.h b/src/include/commands/formatter.h
new file mode 100644
index 00000000000..c68a8745670
--- /dev/null
+++ b/src/include/commands/formatter.h
@@ -0,0 +1,24 @@
+/*-------------------------------------------------------------------------
+ *
+ * formatter.h
+ * prototypes for formattercmds.c.
+ *
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/commands/formatter.h
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef FORMATTER_H
+#define FORMATTER_H
+
+#include "catalog/objectaddress.h"
+#include "nodes/parsenodes.h"
+
+extern ObjectAddress CreateFormatter(CreateFormatterStmt *stmt);
+extern Oid get_formatter_oid(Oid sourcetypeid, Oid targettypeid,
+ bool missing_ok);
+
+#endif /* FORMATTER_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 759c6bfae54..0b0431f4b6a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2445,6 +2445,7 @@ typedef enum ObjectType
OBJECT_FDW,
OBJECT_FOREIGN_SERVER,
OBJECT_FOREIGN_TABLE,
+ OBJECT_FORMATTER,
OBJECT_FUNCTION,
OBJECT_INDEX,
OBJECT_LANGUAGE,
@@ -4356,6 +4357,22 @@ typedef struct CreateTransformStmt
ObjectWithArgs *tosql;
} CreateTransformStmt;
+/* ----------------------
+ * CREATE FORMATTER Statement
+ *
+ * Registers a formatter function for CAST(... AS target FORMAT ...) on a
+ * (sourcetype, targettype) pair. DROP FORMATTER reuses the generic
+ * DropStmt path with removeType == OBJECT_FORMATTER.
+ * ----------------------
+ */
+typedef struct CreateFormatterStmt
+{
+ NodeTag type;
+ TypeName *sourcetype; /* source data type */
+ TypeName *targettype; /* target data type */
+ ObjectWithArgs *func; /* formatter function */
+} CreateFormatterStmt;
+
/* ----------------------
* PREPARE Statement
* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 51ead54f015..7ce539c79f9 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -183,6 +183,7 @@ PG_KEYWORD("for", FOR, RESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("force", FORCE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("foreign", FOREIGN, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("format", FORMAT, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("formatter", FORMATTER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("forward", FORWARD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("freeze", FREEZE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("from", FROM, RESERVED_KEYWORD, AS_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index befae5f6b4f..c5701b4a8aa 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -95,6 +95,7 @@ PG_CMDTAG(CMDTAG_CREATE_EVENT_TRIGGER, "CREATE EVENT TRIGGER", false, false, fal
PG_CMDTAG(CMDTAG_CREATE_EXTENSION, "CREATE EXTENSION", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_FOREIGN_DATA_WRAPPER, "CREATE FOREIGN DATA WRAPPER", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_FOREIGN_TABLE, "CREATE FOREIGN TABLE", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_FORMATTER, "CREATE FORMATTER", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_FUNCTION, "CREATE FUNCTION", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_INDEX, "CREATE INDEX", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_LANGUAGE, "CREATE LANGUAGE", true, false, false)
@@ -148,6 +149,7 @@ PG_CMDTAG(CMDTAG_DROP_EVENT_TRIGGER, "DROP EVENT TRIGGER", false, false, false)
PG_CMDTAG(CMDTAG_DROP_EXTENSION, "DROP EXTENSION", true, false, false)
PG_CMDTAG(CMDTAG_DROP_FOREIGN_DATA_WRAPPER, "DROP FOREIGN DATA WRAPPER", true, false, false)
PG_CMDTAG(CMDTAG_DROP_FOREIGN_TABLE, "DROP FOREIGN TABLE", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_FORMATTER, "DROP FORMATTER", true, false, false)
PG_CMDTAG(CMDTAG_DROP_FUNCTION, "DROP FUNCTION", true, false, false)
PG_CMDTAG(CMDTAG_DROP_INDEX, "DROP INDEX", true, false, false)
PG_CMDTAG(CMDTAG_DROP_LANGUAGE, "DROP LANGUAGE", true, false, false)
diff --git a/src/test/regress/expected/formatters.out b/src/test/regress/expected/formatters.out
new file mode 100644
index 00000000000..bb1f091a1b8
--- /dev/null
+++ b/src/test/regress/expected/formatters.out
@@ -0,0 +1,116 @@
+--
+-- FORMATTERS
+--
+-- CREATE/DROP FORMATTER registers formatter metadata in pg_formatter,
+-- keyed by a (source type, target type) pair. This is catalog and DDL
+-- infrastructure only; it does not transform or execute formatted casts.
+-- A simple formatter function with the required signature
+-- formatter(source_type, text) returns target_type
+CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+-- It shows up in the catalog (use regtype/regprocedure, not raw OIDs)
+SELECT fmtsource::regtype, fmttarget::regtype, fmtfunc::regprocedure
+ FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+ fmtsource | fmttarget | fmtfunc
+-----------+-----------+--------------------------------
+ integer | text | int4_to_text_fmt(integer,text)
+(1 row)
+
+-- ... and as a first-class object
+SELECT pg_describe_object('pg_formatter'::regclass, oid, 0)
+ FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+ pg_describe_object
+-----------------------------------------
+ formatter for cast from integer to text
+(1 row)
+
+-- Only one formatter per (source, target) pair
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+ERROR: formatter for cast from type integer to type text already exists
+-- Function signature validation
+CREATE FUNCTION fmt_bad_nargs(integer) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_nargs(integer); -- wrong # of arguments
+ERROR: formatter function must take exactly two arguments
+CREATE FUNCTION fmt_bad_arg2(integer, integer) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_arg2(integer, integer); -- second arg not text
+ERROR: second argument of formatter function must be type text
+CREATE FUNCTION fmt_bad_arg1(bigint, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_arg1(bigint, text); -- first arg mismatch
+ERROR: first argument of formatter function must be type integer
+CREATE FUNCTION fmt_bad_ret(integer, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE RETURN true;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_ret(integer, text); -- return type mismatch
+ERROR: return data type of formatter function must be type text
+CREATE FUNCTION fmt_bad_set(integer, text) RETURNS SETOF text
+ LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text $$;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_set(integer, text); -- set-returning rejected
+ERROR: formatter function must not return a set
+-- No pseudo-types
+CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
+ LANGUAGE sql IMMUTABLE AS $$ SELECT $2 $$;
+CREATE FORMATTER FOR CAST (anyelement AS text)
+ WITH FUNCTION fmt_anyel(anyelement, text);
+ERROR: source data type anyelement is a pseudo-type
+-- Registering a formatter does not enable a formatted cast: the FORMAT
+-- clause must not be silently ignored or rewritten to a built-in function,
+-- so CAST(... FORMAT ...) is rejected during parse analysis.
+SELECT CAST(5 AS text FORMAT 'YYYY');
+ERROR: formatted casts are not implemented yet
+LINE 1: SELECT CAST(5 AS text FORMAT 'YYYY');
+ ^
+DETAIL: No formatter resolution mechanism is available.
+-- Dependency behavior: the formatter depends on its function.
+DROP FUNCTION int4_to_text_fmt(integer, text); -- fails (RESTRICT)
+ERROR: cannot drop function int4_to_text_fmt(integer,text) because other objects depend on it
+DETAIL: formatter for cast from integer to text depends on function int4_to_text_fmt(integer,text)
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP FUNCTION int4_to_text_fmt(integer, text) CASCADE; -- drops formatter
+NOTICE: drop cascades to formatter for cast from integer to text
+SELECT count(*) FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+ count
+-------
+ 0
+(1 row)
+
+-- Privileges: the creator must own the source or the target type. (The
+-- ownership check happens before the function is looked up, so the function
+-- name here is irrelevant.)
+CREATE ROLE regress_formatter_user;
+SET ROLE regress_formatter_user;
+CREATE FORMATTER FOR CAST (text AS integer)
+ WITH FUNCTION pg_catalog.length(text);
+ERROR: must be owner of type text or type integer
+RESET ROLE;
+DROP ROLE regress_formatter_user;
+-- DROP FORMATTER, including IF EXISTS
+CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+DROP FORMATTER FOR CAST (integer AS text);
+DROP FORMATTER FOR CAST (integer AS text); -- fails, gone
+ERROR: formatter for cast from type integer to type text does not exist
+DROP FORMATTER IF EXISTS FOR CAST (integer AS text); -- notice, no error
+NOTICE: formatter for cast from type integer to type text does not exist, skipping
+-- Clean up
+DROP FUNCTION int4_to_text_fmt(integer, text);
+DROP FUNCTION fmt_bad_nargs(integer);
+DROP FUNCTION fmt_bad_arg2(integer, integer);
+DROP FUNCTION fmt_bad_arg1(bigint, text);
+DROP FUNCTION fmt_bad_ret(integer, text);
+DROP FUNCTION fmt_bad_set(integer, text);
+DROP FUNCTION fmt_anyel(anyelement, text);
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index d64169b7bf0..6b60b02aab3 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -257,6 +257,9 @@ NOTICE: checking pg_range {rngmltconstruct1} => pg_proc {oid}
NOTICE: checking pg_range {rngmltconstruct2} => pg_proc {oid}
NOTICE: checking pg_range {rngcanonical} => pg_proc {oid}
NOTICE: checking pg_range {rngsubdiff} => pg_proc {oid}
+NOTICE: checking pg_formatter {fmtsource} => pg_type {oid}
+NOTICE: checking pg_formatter {fmttarget} => pg_type {oid}
+NOTICE: checking pg_formatter {fmtfunc} => pg_proc {oid}
NOTICE: checking pg_transform {trftype} => pg_type {oid}
NOTICE: checking pg_transform {trflang} => pg_language {oid}
NOTICE: checking pg_transform {trffromsql} => pg_proc {oid}
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..a0281f1fb2a 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -48,7 +48,7 @@ test: create_index create_index_spgist create_view index_including index_includi
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse create_property_graph for_portion_of
+test: create_aggregate create_function_sql create_cast formatters constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse create_property_graph for_portion_of
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/sql/formatters.sql b/src/test/regress/sql/formatters.sql
new file mode 100644
index 00000000000..8050aee6f45
--- /dev/null
+++ b/src/test/regress/sql/formatters.sql
@@ -0,0 +1,99 @@
+--
+-- FORMATTERS
+--
+-- CREATE/DROP FORMATTER registers formatter metadata in pg_formatter,
+-- keyed by a (source type, target type) pair. This is catalog and DDL
+-- infrastructure only; it does not transform or execute formatted casts.
+
+-- A simple formatter function with the required signature
+-- formatter(source_type, text) returns target_type
+CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+
+-- It shows up in the catalog (use regtype/regprocedure, not raw OIDs)
+SELECT fmtsource::regtype, fmttarget::regtype, fmtfunc::regprocedure
+ FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+
+-- ... and as a first-class object
+SELECT pg_describe_object('pg_formatter'::regclass, oid, 0)
+ FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+
+-- Only one formatter per (source, target) pair
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+
+-- Function signature validation
+CREATE FUNCTION fmt_bad_nargs(integer) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_nargs(integer); -- wrong # of arguments
+
+CREATE FUNCTION fmt_bad_arg2(integer, integer) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_arg2(integer, integer); -- second arg not text
+
+CREATE FUNCTION fmt_bad_arg1(bigint, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_arg1(bigint, text); -- first arg mismatch
+
+CREATE FUNCTION fmt_bad_ret(integer, text) RETURNS boolean
+ LANGUAGE sql IMMUTABLE RETURN true;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_ret(integer, text); -- return type mismatch
+
+CREATE FUNCTION fmt_bad_set(integer, text) RETURNS SETOF text
+ LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text $$;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION fmt_bad_set(integer, text); -- set-returning rejected
+
+-- No pseudo-types
+CREATE FUNCTION fmt_anyel(anyelement, text) RETURNS text
+ LANGUAGE sql IMMUTABLE AS $$ SELECT $2 $$;
+CREATE FORMATTER FOR CAST (anyelement AS text)
+ WITH FUNCTION fmt_anyel(anyelement, text);
+
+-- Registering a formatter does not enable a formatted cast: the FORMAT
+-- clause must not be silently ignored or rewritten to a built-in function,
+-- so CAST(... FORMAT ...) is rejected during parse analysis.
+SELECT CAST(5 AS text FORMAT 'YYYY');
+
+-- Dependency behavior: the formatter depends on its function.
+DROP FUNCTION int4_to_text_fmt(integer, text); -- fails (RESTRICT)
+DROP FUNCTION int4_to_text_fmt(integer, text) CASCADE; -- drops formatter
+SELECT count(*) FROM pg_formatter
+ WHERE fmtsource = 'integer'::regtype AND fmttarget = 'text'::regtype;
+
+-- Privileges: the creator must own the source or the target type. (The
+-- ownership check happens before the function is looked up, so the function
+-- name here is irrelevant.)
+CREATE ROLE regress_formatter_user;
+SET ROLE regress_formatter_user;
+CREATE FORMATTER FOR CAST (text AS integer)
+ WITH FUNCTION pg_catalog.length(text);
+RESET ROLE;
+DROP ROLE regress_formatter_user;
+
+-- DROP FORMATTER, including IF EXISTS
+CREATE FUNCTION int4_to_text_fmt(integer, text) RETURNS text
+ LANGUAGE sql IMMUTABLE RETURN $1::text || ':' || $2;
+CREATE FORMATTER FOR CAST (integer AS text)
+ WITH FUNCTION int4_to_text_fmt(integer, text);
+DROP FORMATTER FOR CAST (integer AS text);
+DROP FORMATTER FOR CAST (integer AS text); -- fails, gone
+DROP FORMATTER IF EXISTS FOR CAST (integer AS text); -- notice, no error
+
+-- Clean up
+DROP FUNCTION int4_to_text_fmt(integer, text);
+DROP FUNCTION fmt_bad_nargs(integer);
+DROP FUNCTION fmt_bad_arg2(integer, integer);
+DROP FUNCTION fmt_bad_arg1(bigint, text);
+DROP FUNCTION fmt_bad_ret(integer, text);
+DROP FUNCTION fmt_bad_set(integer, text);
+DROP FUNCTION fmt_anyel(anyelement, text);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..2feea74b3a5 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -575,6 +575,7 @@ CreateExtensionStmt
CreateFdwStmt
CreateForeignServerStmt
CreateForeignTableStmt
+CreateFormatterStmt
CreateFunctionStmt
CreateOpClassItem
CreateOpClassStmt
@@ -921,6 +922,7 @@ FormData_pg_extension
FormData_pg_foreign_data_wrapper
FormData_pg_foreign_server
FormData_pg_foreign_table
+FormData_pg_formatter
FormData_pg_index
FormData_pg_inherits
FormData_pg_language
@@ -986,6 +988,7 @@ Form_pg_extension
Form_pg_foreign_data_wrapper
Form_pg_foreign_server
Form_pg_foreign_table
+Form_pg_formatter
Form_pg_index
Form_pg_inherits
Form_pg_language
@@ -1028,6 +1031,7 @@ Form_pg_ts_template
Form_pg_type
Form_pg_user_mapping
FormatNode
+FormatterInfo
FreeBlockNumberArray
FreeListData
FreePageBtree
--
2.54.0
view thread (56+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: implement CAST(expr AS type FORMAT 'template')
In-Reply-To: <CABXr29FyPC7terFF7E+r462BEHhYgv06oUVoBrhkH7xhshuE6A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox