hi.
while working on CAST(... DEFAULT ON ERROR), I came across link[1]. I don't
have access to the SQL standard, but based on the information in link[1], for
CAST(val AS type FORMAT 'template'), I make the <cast template> as an A_Const
node in gram.y.
so the attached patch is to implement
CAST <left paren>
<cast operand> AS <cast target>
[ FORMAT <cast template> ]
<right paren>
The implementation is pretty straightforward.
CAST(val AS type FORMAT 'template')
internally, it will be transformed into a FuncExpr node whose funcid
corresponds to
function name as one of (to_number, to_date, to_timestamp, to_char).
template as a Const node will make life easier.
select proname, prosrc, proallargtypes, proargtypes,
prorettype::regtype, proargnames
from pg_proc
where proname in ('to_number', 'to_date', 'to_timestamp', 'to_char');
based on the query results, only a limited set of type casts are supported with
formatted casts. so error out early if the source or target type doesn't meet
these conditions. for example, if the source or target is a composite, array,
or polymorphic type.
demo:
select cast('2018-13-12' as date format 'YYYY-MM-DD'); --error
select cast('2018-13-12' as date format 'YYYY-DD-MM'); --no error
select to_char(cast('2018-13-12' as date format 'YYYY-DD-MM'), 'YYYY-Mon-DD');
returns
2018-Dec-13
[1]:
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Major_features_simply_not_implemented_yet
From 8ddb3727f6292d47cdd42e657499fbaabf77f55f Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sun, 27 Jul 2025 23:39:25 +0800
Subject: [PATCH v1 1/1] CAST(val AS type FORMAT 'template')
context: https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Major_features_simply_not_implemented_yet
---
src/backend/nodes/nodeFuncs.c | 2 +
src/backend/parser/gram.y | 26 +++
src/backend/parser/parse_coerce.c | 306 +++++++++++++++++++++++++
src/backend/parser/parse_expr.c | 31 ++-
src/backend/parser/parse_utilcmd.c | 1 +
src/backend/utils/adt/ruleutils.c | 71 ++++++
src/include/nodes/parsenodes.h | 1 +
src/include/parser/parse_coerce.h | 8 +
src/test/regress/expected/horology.out | 94 ++++++++
src/test/regress/expected/misc.out | 131 +++++++++++
src/test/regress/expected/numeric.out | 49 +++-
src/test/regress/sql/horology.sql | 26 +++
src/test/regress/sql/misc.sql | 36 +++
src/test/regress/sql/numeric.sql | 11 +-
14 files changed, 783 insertions(+), 10 deletions(-)
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..42962be3845 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);
@@ -15945,6 +15947,17 @@ 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);
+ if (!IsA($7, A_Const) ||
+ castNode(A_Const, $7)->val.node.type != T_String)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("only string constants are supported in CAST FORMAT template specification"),
+ parser_errposition(@7));
+ }
+
| EXTRACT '(' extract_list ')'
{
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
@@ -18832,12 +18845,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..979539bfc33 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -130,6 +130,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()
@@ -546,6 +606,252 @@ coerce_type(ParseState *pstate, Node *node,
}
+static Oid
+get_fmt_function(Oid targetTypeId)
+{
+ Oid funcId = InvalidOid;
+
+ switch (targetTypeId)
+ {
+ case INT4OID:
+ funcId = fmgr_internal_function("int4_to_char");
+ break;
+ case INT8OID:
+ funcId = fmgr_internal_function("int8_to_char");
+ break;
+ case NUMERICOID:
+ funcId = fmgr_internal_function("numeric_to_char");
+ break;
+ case FLOAT4OID:
+ funcId = fmgr_internal_function("float4_to_char");
+ break;
+ case FLOAT8OID:
+ funcId = fmgr_internal_function("float8_to_char");
+ break;
+ case TIMESTAMPOID:
+ funcId = fmgr_internal_function("timestamp_to_char");
+ break;
+ case TIMESTAMPTZOID:
+ funcId = fmgr_internal_function("timestamptz_to_char");
+ break;
+ case INTERVALOID:
+ funcId = fmgr_internal_function("interval_to_char");
+ break;
+ default:
+ elog(ERROR, "unrecognized type: %d", (int) targetTypeId);
+ break;
+ }
+ return funcId;
+}
+Node *
+coerce_type_fmt(ParseState *pstate, Node *node, Node *format,
+ Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,
+ CoercionContext ccontext, CoercionForm cformat, int location)
+{
+ Node *result;
+ Const *newcon = NULL;
+ Const *fmtcon = NULL;
+ Oid funcId;
+ Oid baseTypeId;
+ int32 baseTypeMod;
+ Oid inputBaseTypeId;
+ char t_typcategory;
+ char s_typcategory;
+ FuncExpr *fexpr;
+ Type textType;
+ List *args;
+
+ baseTypeMod = targetTypeMod;
+ baseTypeId = getBaseTypeAndTypmod(targetTypeId, &baseTypeMod);
+ inputBaseTypeId = getBaseType(inputTypeId);
+
+ s_typcategory = TypeCategory(inputBaseTypeId);
+ t_typcategory = TypeCategory(baseTypeId);
+
+ if (targetTypeId == inputTypeId ||
+ node == NULL)
+ {
+ /* no conversion needed */
+ return node;
+ }
+
+ if (baseTypeId != NUMERICOID &&
+ baseTypeId != TIMESTAMPTZOID &&
+ baseTypeId != DATEOID &&
+ t_typcategory != TYPCATEGORY_STRING)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)),
+ errhint("Formatted type cast target type can only be timestamptz, text, numeric or date");
+ parser_coercion_errposition(pstate, location, node));
+ }
+
+ if (inputBaseTypeId != INT4OID &&
+ inputBaseTypeId != INT8OID &&
+ inputBaseTypeId != NUMERICOID &&
+ inputBaseTypeId != FLOAT4OID &&
+ inputBaseTypeId != FLOAT8OID &&
+ inputBaseTypeId != TIMESTAMPOID &&
+ inputBaseTypeId != TIMESTAMPTZOID &&
+ inputBaseTypeId != INTERVALOID &&
+ inputBaseTypeId != UNKNOWNOID &&
+ s_typcategory != TYPCATEGORY_STRING)
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast type %s to %s",
+ format_type_be(inputTypeId),
+ format_type_be(targetTypeId)),
+ errhint("Formatted type cast source type must be catgeory of numeric, string, datetime, or timespan");
+ parser_coercion_errposition(pstate, location, node));
+ }
+
+
+ if (baseTypeId == NUMERICOID)
+ funcId = fmgr_internal_function("numeric_to_number");
+ else if (baseTypeId == TIMESTAMPTZOID)
+ funcId = fmgr_internal_function("to_timestamp");
+ else if (baseTypeId == DATEOID)
+ funcId = fmgr_internal_function("to_date");
+ else
+ funcId = get_fmt_function(inputBaseTypeId); /* to_char variant */
+
+ Assert(OidIsValid(funcId));
+
+ textType = typeidType(TEXTOID);
+ if (inputTypeId == UNKNOWNOID && IsA(node, Const))
+ {
+ /*
+ * We assume here that UNKNOWN's internal representation is the same as
+ * CSTRING.
+ */
+ Const *con = (Const *) node;
+
+ 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));
+ }
+ }
+
+ Assert(IsA(format, Const));
+ if (exprType(format) == UNKNOWNOID)
+ {
+ Const *con = (Const *) format;
+ 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));
+ }
+ else
+ {
+ Assert(exprType(format) == TEXTOID);
+ fmtcon = (Const *) copyObject(format);
+ }
+
+ if (IsA(node, Param) &&
+ pstate != NULL && pstate->p_coerce_param_hook != NULL)
+ {
+ /*
+ * Allow the CoerceParamHook to decide what happens. It can return a
+ * transformed node (very possibly the same Param node), or return
+ * NULL to indicate we should proceed with normal coercion.
+ */
+ result = pstate->p_coerce_param_hook(pstate,
+ (Param *) node,
+ targetTypeId,
+ targetTypeMod,
+ location);
+ if (result)
+ return result;
+ }
+
+ 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;
+ }
+
+ if(newcon != NULL)
+ args = list_make1(newcon);
+ else
+ args = list_make1(node);
+ args = lappend(args, fmtcon);
+
+ 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()
* Can input_typeids be coerced to target_typeids?
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..f9694ad48e5 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,13 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
if (inputType == InvalidOid)
return expr; /* do nothing if NULL input */
+ if(tc->format)
+ {
+ format = transformExprRecurse(pstate, tc->format);
+ Assert(IsA(format, Const));
+ Assert(!((Const *) format)->constisnull);
+ }
+
/*
* 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 +2777,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..4fc79385c7c 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,71 @@ 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;
+ char *funcname;
+ Oid procnspid;
+ StringInfo buf = context->buf;
+
+ func = expr;
+ if (func->funcformat != COERCE_EXPLICIT_CAST)
+ return false;
+
+ if (list_length(func->args) != 2)
+ return false;
+
+ arg = linitial(func->args);
+ second_arg = (Const *) lsecond(func->args);
+
+ if (!IsA(second_arg, Const) ||
+ second_arg->consttype != TEXTOID ||
+ second_arg->constisnull)
+ return false;
+
+ procnspid = get_func_namespace(func->funcid);
+ if (!IsCatalogNamespace(procnspid))
+ return false;
+
+ funcname = get_func_name(func->funcid);
+ if (strcmp(funcname, "to_char") && strcmp(funcname, "to_date") &&
+ strcmp(funcname, "to_number") && strcmp(funcname, "to_timestamp"))
+ 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/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/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..17a161deb94 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,134 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+select cast('1' as text format 1); --error
+ERROR: only string constants are supported in CAST FORMAT template specification
+LINE 1: select cast('1' as text format 1);
+ ^
+select cast('1' as text format '1'::text); --error
+ERROR: only string constants are supported in CAST FORMAT template specification
+LINE 1: select cast('1' as text format '1'::text);
+ ^
+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 cast type unknown to timestamp without time zone
+LINE 1: select cast('1' as timestamp format 'YYYY-MM-DD');
+ ^
+HINT: Formatted type cast target type can only be timestamptz, text, numeric or date
+select cast('1' as timestamp[] format 'YYYY-MM-DD'); --error
+ERROR: cannot cast type unknown to timestamp without time zone[]
+LINE 1: select cast('1' as timestamp[] format 'YYYY-MM-DD');
+ ^
+HINT: Formatted type cast target type can only be timestamptz, text, numeric or date
+select cast('1' as bool format 'YYYY-MM-DD'); --error
+ERROR: cannot cast type unknown to boolean
+LINE 1: select cast('1' as bool format 'YYYY-MM-DD');
+ ^
+HINT: Formatted type cast target type can only be timestamptz, text, numeric or date
+select cast('1' as json format 'YYYY-MM-DD'); --error
+ERROR: cannot cast type unknown to json
+LINE 1: select cast('1' as json format 'YYYY-MM-DD');
+ ^
+HINT: Formatted type cast target type can only be timestamptz, text, numeric or date
+select cast('1'::json as text format 'YYYY-MM-DD'); --error
+ERROR: cannot cast type json to text
+LINE 1: select cast('1'::json as text format 'YYYY-MM-DD');
+ ^
+HINT: Formatted type cast source type must be catgeory of numeric, string, datetime, or timespan
+--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 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'); --ok
+ERROR: date/time field value out of range: "2012-13-12"
+create table tcast(a text);
+create index s1 on tcast(cast(a as date format 'YYYY-MM-DD')); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+create index s1 on tcast(to_date(a, 'YYYY-MM-DD')); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+create view tcast_v1 as select cast(a as date format 'YYYY-MM-DD') from tcast;
+select pg_get_viewdef('tcast_v1', false);
+ pg_get_viewdef
+----------------------------------------------------
+ SELECT CAST((a) AS date FORMAT 'YYYY-MM-DD') AS a+
+ FROM tcast;
+(1 row)
+
+select pg_get_viewdef('tcast_v1', true);
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT CAST(a AS date FORMAT 'YYYY-MM-DD') AS a+
+ 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..8d9db74173c 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -273,3 +273,39 @@ 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 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'); --ok
+create table tcast(a text);
+create index s1 on tcast(cast(a as date format 'YYYY-MM-DD')); --error
+create index s1 on tcast(to_date(a, 'YYYY-MM-DD')); --error
+create view tcast_v1 as select cast(a 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
