v3-0001-CAST-val-AS-type-FORMAT-template.patch

From c944169304c3922c3cc76166ccd7a54cfcf595ba Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 29 Jul 2025 11:10:24 +0800
Subject: [PATCH v3 1/1] CAST(val AS type FORMAT 'template')

context: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Major_features_simply_not_implemented_yet
discussion: https://postgr.es/m/CACJufxGqm7cYQ5C65Eoh1z-f+aMdhv9_7V=NoLH_p6uuyesi6A@mail.gmail.com
---
 src/backend/catalog/pg_aggregate.c     |   1 +
 src/backend/catalog/pg_proc.c          |  61 ++++++
 src/backend/commands/functioncmds.c    |  22 +-
 src/backend/commands/typecmds.c        |   4 +
 src/backend/nodes/nodeFuncs.c          |   2 +
 src/backend/parser/gram.y              |  21 ++
 src/backend/parser/parse_coerce.c      | 266 +++++++++++++++++++++++++
 src/backend/parser/parse_expr.c        |  27 ++-
 src/backend/parser/parse_utilcmd.c     |   1 +
 src/backend/utils/adt/ruleutils.c      |  70 +++++++
 src/backend/utils/cache/lsyscache.c    |  19 ++
 src/include/catalog/pg_proc.dat        |  22 +-
 src/include/catalog/pg_proc.h          |   6 +-
 src/include/nodes/parsenodes.h         |   1 +
 src/include/parser/parse_coerce.h      |   8 +
 src/include/utils/lsyscache.h          |   1 +
 src/test/regress/expected/horology.out |  94 +++++++++
 src/test/regress/expected/misc.out     | 169 ++++++++++++++++
 src/test/regress/expected/numeric.out  |  49 ++++-
 src/test/regress/sql/horology.sql      |  26 +++
 src/test/regress/sql/misc.sql          |  47 +++++
 src/test/regress/sql/numeric.sql       |  11 +-
 22 files changed, 905 insertions(+), 23 deletions(-)

diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index a05f8a87c1f..10eb6ff4ce4 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -627,6 +627,7 @@ AggregateCreate(const char *aggName,
 							 false, /* security invoker (currently not
 									 * definable for agg) */
 							 false, /* isLeakProof */
+							 false, /* format */
 							 false, /* isStrict (not needed for agg) */
 							 PROVOLATILE_IMMUTABLE, /* volatility (not needed
 													 * for agg) */
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 5fdcf24d5f8..58d13dc4543 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -16,6 +16,7 @@
 
 #include "access/htup_details.h"
 #include "access/table.h"
+#include "access/tableam.h"
 #include "access/xact.h"
 #include "catalog/catalog.h"
 #include "catalog/dependency.h"
@@ -38,6 +39,7 @@
 #include "tcop/tcopprot.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
+#include "utils/fmgroids.h"
 #include "utils/lsyscache.h"
 #include "utils/regproc.h"
 #include "utils/rel.h"
@@ -109,6 +111,7 @@ ProcedureCreate(const char *procedureName,
 				char prokind,
 				bool security_definer,
 				bool isLeakProof,
+				bool isFormat,
 				bool isStrict,
 				char volatility,
 				char parallel,
@@ -336,6 +339,7 @@ ProcedureCreate(const char *procedureName,
 	values[Anum_pg_proc_prokind - 1] = CharGetDatum(prokind);
 	values[Anum_pg_proc_prosecdef - 1] = BoolGetDatum(security_definer);
 	values[Anum_pg_proc_proleakproof - 1] = BoolGetDatum(isLeakProof);
+	values[Anum_pg_proc_proformat - 1] = BoolGetDatum(isFormat);
 	values[Anum_pg_proc_proisstrict - 1] = BoolGetDatum(isStrict);
 	values[Anum_pg_proc_proretset - 1] = BoolGetDatum(returnsSet);
 	values[Anum_pg_proc_provolatile - 1] = CharGetDatum(volatility);
@@ -382,6 +386,63 @@ ProcedureCreate(const char *procedureName,
 	rel = table_open(ProcedureRelationId, RowExclusiveLock);
 	tupDesc = RelationGetDescr(rel);
 
+	if (isFormat)
+	{
+		if (parameterCount != 2 || parameterTypes->values[1] != TEXTOID)
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function should hav only two argument and the last argument type must be TEXTOID"));
+		if (parameterTypes->values[0] != TEXTOID && returnType != TEXTOID)
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function first input argument should be TEXTOID or the return type as TEXTOID"));
+		if (parameterTypes->values[1] == TEXTOID)
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function second input argument must be TEXTOID"));
+		if (returnsSet)
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function must not return a set"));
+		if (parameterModes != PointerGetDatum(NULL))
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function proargmodes should be NULL"));
+		if (prokind != PROKIND_FUNCTION)
+			ereport(ERROR,
+					errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					errmsg("type format function can only be normal function"));
+	}
+
+	/* unlikely be reachable, since type format function is rare */
+	if (isFormat)
+	{
+		SysScanDesc	sscan;
+		ScanKeyData scankey;
+		HeapTuple	protup;
+
+		ScanKeyInit(&scankey,
+					Anum_pg_proc_proformat,
+					BTEqualStrategyNumber, F_BOOLEQ,
+					BoolGetDatum(true));
+		sscan = systable_beginscan(rel, ProcedureProformatIndexId, true,
+								   SnapshotSelf, 1, &scankey);
+		while (HeapTupleIsValid(protup = systable_getnext(sscan)))
+		{
+			Form_pg_proc procform = (Form_pg_proc) GETSTRUCT(protup);
+			if (procform->proformat &&
+				procform->proargtypes.values[0] == parameterTypes->values[0] &&
+				procform->proargtypes.values[1] == TEXTOID &&
+				procform->prorettype == returnType)
+				ereport(ERROR,
+						errcode(ERRCODE_DUPLICATE_FUNCTION),
+						errmsg("formatter function \"%s\" already exists with for type formatter %s",
+								get_func_name(procform->oid	),
+								format_type_be(parameterTypes->values[0])));
+		}
+		systable_endscan(sscan);
+	}
+
 	/* Check for pre-existing definition */
 	oldtup = SearchSysCache3(PROCNAMEARGSNSP,
 							 PointerGetDatum(procedureName),
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 0335e982b31..aadcc1dd8c3 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -519,6 +519,7 @@ compute_common_attribute(ParseState *pstate,
 						 DefElem **strict_item,
 						 DefElem **security_item,
 						 DefElem **leakproof_item,
+						 DefElem **format_item,
 						 List **set_items,
 						 DefElem **cost_item,
 						 DefElem **rows_item,
@@ -559,6 +560,14 @@ compute_common_attribute(ParseState *pstate,
 
 		*leakproof_item = defel;
 	}
+	else if (strcmp(defel->defname, "format") == 0)
+	{
+		if (is_procedure)
+			goto procedure_error;
+		if (*format_item)
+			errorConflictingDefElem(defel, pstate);
+		*format_item = defel;
+	}
 	else if (strcmp(defel->defname, "set") == 0)
 	{
 		*set_items = lappend(*set_items, defel->arg);
@@ -737,6 +746,7 @@ compute_function_attributes(ParseState *pstate,
 							bool *strict_p,
 							bool *security_definer,
 							bool *leakproof_p,
+							bool *format_p,
 							ArrayType **proconfig,
 							float4 *procost,
 							float4 *prorows,
@@ -752,6 +762,7 @@ compute_function_attributes(ParseState *pstate,
 	DefElem    *strict_item = NULL;
 	DefElem    *security_item = NULL;
 	DefElem    *leakproof_item = NULL;
+	DefElem    *format_item = NULL;
 	List	   *set_items = NIL;
 	DefElem    *cost_item = NULL;
 	DefElem    *rows_item = NULL;
@@ -798,6 +809,7 @@ compute_function_attributes(ParseState *pstate,
 										  &strict_item,
 										  &security_item,
 										  &leakproof_item,
+										  &format_item,
 										  &set_items,
 										  &cost_item,
 										  &rows_item,
@@ -828,6 +840,8 @@ compute_function_attributes(ParseState *pstate,
 		*security_definer = boolVal(security_item->arg);
 	if (leakproof_item)
 		*leakproof_p = boolVal(leakproof_item->arg);
+	if (format_item)
+		*format_p = boolVal(format_item->arg);
 	if (set_items)
 		*proconfig = update_proconfig_value(NULL, set_items);
 	if (cost_item)
@@ -1052,7 +1066,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 	bool		isWindowFunc,
 				isStrict,
 				security,
-				isLeakProof;
+				isLeakProof,
+				isFormat;
 	char		volatility;
 	ArrayType  *proconfig;
 	float4		procost;
@@ -1080,6 +1095,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 	isStrict = false;
 	security = false;
 	isLeakProof = false;
+	isFormat = false;
 	volatility = PROVOLATILE_VOLATILE;
 	proconfig = NULL;
 	procost = -1;				/* indicates not set */
@@ -1094,6 +1110,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 								&as_clause, &language, &transformDefElem,
 								&isWindowFunc, &volatility,
 								&isStrict, &security, &isLeakProof,
+								&isFormat,
 								&proconfig, &procost, &prorows,
 								&prosupport, &parallel);
 
@@ -1285,6 +1302,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt)
 						   stmt->is_procedure ? PROKIND_PROCEDURE : (isWindowFunc ? PROKIND_WINDOW : PROKIND_FUNCTION),
 						   security,
 						   isLeakProof,
+						   isFormat,
 						   isStrict,
 						   volatility,
 						   parallel,
@@ -1370,6 +1388,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
 	DefElem    *strict_item = NULL;
 	DefElem    *security_def_item = NULL;
 	DefElem    *leakproof_item = NULL;
+	DefElem    *format_item = NULL;
 	List	   *set_items = NIL;
 	DefElem    *cost_item = NULL;
 	DefElem    *rows_item = NULL;
@@ -1414,6 +1433,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt)
 									 &strict_item,
 									 &security_def_item,
 									 &leakproof_item,
+									 &format_item,
 									 &set_items,
 									 &cost_item,
 									 &rows_item,
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 26d985193ae..ccd5c8a14a5 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -1809,6 +1809,7 @@ makeRangeConstructors(const char *name, Oid namespace,
 								 PROKIND_FUNCTION,
 								 false, /* security_definer */
 								 false, /* leakproof */
+								 false, /* format */
 								 false, /* isStrict */
 								 PROVOLATILE_IMMUTABLE, /* volatility */
 								 PROPARALLEL_SAFE,	/* parallel safety */
@@ -1875,6 +1876,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
 							 PROKIND_FUNCTION,
 							 false, /* security_definer */
 							 false, /* leakproof */
+							 false, /* format */
 							 true,	/* isStrict */
 							 PROVOLATILE_IMMUTABLE, /* volatility */
 							 PROPARALLEL_SAFE,	/* parallel safety */
@@ -1920,6 +1922,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
 							 PROKIND_FUNCTION,
 							 false, /* security_definer */
 							 false, /* leakproof */
+							 false, /* format */
 							 true,	/* isStrict */
 							 PROVOLATILE_IMMUTABLE, /* volatility */
 							 PROPARALLEL_SAFE,	/* parallel safety */
@@ -1959,6 +1962,7 @@ makeMultirangeConstructors(const char *name, Oid namespace,
 							 PROKIND_FUNCTION,
 							 false, /* security_definer */
 							 false, /* leakproof */
+							 false, /* format */
 							 true,	/* isStrict */
 							 PROVOLATILE_IMMUTABLE, /* volatility */
 							 PROPARALLEL_SAFE,	/* parallel safety */
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..91560bd1844 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4464,6 +4464,8 @@ raw_expression_tree_walker_impl(Node *node,
 
 				if (WALK(tc->arg))
 					return true;
+				if (WALK(tc->format))
+					return true;
 				if (WALK(tc->typeName))
 					return true;
 			}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 73345bb3c70..b21298f75a6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -156,6 +156,8 @@ static RawStmt *makeRawStmt(Node *stmt, int stmt_location);
 static void updateRawStmtEnd(RawStmt *rs, int end_location);
 static Node *makeColumnRef(char *colname, List *indirection,
 						   int location, core_yyscan_t yyscanner);
+static Node *makeFormattedTypeCast(Node *arg, Node *format,
+								   TypeName *typename, int location);
 static Node *makeTypeCast(Node *arg, TypeName *typename, int location);
 static Node *makeStringConstCast(char *str, int location, TypeName *typename);
 static Node *makeIntConst(int val, int location);
@@ -8828,6 +8830,10 @@ common_func_opt_item:
 				{
 					$$ = makeDefElem("leakproof", (Node *) makeBoolean(true), @1);
 				}
+			| FORMAT
+				{
+					$$ = makeDefElem("format", (Node *) makeBoolean(true), @1);
+				}
 			| NOT LEAKPROOF
 				{
 					$$ = makeDefElem("leakproof", (Node *) makeBoolean(false), @1);
@@ -15945,6 +15951,8 @@ func_expr_common_subexpr:
 				}
 			| CAST '(' a_expr AS Typename ')'
 				{ $$ = makeTypeCast($3, $5, @1); }
+			| CAST '(' a_expr AS Typename FORMAT a_expr ')'
+				{ $$ = makeFormattedTypeCast($3, $7, $5, @1); }
 			| EXTRACT '(' extract_list ')'
 				{
 					$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
@@ -18832,12 +18840,25 @@ makeColumnRef(char *colname, List *indirection,
 	return (Node *) c;
 }
 
+static Node *
+makeFormattedTypeCast(Node *arg, Node *format, TypeName *typename, int location)
+{
+	TypeCast   *n = makeNode(TypeCast);
+
+	n->arg = arg;
+	n->format = format;
+	n->typeName = typename;
+	n->location = location;
+	return (Node *) n;
+}
+
 static Node *
 makeTypeCast(Node *arg, TypeName *typename, int location)
 {
 	TypeCast   *n = makeNode(TypeCast);
 
 	n->arg = arg;
+	n->format = NULL;
 	n->typeName = typename;
 	n->location = location;
 	return (Node *) n;
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 0b5b81c7f27..9eee9da6447 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -14,6 +14,9 @@
  */
 #include "postgres.h"
 
+#include "access/table.h"
+#include "access/tableam.h"
+#include "access/heapam.h"
 #include "catalog/pg_cast.h"
 #include "catalog/pg_class.h"
 #include "catalog/pg_inherits.h"
@@ -130,6 +133,66 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
 	return result;
 }
 
+/*
+ * For CAST(A AS TYPE FORMAT 'template'),
+ * generate a FuncExpr representing the underlying function call.
+ * See coerce_to_target_type for normal type coerce.
+ */
+Node *
+coerce_to_target_type_fmt(ParseState *pstate, Node *expr, Node *format,
+						  Oid exprtype, Oid targettype, int32 targettypmod,
+						  CoercionContext ccontext, CoercionForm cformat,
+						  int location)
+{
+	Node	   *result;
+	Node	   *origexpr;
+
+	if (!can_coerce_type(1, &exprtype, &targettype, ccontext))
+		return NULL;
+
+	/*
+	 * If the input has a CollateExpr at the top, strip it off, perform the
+	 * coercion, and put a new one back on.  This is annoying since it
+	 * duplicates logic in coerce_type, but if we don't do this then it's too
+	 * hard to tell whether coerce_type actually changed anything, and we
+	 * *must* know that to avoid possibly calling hide_coercion_node on
+	 * something that wasn't generated by coerce_type.  Note that if there are
+	 * multiple stacked CollateExprs, we just discard all but the topmost.
+	 * Also, if the target type isn't collatable, we discard the CollateExpr.
+	 */
+	origexpr = expr;
+	while (expr && IsA(expr, CollateExpr))
+		expr = (Node *) ((CollateExpr *) expr)->arg;
+
+	result = coerce_type_fmt(pstate, expr, format, exprtype,
+							 targettype, targettypmod,
+							 ccontext, cformat, location);
+
+	/*
+	 * If the target is a fixed-length type, it may need a length coercion as
+	 * well as a type coercion.  If we find ourselves adding both, force the
+	 * inner coercion node to implicit display form.
+	 */
+	result = coerce_type_typmod(result,
+								targettype, targettypmod,
+								ccontext, cformat, location,
+								(result != expr && !IsA(result, Const)));
+
+	if (expr != origexpr && type_is_collatable(targettype))
+	{
+		/* Reinstall top CollateExpr */
+		CollateExpr *coll = (CollateExpr *) origexpr;
+		CollateExpr *newcoll = makeNode(CollateExpr);
+
+		newcoll->arg = (Expr *) result;
+		newcoll->collOid = coll->collOid;
+		newcoll->location = coll->location;
+		result = (Node *) newcoll;
+	}
+
+	return result;
+}
+
 
 /*
  * coerce_type()
@@ -545,6 +608,209 @@ coerce_type(ParseState *pstate, Node *node,
 	return NULL;				/* keep compiler quiet */
 }
 
+Node *
+coerce_type_fmt(ParseState *pstate, Node *node, Node *format,
+				Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+				CoercionContext ccontext, CoercionForm cformat, int location)
+{
+	Node	   *result;
+	Node	   *fmt;
+	Node	   *source = NULL;
+	Oid			funcId = InvalidOid;
+	Oid			baseTypeId;
+	int32		baseTypeMod;
+	Oid			inputBaseTypeId;
+	FuncExpr   *fexpr;
+	Type		textType;
+	List	   *args;
+	Relation	pg_proc;
+	SysScanDesc	sscan;
+	ScanKeyData scankey;
+	HeapTuple	tuple;
+
+	baseTypeMod = targetTypeMod;
+	baseTypeId = getBaseTypeAndTypmod(targetTypeId, &baseTypeMod);
+	inputBaseTypeId = getBaseType(inputTypeId);
+
+	if (targetTypeId == inputTypeId ||
+		node == NULL)
+	{
+		/* no conversion needed */
+		return node;
+	}
+
+	textType = typeidType(TEXTOID);
+	if (IsA(format, Const) && exprType(format) == UNKNOWNOID)
+	{
+		Const	   *con = (Const *) format;
+		Const	   *fmtcon = NULL;
+		fmtcon = makeNode(Const);
+		fmtcon->consttype = TEXTOID;
+		fmtcon->consttypmod = -1;
+		fmtcon->constcollid = typeTypeCollation(textType);
+		fmtcon->constlen = typeLen(textType);
+		fmtcon->constbyval = typeByVal(textType);
+		fmtcon->constisnull = con->constisnull;
+		fmtcon->location = exprLocation(format);
+
+		/* format string can not be null */
+		Assert(!con->constisnull);
+		fmtcon->constvalue = stringTypeDatum(textType,
+											 DatumGetCString(con->constvalue),
+											 -1);
+		fmtcon->constvalue =
+			PointerGetDatum(PG_DETOAST_DATUM(fmtcon->constvalue));
+		fmt = (Node *) fmtcon;
+	}
+	else
+	{
+		if (TypeCategory(exprType(format)) != TYPCATEGORY_STRING)
+			ereport(ERROR,
+					errcode(ERRCODE_CANNOT_COERCE),
+					errmsg("FORMAT template is not string type"),
+					parser_errposition(pstate, exprLocation(format)));
+
+		if (expression_returns_set(format))
+			ereport(ERROR,
+					errcode(ERRCODE_DATATYPE_MISMATCH),
+					errmsg("FORMAT template expression must not return a set"),
+					parser_errposition(pstate, exprLocation(format)));
+
+		fmt = format;
+	}
+
+	if (inputTypeId == UNKNOWNOID && IsA(node, Const))
+	{
+		/*
+		 * We assume here that UNKNOWN's internal representation is the same as
+		 * CSTRING.
+		 */
+		Const	   *con = (Const *) node;
+		Const	   *newcon = NULL;
+
+		newcon = makeNode(Const);
+		newcon->consttype = TEXTOID;
+		newcon->consttypmod = -1;
+		newcon->constcollid = typeTypeCollation(textType);
+		newcon->constlen = typeLen(textType);
+		newcon->constbyval = typeByVal(textType);
+		newcon->constisnull = con->constisnull;
+		newcon->location = exprLocation(node);
+
+		if (con->constisnull)
+			newcon->constvalue = (Datum) 0;
+		else
+		{
+			newcon->constvalue = stringTypeDatum(textType,
+												 DatumGetCString(con->constvalue),
+												 -1);
+			/*
+			 * If it's a varlena value, force it to be in non-expanded (non-toasted)
+			 * format; this avoids any possible dependency on external values and
+			 * improves consistency of representation.
+			*/
+			newcon->constvalue =
+				PointerGetDatum(PG_DETOAST_DATUM(newcon->constvalue));
+		}
+		source = (Node *) newcon;
+
+		inputBaseTypeId = TEXTOID;
+	}
+	else
+		source = node;
+
+	pg_proc = table_open(ProcedureRelationId, AccessShareLock);
+
+	ScanKeyInit(&scankey,
+				Anum_pg_proc_proformat,
+				BTEqualStrategyNumber, F_BOOLEQ,
+				BoolGetDatum(true));
+	sscan = systable_beginscan(pg_proc, ProcedureProformatIndexId, true,
+							   SnapshotSelf, 1, &scankey);
+	while (HeapTupleIsValid(tuple = systable_getnext(sscan)))
+	{
+		Form_pg_proc procform = (Form_pg_proc) GETSTRUCT(tuple);
+
+		if (!procform->proformat)
+			continue;
+
+		if (procform->proargtypes.values[0] != inputBaseTypeId)
+			continue;
+
+		if (procform->proargtypes.values[1] != TEXTOID)
+			continue;
+
+		if (procform->prorettype != baseTypeId)
+			continue;
+
+		funcId = procform->oid;
+		break;
+	}
+	systable_endscan(sscan);
+	table_close(pg_proc, AccessShareLock);
+
+	if (!OidIsValid(funcId))
+	{
+		if (inputTypeId == UNKNOWNOID)
+			inputTypeId = TEXTOID;
+
+		ereport(ERROR,
+				errcode(ERRCODE_CANNOT_COERCE),
+				errmsg("cannot use FORMAT template cast type %s to %s",
+						format_type_be(inputTypeId),
+						format_type_be(targetTypeId)),
+				errhint("Formatted type cast function casting %s to %s does not exists",
+						format_type_be(inputTypeId),
+						format_type_be(targetTypeId)),
+				parser_coercion_errposition(pstate, location, node));
+	}
+	if (IsA(node, CollateExpr))
+	{
+		/*
+		 * If we have a COLLATE clause, we have to push the coercion
+		 * underneath the COLLATE; or discard the COLLATE if the target type
+		 * isn't collatable.  This is really ugly, but there is little choice
+		 * because the above hacks on Consts and Params wouldn't happen
+		 * otherwise.  This kluge has consequences in coerce_to_target_type.
+		 */
+		CollateExpr *coll = (CollateExpr *) node;
+
+		result = coerce_type_fmt(pstate, (Node *) coll->arg, format,
+								 inputTypeId, targetTypeId, targetTypeMod,
+								 ccontext, cformat, location);
+		if (type_is_collatable(targetTypeId))
+		{
+			CollateExpr *newcoll = makeNode(CollateExpr);
+
+			newcoll->arg = (Expr *) result;
+			newcoll->collOid = coll->collOid;
+			newcoll->location = coll->location;
+			result = (Node *) newcoll;
+		}
+		return result;
+	}
+
+	args = list_make1(source);
+	args = lappend(args, fmt);
+	fexpr = makeFuncExpr(funcId, targetTypeId, args,
+						 InvalidOid, InvalidOid, cformat);
+	fexpr->location = location;
+	result = (Node *) fexpr;
+
+	/*
+	 * If domain, coerce to the domain type and relabel with domain type ID,
+	 * hiding the previous coercion node.
+	*/
+	if (targetTypeId != baseTypeId)
+		result = coerce_to_domain(result, baseTypeId, baseTypeMod,
+								  targetTypeId,
+								  ccontext, cformat, location,
+								  true);
+
+	ReleaseSysCache(textType);
+
+	return result;
+}
 
 /*
  * can_coerce_type()
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..ef7d103a998 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2706,6 +2706,7 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
 	Node	   *result;
 	Node	   *arg = tc->arg;
 	Node	   *expr;
+	Node	   *format = NULL;
 	Oid			inputType;
 	Oid			targetType;
 	int32		targetTypmod;
@@ -2727,6 +2728,12 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
 		int32		targetBaseTypmod;
 		Oid			elementType;
 
+		if(tc->format)
+			ereport(ERROR,
+					errcode(ERRCODE_CANNOT_COERCE),
+					errmsg("formmatted type cast does not apply to array type");
+					parser_coercion_errposition(pstate, exprLocation(arg), arg));
+
 		/*
 		 * If target is a domain over array, work with the base array type
 		 * here.  Below, we'll cast the array type to the domain.  In the
@@ -2754,6 +2761,9 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
 	if (inputType == InvalidOid)
 		return expr;			/* do nothing if NULL input */
 
+	if(tc->format)
+		format = transformExprRecurse(pstate, tc->format);
+
 	/*
 	 * Location of the coercion is preferentially the location of the :: or
 	 * CAST symbol, but if there is none then use the location of the type
@@ -2763,11 +2773,18 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
 	if (location < 0)
 		location = tc->typeName->location;
 
-	result = coerce_to_target_type(pstate, expr, inputType,
-								   targetType, targetTypmod,
-								   COERCION_EXPLICIT,
-								   COERCE_EXPLICIT_CAST,
-								   location);
+	if (format != NULL)
+		result = coerce_to_target_type_fmt(pstate, expr, format, inputType,
+										   targetType, targetTypmod,
+										   COERCION_EXPLICIT,
+										   COERCE_EXPLICIT_CAST,
+										   location);
+	else
+		result = coerce_to_target_type(pstate, expr, inputType,
+									   targetType, targetTypmod,
+									   COERCION_EXPLICIT,
+									   COERCE_EXPLICIT_CAST,
+									   location);
 	if (result == NULL)
 		ereport(ERROR,
 				(errcode(ERRCODE_CANNOT_COERCE),
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index a414bfd6252..8b31f697fa7 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -682,6 +682,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 		castnode = makeNode(TypeCast);
 		castnode->typeName = SystemTypeName("regclass");
 		castnode->arg = (Node *) snamenode;
+		castnode->format = NULL;
 		castnode->location = -1;
 		funccallnode = makeFuncCall(SystemFuncName("nextval"),
 									list_make1(castnode),
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 98fd300c35a..bde75c45028 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -474,6 +474,8 @@ static bool looks_like_function(Node *node);
 static void get_oper_expr(OpExpr *expr, deparse_context *context);
 static void get_func_expr(FuncExpr *expr, deparse_context *context,
 						  bool showimplicit);
+static bool get_fmt_coercion_expr(FuncExpr *expr, deparse_context *context,
+								  Oid resulttype, int32 resulttypmod);
 static void get_agg_expr(Aggref *aggref, deparse_context *context,
 						 Aggref *original_aggref);
 static void get_agg_expr_helper(Aggref *aggref, deparse_context *context,
@@ -10840,6 +10842,10 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
 		/* Get the typmod if this is a length-coercion function */
 		(void) exprIsLengthCoercion((Node *) expr, &coercedTypmod);
 
+		if (get_fmt_coercion_expr(expr, context,
+								  rettype, coercedTypmod))
+			return;
+
 		get_coercion_expr(arg, context,
 						  rettype, coercedTypmod,
 						  (Node *) expr);
@@ -10896,6 +10902,70 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
 	appendStringInfoChar(buf, ')');
 }
 
+/*
+ * get_fmt_coercion_expr
+ *
+ * Parse back expression: CAST (expr AS type FORMAT 'fmt')
+ */
+static bool
+get_fmt_coercion_expr(FuncExpr *expr, deparse_context *context,
+					  Oid resulttype, int32 resulttypmod)
+
+{
+	Node	   *arg;
+	Const	   *second_arg;
+	FuncExpr 	*func;
+	StringInfo	buf = context->buf;
+
+	func = expr;
+	if (func->funcformat != COERCE_EXPLICIT_CAST)
+		return false;
+
+	if (func->funcvariadic)
+		return false;
+
+	if (list_length(func->args) != 2)
+		return false;
+
+	arg = linitial(func->args);
+	second_arg = (Const *) lsecond(func->args);
+
+	if (exprType(arg) != TEXTOID &&
+		func->funcresulttype != TEXTOID)
+		return false;
+
+	if (!IsA(second_arg, Const) ||
+		second_arg->consttype != TEXTOID ||
+		second_arg->constisnull)
+		return false;
+
+	if (!get_func_retformat(func->funcid))
+		return false;
+
+	appendStringInfoString(buf, "CAST(");
+
+	if (!PRETTY_PAREN(context))
+		appendStringInfoChar(buf, '(');
+	get_rule_expr_paren(arg, context, false, (Node *) func);
+	if (!PRETTY_PAREN(context))
+		appendStringInfoChar(buf, ')');
+
+	/*
+	 * Never emit resulttype(arg) functional notation. A pg_proc entry could
+	 * take precedence, and a resulttype in pg_temp would require schema
+	 * qualification that format_type_with_typemod() would usually omit. We've
+	 * standardized on arg::resulttype, but CAST(arg AS resulttype) notation
+	 * would work fine.
+	 */
+	appendStringInfo(buf, " AS %s FORMAT ",
+					 format_type_with_typemod(resulttype, resulttypmod));
+
+	get_const_expr((Const *) second_arg, context, -1);
+	appendStringInfoChar(buf, ')');
+
+	return true;
+}
+
 /*
  * get_agg_expr			- Parse back an Aggref node
  */
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index c460a72b75d..9ace1ff99e6 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1806,6 +1806,25 @@ get_func_rettype(Oid funcid)
 	return result;
 }
 
+/*
+ * get_func_retformat
+ *		Given procedure id return the function's proformat flag.
+ */
+bool
+get_func_retformat(Oid funcid)
+{
+	HeapTuple	tp;
+	bool		result;
+
+	tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
+	if (!HeapTupleIsValid(tp))
+		elog(ERROR, "cache lookup failed for function %u", funcid);
+
+	result = ((Form_pg_proc) GETSTRUCT(tp))->proformat;
+	ReleaseSysCache(tp);
+	return result;
+}
+
 /*
  * get_func_nargs
  *		Given procedure id, return the number of arguments.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3ee8fed7e53..f57d8747706 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4781,33 +4781,33 @@
 # formatting
 { oid => '1770', descr => 'format timestamp with time zone to text',
   proname => 'to_char', provolatile => 's', prorettype => 'text',
-  proargtypes => 'timestamptz text', prosrc => 'timestamptz_to_char' },
+  proargtypes => 'timestamptz text', prosrc => 'timestamptz_to_char', proformat => 't' },
 { oid => '1772', descr => 'format numeric to text',
   proname => 'to_char', provolatile => 's', prorettype => 'text',
-  proargtypes => 'numeric text', prosrc => 'numeric_to_char' },
+  proargtypes => 'numeric text', prosrc => 'numeric_to_char', proformat => 't' },
 { oid => '1773', descr => 'format int4 to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'int4 text', prosrc => 'int4_to_char' },
 { oid => '1774', descr => 'format int8 to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'int8 text', prosrc => 'int8_to_char' },
 { oid => '1775', descr => 'format float4 to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'float4 text', prosrc => 'float4_to_char' },
 { oid => '1776', descr => 'format float8 to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'float8 text', prosrc => 'float8_to_char' },
 { oid => '1777', descr => 'convert text to numeric',
-  proname => 'to_number', provolatile => 's', prorettype => 'numeric',
+  proname => 'to_number', provolatile => 's', prorettype => 'numeric', proformat => 't',
   proargtypes => 'text text', prosrc => 'numeric_to_number' },
 { oid => '1778', descr => 'convert text to timestamp with time zone',
-  proname => 'to_timestamp', provolatile => 's', prorettype => 'timestamptz',
+  proname => 'to_timestamp', provolatile => 's', prorettype => 'timestamptz', proformat => 't',
   proargtypes => 'text text', prosrc => 'to_timestamp' },
 { oid => '1780', descr => 'convert text to date',
   proname => 'to_date', provolatile => 's', prorettype => 'date',
-  proargtypes => 'text text', prosrc => 'to_date' },
+  proargtypes => 'text text', prosrc => 'to_date', proformat => 't' },
 { oid => '1768', descr => 'format interval to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'interval text', prosrc => 'interval_to_char' },
 
 { oid => '1282', descr => 'quote an identifier for usage in a querystring',
@@ -6433,7 +6433,7 @@
   proname => 'isfinite', prorettype => 'bool', proargtypes => 'timestamp',
   prosrc => 'timestamp_finite' },
 { oid => '2049', descr => 'format timestamp to text',
-  proname => 'to_char', provolatile => 's', prorettype => 'text',
+  proname => 'to_char', provolatile => 's', prorettype => 'text', proformat => 't',
   proargtypes => 'timestamp text', prosrc => 'timestamp_to_char' },
 { oid => '2052',
   proname => 'timestamp_eq', proleakproof => 't', prorettype => 'bool',
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index d7353e7a088..7c0ceb89aa0 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -64,6 +64,9 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce
 	/* is it a leakproof function? */
 	bool		proleakproof BKI_DEFAULT(f);
 
+	/* is it a leakproof function? */
+	bool		proformat BKI_DEFAULT(f);
+
 	/* strict with respect to NULLs? */
 	bool		proisstrict BKI_DEFAULT(t);
 
@@ -139,7 +142,7 @@ DECLARE_TOAST(pg_proc, 2836, 2837);
 
 DECLARE_UNIQUE_INDEX_PKEY(pg_proc_oid_index, 2690, ProcedureOidIndexId, pg_proc, btree(oid oid_ops));
 DECLARE_UNIQUE_INDEX(pg_proc_proname_args_nsp_index, 2691, ProcedureNameArgsNspIndexId, pg_proc, btree(proname name_ops, proargtypes oidvector_ops, pronamespace oid_ops));
-
+DECLARE_INDEX(pg_proc_proformat_index, 2775, ProcedureProformatIndexId, pg_proc, btree(proformat bool_ops));
 MAKE_SYSCACHE(PROCOID, pg_proc_oid_index, 128);
 MAKE_SYSCACHE(PROCNAMEARGSNSP, pg_proc_proname_args_nsp_index, 128);
 
@@ -202,6 +205,7 @@ extern ObjectAddress ProcedureCreate(const char *procedureName,
 									 char prokind,
 									 bool security_definer,
 									 bool isLeakProof,
+									 bool isFormat,
 									 bool isStrict,
 									 char volatility,
 									 char parallel,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..b71c4135ae5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -395,6 +395,7 @@ typedef struct TypeCast
 {
 	NodeTag		type;
 	Node	   *arg;			/* the expression being casted */
+	Node	   *format;			/* the cast format template Const*/
 	TypeName   *typeName;		/* the target type */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } TypeCast;
diff --git a/src/include/parser/parse_coerce.h b/src/include/parser/parse_coerce.h
index 8d775c72c59..282f559c4e1 100644
--- a/src/include/parser/parse_coerce.h
+++ b/src/include/parser/parse_coerce.h
@@ -43,11 +43,19 @@ extern Node *coerce_to_target_type(ParseState *pstate,
 								   CoercionContext ccontext,
 								   CoercionForm cformat,
 								   int location);
+extern Node *coerce_to_target_type_fmt(ParseState *pstate,
+									   Node *expr,Node *format,
+									   Oid exprtype, Oid targettype,
+									   int32 targettypmod, CoercionContext ccontext,
+									   CoercionForm cformat, int location);
 extern bool can_coerce_type(int nargs, const Oid *input_typeids, const Oid *target_typeids,
 							CoercionContext ccontext);
 extern Node *coerce_type(ParseState *pstate, Node *node,
 						 Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
 						 CoercionContext ccontext, CoercionForm cformat, int location);
+Node *coerce_type_fmt(ParseState *pstate, Node *node, Node *format,
+					  Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+					  CoercionContext ccontext, CoercionForm cformat, int location);
 extern Node *coerce_to_domain(Node *arg, Oid baseTypeId, int32 baseTypeMod,
 							  Oid typeId,
 							  CoercionContext ccontext, CoercionForm cformat, int location,
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index fa7c7e0323b..a1e6a5703b0 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -128,6 +128,7 @@ extern RegProcedure get_oprjoin(Oid opno);
 extern char *get_func_name(Oid funcid);
 extern Oid	get_func_namespace(Oid funcid);
 extern Oid	get_func_rettype(Oid funcid);
+extern bool get_func_retformat(Oid funcid);
 extern int	get_func_nargs(Oid funcid);
 extern Oid	get_func_signature(Oid funcid, Oid **argtypes, int *nargs);
 extern Oid	get_func_variadictype(Oid funcid);
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 5ae93d8e8a5..c6b8b65b6dc 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3110,6 +3110,13 @@ SELECT to_timestamp('15 "text between quote marks" 98 54 45',
  Thu Jan 01 15:54:45 1998 PST
 (1 row)
 
+SELECT cast('15 "text between quote marks" 98 54 45' as timestamptz format
+                    E'HH24 "\\"text between quote marks\\"" YY MI SS');
+         timestamptz          
+------------------------------
+ Thu Jan 01 15:54:45 1998 PST
+(1 row)
+
 SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
          to_timestamp         
 ------------------------------
@@ -3341,12 +3348,24 @@ SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ');  -- dyntz
  Sat Dec 17 23:38:00 2011 PST
 (1 row)
 
+SELECT cast('2011-12-18 11:38 MSK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
+         timestamptz          
+------------------------------
+ Sat Dec 17 23:38:00 2011 PST
+(1 row)
+
 SELECT to_timestamp('2011-12-18 00:00 LMT', 'YYYY-MM-DD HH24:MI TZ');  -- dyntz
          to_timestamp         
 ------------------------------
  Sat Dec 17 23:52:58 2011 PST
 (1 row)
 
+SELECT cast('2011-12-18 00:00 LMT' as timestamptz format 'YYYY-MM-DD HH24:MI TZ');  -- dyntz
+         timestamptz          
+------------------------------
+ Sat Dec 17 23:52:58 2011 PST
+(1 row)
+
 SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
          to_timestamp         
 ------------------------------
@@ -3380,9 +3399,15 @@ SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
 SELECT to_timestamp('2011-12-18 11:38 +xyz', 'YYYY-MM-DD HH12:MI OF');  -- error
 ERROR:  invalid value "xy" for "OF"
 DETAIL:  Value must be an integer.
+SELECT cast('2011-12-18 11:38 +xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF');  -- error
+ERROR:  invalid value "xy" for "OF"
+DETAIL:  Value must be an integer.
 SELECT to_timestamp('2011-12-18 11:38 +01:xyz', 'YYYY-MM-DD HH12:MI OF');  -- error
 ERROR:  invalid value "xy" for "OF"
 DETAIL:  Value must be an integer.
+SELECT cast('2011-12-18 11:38 +01:xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF');  -- error
+ERROR:  invalid value "xy" for "OF"
+DETAIL:  Value must be an integer.
 SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');
            to_timestamp           
 ----------------------------------
@@ -3466,6 +3491,27 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF'
  6 | Fri Nov 02 12:34:56.123456 2018 PDT
 (6 rows)
 
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(1) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(2) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(3) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(4) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(5) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(6) format 'YYYY-MM-DD HH24:MI:SS.FF6');
+             timestamptz             
+-------------------------------------
+ Fri Nov 02 12:34:56.1 2018 PDT
+ Fri Nov 02 12:34:56.12 2018 PDT
+ Fri Nov 02 12:34:56.123 2018 PDT
+ Fri Nov 02 12:34:56.1235 2018 PDT
+ Fri Nov 02 12:34:56.12346 2018 PDT
+ Fri Nov 02 12:34:56.123456 2018 PDT
+(6 rows)
+
 SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
 ERROR:  date/time field value out of range: "2018-11-02 12:34:56.123456789"
 SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i;
@@ -3485,18 +3531,36 @@ SELECT to_date('1 4 1902', 'Q MM YYYY');  -- Q is ignored
  04-01-1902
 (1 row)
 
+SELECT cast('1 4 1902' as date format 'Q MM YYYY');  -- Q is ignored
+    date    
+------------
+ 04-01-1902
+(1 row)
+
 SELECT to_date('3 4 21 01', 'W MM CC YY');
   to_date   
 ------------
  04-15-2001
 (1 row)
 
+SELECT cast('3 4 21 01' as date format 'W MM CC YY');
+    date    
+------------
+ 04-15-2001
+(1 row)
+
 SELECT to_date('2458872', 'J');
   to_date   
 ------------
  01-23-2020
 (1 row)
 
+SELECT cast('2458872' as date format 'J');
+    date    
+------------
+ 01-23-2020
+(1 row)
+
 --
 -- Check handling of BC dates
 --
@@ -3832,12 +3896,24 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
  2012-12-12 12:00:00 PST
 (1 row)
 
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+          text           
+-------------------------
+ 2012-12-12 12:00:00 PST
+(1 row)
+
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
          to_char         
 -------------------------
  2012-12-12 12:00:00 pst
 (1 row)
 
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS tz');
+          text           
+-------------------------
+ 2012-12-12 12:00:00 pst
+(1 row)
+
 --
 -- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
 --
@@ -3867,18 +3943,36 @@ SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
  2012-12-12 12:00:00 -01:30
 (1 row)
 
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
+            text            
+----------------------------
+ 2012-12-12 12:00:00 -01:30
+(1 row)
+
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
      to_char      
 ------------------
  2012-12-12 43200
 (1 row)
 
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD SSSS');
+       text       
+------------------
+ 2012-12-12 43200
+(1 row)
+
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');
      to_char      
 ------------------
  2012-12-12 43200
 (1 row)
 
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD SSSSS');
+       text       
+------------------
+ 2012-12-12 43200
+(1 row)
+
 SET TIME ZONE '+2';
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
          to_char         
diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
index 6e816c57f1f..e26f29b23de 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,172 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
 --
 -- rewrite rules
 --
+select cast('1' as text format 1); --error
+ERROR:  FORMAT template is not string type
+LINE 1: select cast('1' as text format 1);
+                                       ^
+select cast('1' as text format '1'::text); --error
+ERROR:  cannot use FORMAT template cast type text to text
+LINE 1: select cast('1' as text format '1'::text);
+               ^
+HINT:  Formatted type cast function casting text to text does not exists
+select cast(array[1] as text format 'YYYY'); --error
+ERROR:  formmatted type cast does not apply to array type
+LINE 1: select cast(array[1] as text format 'YYYY');
+                    ^
+--type check
+select cast('1' as timestamp format 'YYYY-MM-DD'); --error
+ERROR:  cannot use FORMAT template cast type text to timestamp without time zone
+LINE 1: select cast('1' as timestamp format 'YYYY-MM-DD');
+               ^
+HINT:  Formatted type cast function casting text to timestamp without time zone does not exists
+select cast('1' as timestamp[] format 'YYYY-MM-DD'); --error
+ERROR:  cannot use FORMAT template cast type text to timestamp without time zone[]
+LINE 1: select cast('1' as timestamp[] format 'YYYY-MM-DD');
+               ^
+HINT:  Formatted type cast function casting text to timestamp without time zone[] does not exists
+select cast('1' as bool format 'YYYY-MM-DD'); --error
+ERROR:  cannot use FORMAT template cast type text to boolean
+LINE 1: select cast('1' as bool format 'YYYY-MM-DD');
+               ^
+HINT:  Formatted type cast function casting text to boolean does not exists
+select cast('1' as json format 'YYYY-MM-DD'); --error
+ERROR:  cannot use FORMAT template cast type text to json
+LINE 1: select cast('1' as json format 'YYYY-MM-DD');
+               ^
+HINT:  Formatted type cast function casting text to json does not exists
+select cast('1'::json as text format 'YYYY-MM-DD'); --error
+ERROR:  cannot use FORMAT template cast type json to text
+LINE 1: select cast('1'::json as text format 'YYYY-MM-DD');
+               ^
+HINT:  Formatted type cast function casting json to text does not exists
+--domain check
+create domain d1 as date check (value <> '0001-01-01');
+select cast('1' as d1 format 'YYYY-MM-DD'); --error
+ERROR:  value for domain d1 violates check constraint "d1_check"
+select cast('1' as d1 format 'MM-DD'); --ok
+      d1       
+---------------
+ 01-01-0001 BC
+(1 row)
+
+select cast('1' as date); --error
+ERROR:  invalid input syntax for type date: "1"
+LINE 1: select cast('1' as date);
+                    ^
+select cast('1' as date format 'YYYY-MM-DD');
+    date    
+------------
+ 01-01-0001
+(1 row)
+
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD') as expect_true;
+ expect_true 
+-------------
+ t
+(1 row)
+
+select cast('2012-13-12' as date format 'YYYY-MM-DD'); --error
+ERROR:  date/time field value out of range: "2012-13-12"
+create table tcast(col1 text, col2 text, col3 date, col4 timestamptz);
+insert into tcast(col1, col2) values('2022-12-13', 'YYYY-MM-DD'), ('2022-12-01', 'YYYY-DD-MM');
+select cast(col1 as date format col2) from tcast;
+    col1    
+------------
+ 12-13-2022
+ 01-12-2022
+(2 rows)
+
+select cast(col1 as date format col3) from tcast; --error
+ERROR:  FORMAT template is not string type
+LINE 1: select cast(col1 as date format col3) from tcast;
+                                        ^
+select cast(col1 as date format col3::text) from tcast; --ok
+ col1 
+------
+ 
+ 
+(2 rows)
+
+CREATE FUNCTION volatile_const() RETURNS TEXT AS $$ BEGIN RETURN 'YYYY-MM-DD'; END; $$ LANGUAGE plpgsql VOLATILE;
+CREATE FUNCTION stable_const() RETURNS TEXT AS $$ BEGIN RETURN 'YYYY-MM-DD'; END; $$ LANGUAGE plpgsql STABLE;
+select cast(col1 as date format volatile_const()) from tcast;
+    col1    
+------------
+ 12-13-2022
+ 12-01-2022
+(2 rows)
+
+select cast(col1 as date format stable_const()) from tcast;
+    col1    
+------------
+ 12-13-2022
+ 12-01-2022
+(2 rows)
+
+create index s1 on tcast(cast(col1 as date format 'YYYY-MM-DD')); --error
+ERROR:  functions in index expression must be marked IMMUTABLE
+create view tcast_v1 as select cast(col1 as date format 'YYYY-MM-DD') from tcast;
+select pg_get_viewdef('tcast_v1', false);
+                      pg_get_viewdef                      
+----------------------------------------------------------
+  SELECT CAST((col1) AS date FORMAT 'YYYY-MM-DD') AS col1+
+    FROM tcast;
+(1 row)
+
+select pg_get_viewdef('tcast_v1', true);
+                     pg_get_viewdef                     
+--------------------------------------------------------
+  SELECT CAST(col1 AS date FORMAT 'YYYY-MM-DD') AS col1+
+    FROM tcast;
+(1 row)
+
+select cast('2012-13-12' as date format 'YYYY-DD-MM') is not null as expect_true;
+ expect_true 
+-------------
+ t
+(1 row)
+
+--null value check
+select cast(NULL as date format 'YYYY-MM-DD');
+ date 
+------
+ 
+(1 row)
+
+select cast(NULL as numeric format 'YYYY-MM-DD');
+ numeric 
+---------
+        
+(1 row)
+
+select cast(NULL as timestamptz format 'YYYY-MM-DD');
+ timestamptz 
+-------------
+ 
+(1 row)
+
+select cast(NULL::interval AS TEXT format 'YYYY-MM-DD');
+ text 
+------
+ 
+(1 row)
+
+select cast(NULL::timestamp AS TEXT format 'YYYY-MM-DD');
+ text 
+------
+ 
+(1 row)
+
+select cast(NULL::timestamptz AS TEXT format 'YYYY-MM-DD');
+ text 
+------
+ 
+(1 row)
+
+select cast(NULL::numeric AS TEXT format 'YYYY-MM-DD');
+ text 
+------
+ 
+(1 row)
+
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out
index c58e232a263..b48fe4f3037 100644
--- a/src/test/regress/expected/numeric.out
+++ b/src/test/regress/expected/numeric.out
@@ -2270,6 +2270,12 @@ SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
  -34338492.654878
 (1 row)
 
+SELECT cast('-34,338,492.654,878' as numeric format '99G999G999D999G999');
+     numeric      
+------------------
+ -34338492.654878
+(1 row)
+
 SELECT to_number('<564646.654564>', '999999.999999PR');
    to_number    
 ----------------
@@ -2300,6 +2306,12 @@ SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
  544448.78
 (1 row)
 
+SELECT cast('5 4 4 4 4 8 . 7 8' as numeric format'9 9 9 9 9 9 . 9 9');
+  numeric  
+-----------
+ 544448.78
+(1 row)
+
 SELECT to_number('.01', 'FM9.99');
  to_number 
 -----------
@@ -2372,6 +2384,12 @@ SELECT to_number('$1,234.56','L99,999.99');
    1234.56
 (1 row)
 
+SELECT cast('$1,234.56' as numeric format 'L99,999.99');
+ numeric 
+---------
+ 1234.56
+(1 row)
+
 SELECT to_number('1234.56','L99,999.99');
  to_number 
 -----------
@@ -2390,21 +2408,34 @@ SELECT to_number('42nd', '99th');
         42
 (1 row)
 
+SELECT cast('42nd' as numeric format '99th');
+ numeric 
+---------
+      42
+(1 row)
+
 SELECT to_number('123456', '99999V99');
         to_number        
 -------------------------
  1234.560000000000000000
 (1 row)
 
+SELECT cast('123456' as numeric format '99999V99');
+         numeric         
+-------------------------
+ 1234.560000000000000000
+(1 row)
+
 -- Test for correct conversion between numbers and Roman numerals
 WITH rows AS
   (SELECT i, to_char(i, 'RN') AS roman FROM generate_series(1, 3999) AS i)
 SELECT
-  bool_and(to_number(roman, 'RN') = i) as valid
+  bool_and(to_number(roman, 'RN') = i) as valid,
+  bool_and(cast(roman as numeric format 'RN') = i) as valid
 FROM rows;
- valid 
--------
- t
+ valid | valid 
+-------+-------
+ t     | t
 (1 row)
 
 -- Some additional tests for RN input
@@ -2414,6 +2445,12 @@ SELECT to_number('CvIiI', 'rn');
        108
 (1 row)
 
+SELECT cast('CvIiI' as numeric format 'rn');
+ numeric 
+---------
+     108
+(1 row)
+
 SELECT to_number('MMXX  ', 'RN');
  to_number 
 -----------
@@ -2441,8 +2478,12 @@ SELECT to_number('M CC', 'RN');
 -- error cases
 SELECT to_number('viv', 'RN');
 ERROR:  invalid Roman numeral
+SELECT cast('viv' as numeric format 'RN');
+ERROR:  invalid Roman numeral
 SELECT to_number('DCCCD', 'RN');
 ERROR:  invalid Roman numeral
+SELECT cast('DCCCD' as numeric format 'RN');
+ERROR:  invalid Roman numeral
 SELECT to_number('XIXL', 'RN');
 ERROR:  invalid Roman numeral
 SELECT to_number('MCCM', 'RN');
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 8978249a5dc..9fb50016c79 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -476,6 +476,9 @@ SELECT to_timestamp('1,582nd VIII 21', 'Y,YYYth FMRM DD');
 SELECT to_timestamp('15 "text between quote marks" 98 54 45',
                     E'HH24 "\\"text between quote marks\\"" YY MI SS');
 
+SELECT cast('15 "text between quote marks" 98 54 45' as timestamptz format
+                    E'HH24 "\\"text between quote marks\\"" YY MI SS');
+
 SELECT to_timestamp('05121445482000', 'MMDDHH24MISSYYYY');
 
 SELECT to_timestamp('2000January09Sunday', 'YYYYFMMonthDDFMDay');
@@ -542,7 +545,9 @@ SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ');
 SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ');
 SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ');
 SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ');  -- dyntz
+SELECT cast('2011-12-18 11:38 MSK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ');
 SELECT to_timestamp('2011-12-18 00:00 LMT', 'YYYY-MM-DD HH24:MI TZ');  -- dyntz
+SELECT cast('2011-12-18 00:00 LMT' as timestamptz format 'YYYY-MM-DD HH24:MI TZ');  -- dyntz
 SELECT to_timestamp('2011-12-18 11:38ESTFOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
 SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS');
 SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ');  -- error
@@ -551,7 +556,9 @@ SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ');  -- error
 SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF');
 SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF');
 SELECT to_timestamp('2011-12-18 11:38 +xyz', 'YYYY-MM-DD HH12:MI OF');  -- error
+SELECT cast('2011-12-18 11:38 +xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF');  -- error
 SELECT to_timestamp('2011-12-18 11:38 +01:xyz', 'YYYY-MM-DD HH12:MI OF');  -- error
+SELECT cast('2011-12-18 11:38 +01:xyz' as timestamptz format 'YYYY-MM-DD HH12:MI OF');  -- error
 
 SELECT to_timestamp('2018-11-02 12:34:56.025', 'YYYY-MM-DD HH24:MI:SS.MS');
 
@@ -562,12 +569,26 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123', 'YYYY-MM-DD HH24:MI:SS.FF' ||
 SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
 SELECT i, to_timestamp('2018-11-02 12:34:56.12345', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
 SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(1) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(2) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(3) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(4) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(5) format 'YYYY-MM-DD HH24:MI:SS.FF6')
+UNION ALL
+SELECT cast('2018-11-02 12:34:56.123456' as timestamptz(6) format 'YYYY-MM-DD HH24:MI:SS.FF6');
 SELECT i, to_timestamp('2018-11-02 12:34:56.123456789', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i;
 SELECT i, to_timestamp('20181102123456123456', 'YYYYMMDDHH24MISSFF' || i) FROM generate_series(1, 6) i;
 
 SELECT to_date('1 4 1902', 'Q MM YYYY');  -- Q is ignored
+SELECT cast('1 4 1902' as date format 'Q MM YYYY');  -- Q is ignored
 SELECT to_date('3 4 21 01', 'W MM CC YY');
+SELECT cast('3 4 21 01' as date format 'W MM CC YY');
 SELECT to_date('2458872', 'J');
+SELECT cast('2458872' as date format 'J');
 
 --
 -- Check handling of BC dates
@@ -677,7 +698,9 @@ SELECT to_date('2147483647 01', 'CC YY');
 
 -- to_char's TZ format code produces zone abbrev if known
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS tz');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS tz');
 
 --
 -- Check behavior with SQL-style fixed-GMT-offset time zone (cf bug #8572)
@@ -692,8 +715,11 @@ SELECT '2012-12-12 12:00'::timestamptz;
 SELECT '2012-12-12 12:00 America/New_York'::timestamptz;
 
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD HH:MI:SS TZ');
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD SSSS');
 SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS');
+SELECT cast('2012-12-12 12:00'::timestamptz as text format 'YYYY-MM-DD SSSSS');
 
 SET TIME ZONE '+2';
 
diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql
index 165a2e175fb..8d6bdea7654 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -273,3 +273,50 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
 --
 -- rewrite rules
 --
+
+select cast('1' as text format 1); --error
+select cast('1' as text format '1'::text); --error
+select cast(array[1] as text format 'YYYY'); --error
+
+--type check
+select cast('1' as timestamp format 'YYYY-MM-DD'); --error
+select cast('1' as timestamp[] format 'YYYY-MM-DD'); --error
+select cast('1' as bool format 'YYYY-MM-DD'); --error
+select cast('1' as json format 'YYYY-MM-DD'); --error
+select cast('1'::json as text format 'YYYY-MM-DD'); --error
+
+--domain check
+create domain d1 as date check (value <> '0001-01-01');
+select cast('1' as d1 format 'YYYY-MM-DD'); --error
+select cast('1' as d1 format 'MM-DD'); --ok
+
+select cast('1' as date); --error
+select cast('1' as date format 'YYYY-MM-DD');
+select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD') as expect_true;
+select cast('2012-13-12' as date format 'YYYY-MM-DD'); --error
+
+create table tcast(col1 text, col2 text, col3 date, col4 timestamptz);
+insert into tcast(col1, col2) values('2022-12-13', 'YYYY-MM-DD'), ('2022-12-01', 'YYYY-DD-MM');
+select cast(col1 as date format col2) from tcast;
+select cast(col1 as date format col3) from tcast; --error
+select cast(col1 as date format col3::text) from tcast; --ok
+
+CREATE FUNCTION volatile_const() RETURNS TEXT AS $$ BEGIN RETURN 'YYYY-MM-DD'; END; $$ LANGUAGE plpgsql VOLATILE;
+CREATE FUNCTION stable_const() RETURNS TEXT AS $$ BEGIN RETURN 'YYYY-MM-DD'; END; $$ LANGUAGE plpgsql STABLE;
+select cast(col1 as date format volatile_const()) from tcast;
+select cast(col1 as date format stable_const()) from tcast;
+
+create index s1 on tcast(cast(col1 as date format 'YYYY-MM-DD')); --error
+create view tcast_v1 as select cast(col1 as date format 'YYYY-MM-DD') from tcast;
+select pg_get_viewdef('tcast_v1', false);
+select pg_get_viewdef('tcast_v1', true);
+select cast('2012-13-12' as date format 'YYYY-DD-MM') is not null as expect_true;
+
+--null value check
+select cast(NULL as date format 'YYYY-MM-DD');
+select cast(NULL as numeric format 'YYYY-MM-DD');
+select cast(NULL as timestamptz format 'YYYY-MM-DD');
+select cast(NULL::interval AS TEXT format 'YYYY-MM-DD');
+select cast(NULL::timestamp AS TEXT format 'YYYY-MM-DD');
+select cast(NULL::timestamptz AS TEXT format 'YYYY-MM-DD');
+select cast(NULL::numeric AS TEXT format 'YYYY-MM-DD');
diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql
index 640c6d92f4c..1092317815b 100644
--- a/src/test/regress/sql/numeric.sql
+++ b/src/test/regress/sql/numeric.sql
@@ -1066,11 +1066,13 @@ SELECT to_char('100'::numeric, 'f"ool\\"999');
 SET lc_numeric = 'C';
 SELECT to_number('-34,338,492', '99G999G999');
 SELECT to_number('-34,338,492.654,878', '99G999G999D999G999');
+SELECT cast('-34,338,492.654,878' as numeric format '99G999G999D999G999');
 SELECT to_number('<564646.654564>', '999999.999999PR');
 SELECT to_number('0.00001-', '9.999999S');
 SELECT to_number('5.01-', 'FM9.999999S');
 SELECT to_number('5.01-', 'FM9.999999MI');
 SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9');
+SELECT cast('5 4 4 4 4 8 . 7 8' as numeric format'9 9 9 9 9 9 . 9 9');
 SELECT to_number('.01', 'FM9.99');
 SELECT to_number('.0', '99999999.99999999');
 SELECT to_number('0', '99.99');
@@ -1083,27 +1085,34 @@ SELECT to_number('123456','999G999');
 SELECT to_number('$1234.56','L9,999.99');
 SELECT to_number('$1234.56','L99,999.99');
 SELECT to_number('$1,234.56','L99,999.99');
+SELECT cast('$1,234.56' as numeric format 'L99,999.99');
 SELECT to_number('1234.56','L99,999.99');
 SELECT to_number('1,234.56','L99,999.99');
 SELECT to_number('42nd', '99th');
+SELECT cast('42nd' as numeric format '99th');
 SELECT to_number('123456', '99999V99');
+SELECT cast('123456' as numeric format '99999V99');
 
 -- Test for correct conversion between numbers and Roman numerals
 WITH rows AS
   (SELECT i, to_char(i, 'RN') AS roman FROM generate_series(1, 3999) AS i)
 SELECT
-  bool_and(to_number(roman, 'RN') = i) as valid
+  bool_and(to_number(roman, 'RN') = i) as valid,
+  bool_and(cast(roman as numeric format 'RN') = i) as valid
 FROM rows;
 
 -- Some additional tests for RN input
 SELECT to_number('CvIiI', 'rn');
+SELECT cast('CvIiI' as numeric format 'rn');
 SELECT to_number('MMXX  ', 'RN');
 SELECT to_number('  XIV', '  RN');
 SELECT to_number('  XIV  ', '  RN');
 SELECT to_number('M CC', 'RN');
 -- error cases
 SELECT to_number('viv', 'RN');
+SELECT cast('viv' as numeric format 'RN');
 SELECT to_number('DCCCD', 'RN');
+SELECT cast('DCCCD' as numeric format 'RN');
 SELECT to_number('XIXL', 'RN');
 SELECT to_number('MCCM', 'RN');
 SELECT to_number('MMMM', 'RN');
-- 
2.34.1