On Mon, Jul 28, 2025 at 2:31 AM Vik Fearing <v...@postgresfriends.org> wrote:
>
>
> On 27/07/2025 17:43, jian he wrote:
> > 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.
>
>
> Why does it have to be an A_const? Shouldn't any a_expr work there?
>
you are right. a_expr should work.
the attached patch changed accordingly.
so now
select cast(NULL as date format NULL::date); ---error
select cast(NULL as date format lower('a')); --no error, returns NULL
>
> > so the attached patch is to implement
> > CAST <left paren>
> > <cast operand> AS <cast target>
> > [ FORMAT <cast template> ]
> > <right paren>
>
> This is correct syntax. Thanks for working on it!
>
>
> This doesn't seem very postgres-y to me. Wouldn't it be better to add
> something like castformatfuncid to pg_cast? That way any types that
> have that would just call that. It would allow extensions to add
> formatted casting to their types, for example.
>
select oid, castsource::regtype, casttarget::regtype,
castfunc::regproc, castcontext, castmethod
from pg_cast
where casttarget::regtype::text in ('text') or
castsource::regtype::text in ('text');
As you can see from the query output, cast from other type to text or
cast from text to other type is not in the pg_cast catalog entry.
there are in type input/output functions. it will be represented as a
CoerceViaIO node.
see function find_coercion_pathway (src/backend/parser/parse_coerce.c
line:3577).
adding these pg_cast entries seems tricky.
for example:
(assume castsource as numeric, casttarget as text)
will
(castsource as numeric, casttarget as text, castfunc as numeric_out,
castformatfunc as numeric_to_char)
ever work?
but numeric_out' result type is cstring.
so I tend to think adding castformatfunc to pg_cast will not work.
From bd4ae95df52319fd2bb50f653cc1ed49884e2ce7 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 28 Jul 2025 16:19:32 +0800
Subject: [PATCH v2 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_p6uuyes...@mail.gmail.com
---
src/backend/nodes/nodeFuncs.c | 2 +
src/backend/parser/gram.y | 17 ++
src/backend/parser/parse_coerce.c | 318 +++++++++++++++++++++++++
src/backend/parser/parse_expr.c | 27 ++-
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 | 166 +++++++++++++
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 +-
14 files changed, 828 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..8918adeae00 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,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 +18836,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..ba171013ee7 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,264 @@ 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;
+ Node *fmt;
+ Node *source = 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;
+ }
+
+ 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 (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));
+
+ 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;
+ }
+ else
+ source = node;
+
+ 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;
+ }
+
+ 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()
* 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..f4a3b0e1219 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..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..673205e6aac 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,169 @@ 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: unrecognized type: 705
+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); --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
