hi. Please check the attached new version.
I’ve integrated the CAST FORMAT logic right into coerce_to_target_type and coerce_type, see static function coerce_type_with_format. in coerce_type_with_format, we first first do source type, target type, format expression check, validation, if everything is ok then, construct a FuncCall node and let ParseFuncOrColumn do all the remaining job, with that now overall the patch looks more neat. I have tried to transform SELECT CAST(NULL::text as time FORMAT NULL); into SELECT CAST( NULL::text AS timestamp with time zone FORMAT NULL::text)::time; Then later realized it will not work, so CAST FORMAT can only be applied to the result type of the following formatting functions: to_char, to_number, to_date, or to_timestamp. The tests are extensive. I put them right next to the to_char, to_number, to_date, and to_timestamp tests so it's super easy to compare the results with CAST FORMAT. -- jian https://www.enterprisedb.com/
From b50d26302019bd1b09056e500b6a32c1a854b043 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 29 Jan 2026 15:00:39 +0800 Subject: [PATCH v5 1/1] CAST(expr AS type FORMAT 'template') This enables the CAST(expression AS type FORMAT template) syntax. For Binary-coercible casts: Specifying a format template for binary-coercible casts (e.g., text to text) will now raise an error. No pg_cast entries have been modified at this stage. Adding these formatting functions to pg_cast has complex implications requiring further discussion (see [1]) and is not strictly necessary to achieve the current behavior. Under the hood, CAST FORMAT is transformed into a FuncExpr node. Since only function to_char, to_date, to_number, and to_timestamp support formatting, this feature is currently limited to the input and result types compatible with these functions. [1]: https://postgr.es/m/CACJufxF4OW=x2rcwa+zmcgopdwgkdxha09qtftpcj3qstg6...@mail.gmail.com 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 commitfest: https://commitfest.postgresql.org/patch/5957 --- contrib/citext/expected/citext.out | 63 +++++ contrib/citext/expected/citext_1.out | 63 +++++ contrib/citext/sql/citext.sql | 21 ++ src/backend/nodes/nodeFuncs.c | 2 + src/backend/parser/gram.y | 19 ++ src/backend/parser/parse_coerce.c | 241 +++++++++++++++++- src/backend/parser/parse_expr.c | 21 +- src/backend/parser/parse_utilcmd.c | 1 + src/backend/utils/adt/ruleutils.c | 19 ++ src/include/nodes/parsenodes.h | 1 + src/include/parser/parse_coerce.h | 11 + .../regress/expected/collate.linux.utf8.out | 33 +++ src/test/regress/expected/horology.out | 129 ++++++++++ src/test/regress/expected/interval.out | 12 + src/test/regress/expected/misc.out | 216 ++++++++++++++++ src/test/regress/expected/numeric.out | 147 ++++++++++- src/test/regress/sql/collate.linux.utf8.sql | 7 + src/test/regress/sql/horology.sql | 42 +++ src/test/regress/sql/interval.sql | 2 + src/test/regress/sql/misc.sql | 80 ++++++ src/test/regress/sql/numeric.sql | 26 +- 21 files changed, 1143 insertions(+), 13 deletions(-) diff --git a/contrib/citext/expected/citext.out b/contrib/citext/expected/citext.out index 8c0bf54f0f3..7a58ff343ce 100644 --- a/contrib/citext/expected/citext.out +++ b/contrib/citext/expected/citext.out @@ -2210,6 +2210,27 @@ SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext) t (1 row) +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY') + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000' as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext) = to_number('12,454.8-', '99G999D9S') AS t; t @@ -2231,6 +2252,27 @@ SELECT to_number('12,454.8-', '99G999D9S'::citext) t (1 row) +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S') + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + +SELECT cast('12,454.8-' as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; t @@ -2252,6 +2294,27 @@ SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext) t (1 row) +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY') + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + -- Try assigning function results to a column. SELECT COUNT(*) = 8::bigint AS t FROM try; t diff --git a/contrib/citext/expected/citext_1.out b/contrib/citext/expected/citext_1.out index c5e5f180f2b..9513bb72ce3 100644 --- a/contrib/citext/expected/citext_1.out +++ b/contrib/citext/expected/citext_1.out @@ -2210,6 +2210,27 @@ SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext) t (1 row) +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY') + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000' as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext) = to_number('12,454.8-', '99G999D9S') AS t; t @@ -2231,6 +2252,27 @@ SELECT to_number('12,454.8-', '99G999D9S'::citext) t (1 row) +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S') + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + +SELECT cast('12,454.8-' as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + t +--- + t +(1 row) + SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; t @@ -2252,6 +2294,27 @@ SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext) t (1 row) +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY') + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + +SELECT cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + t +--- + t +(1 row) + -- Try assigning function results to a column. SELECT COUNT(*) = 8::bigint AS t FROM try; t diff --git a/contrib/citext/sql/citext.sql b/contrib/citext/sql/citext.sql index aa1cf9abd5c..1b32b8bf656 100644 --- a/contrib/citext/sql/citext.sql +++ b/contrib/citext/sql/citext.sql @@ -677,6 +677,13 @@ SELECT to_date('05 Dec 2000'::citext, 'DD Mon YYYY') SELECT to_date('05 Dec 2000', 'DD Mon YYYY'::citext) = to_date('05 Dec 2000', 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000'::citext as date format 'DD Mon YYYY') + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000' as date format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as date format 'DD Mon YYYY') AS t; + SELECT to_number('12,454.8-'::citext, '99G999D9S'::citext) = to_number('12,454.8-', '99G999D9S') AS t; SELECT to_number('12,454.8-'::citext, '99G999D9S') @@ -684,6 +691,13 @@ SELECT to_number('12,454.8-'::citext, '99G999D9S') SELECT to_number('12,454.8-', '99G999D9S'::citext) = to_number('12,454.8-', '99G999D9S') AS t; +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; +SELECT cast('12,454.8-'::citext as numeric format '99G999D9S') + = cast('12,454.8-' as numeric format '99G999D9S') AS t; +SELECT cast('12,454.8-' as numeric format '99G999D9S'::citext) + = cast('12,454.8-' as numeric format '99G999D9S') AS t; + SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY'::citext) = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY') @@ -691,6 +705,13 @@ SELECT to_timestamp('05 Dec 2000'::citext, 'DD Mon YYYY') SELECT to_timestamp('05 Dec 2000', 'DD Mon YYYY'::citext) = to_timestamp('05 Dec 2000', 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000'::citext as timestamptz format 'DD Mon YYYY') + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; +SELECT cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY'::citext) + = cast('05 Dec 2000' as timestamptz format 'DD Mon YYYY') AS t; + -- Try assigning function results to a column. SELECT COUNT(*) = 8::bigint AS t FROM try; INSERT INTO try diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d29664ca5d4..0c5cb3ec728 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -4463,6 +4463,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 713ee5c10a2..f5fe634d526 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -157,6 +157,9 @@ 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, TypeName *typename, + Node *format, + 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); @@ -16070,6 +16073,8 @@ func_expr_common_subexpr: } | CAST '(' a_expr AS Typename ')' { $$ = makeTypeCast($3, $5, @1); } + | CAST '(' a_expr AS Typename FORMAT a_expr ')' + { $$ = makeFormattedTypeCast($3, $5, $7, @1); } | EXTRACT '(' extract_list ')' { $$ = (Node *) makeFuncCall(SystemFuncName("extract"), @@ -18994,12 +18999,26 @@ makeColumnRef(char *colname, List *indirection, return (Node *) c; } +static Node * +makeFormattedTypeCast(Node *arg, TypeName *typename, Node *format, int location) +{ + TypeCast *n = makeNode(TypeCast); + + n->arg = arg; + n->typeName = typename; + n->format = format; + 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 913ca53666f..bfaac4229ea 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -23,6 +23,7 @@ #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "parser/parse_coerce.h" +#include "parser/parse_func.h" #include "parser/parse_relation.h" #include "parser/parse_type.h" #include "utils/builtins.h" @@ -74,6 +75,7 @@ static bool typeIsOfTypedTable(Oid reltypeId, Oid reloftypeId); * targettypmod - desired result typmod * ccontext, cformat - context indicators to control coercions * location - parse location of the coercion request, or -1 if unknown/implicit + * format - cast format template node expression in CAST(expr as type FORMAT 'format_expr') construct. */ Node * coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, @@ -81,6 +83,22 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, CoercionContext ccontext, CoercionForm cformat, int location) +{ + return coerce_to_target_type_extended(pstate, expr, exprtype, + targettype, targettypmod, + ccontext, + cformat, + location, + NULL); +} + +Node * +coerce_to_target_type_extended(ParseState *pstate, Node *expr, Oid exprtype, + Oid targettype, int32 targettypmod, + CoercionContext ccontext, + CoercionForm cformat, + int location, + Node *format) { Node *result; Node *origexpr; @@ -102,9 +120,10 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, while (expr && IsA(expr, CollateExpr)) expr = (Node *) ((CollateExpr *) expr)->arg; - result = coerce_type(pstate, expr, exprtype, - targettype, targettypmod, - ccontext, cformat, location); + result = coerce_type_extended(pstate, expr, exprtype, + targettype, targettypmod, + ccontext, cformat, location, + format); /* * If the target is a fixed-length type, it may need a length coercion as @@ -131,6 +150,177 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype, return result; } + /* + * coerce_type_with_format() + * + * Cocerce the CAST(expr AS type FORMAT 'fmt') construct to the target type. + * + * This is a subroutine of coerce_type_extended. The caller must ensure that + * the coercion is possible via can_coerce_type. + * + * We cannot simply construct a FuncCall node and rely on transformFuncCall, + * because the source expression and format template have already been + * transformed. Invoking transformExprRecurse again would be incorrect. + * Instead, we construct a FuncCall node and let ParseFuncOrColumn produce + * the final FuncExpr node. + */ +static Node * +coerce_type_with_format(ParseState *pstate, Node *node, Node *fmt, + Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, + CoercionContext ccontext, CoercionForm cformat, int location) +{ + Node *format; + Node *funcexpr; + FuncCall *fn; + List *funcname = NIL; + List *args = NIL; + TYPCATEGORY s_typcategory; + TYPCATEGORY t_typcategory; + TYPCATEGORY fmtcategory; + int32 targetBaseTypeMod = targetTypeMod; + Oid targetBaseTypeId = getBaseTypeAndTypmod(targetTypeId, + &targetBaseTypeMod); + Oid inputBaseTypeId = getBaseType(inputTypeId); + Oid formatBaseTypeId = getBaseType(exprType(fmt)); + + s_typcategory = TypeCategory(inputBaseTypeId); + t_typcategory = TypeCategory(targetBaseTypeId); + fmtcategory = TypeCategory(formatBaseTypeId); + + /* + * Since the caller (coerce_type_extended) does not handle 'Unknown' + * constants, we must explicitly coerce them to TEXT in the source + * expression. + */ + if (IsA(node, Const) && inputTypeId == UNKNOWNOID) + { + Const *con = (Const *) node; + Const *newcon = makeNode(Const); + Type textType = typeidType(TEXTOID); + + 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); + newcon->constvalue = stringTypeDatum(textType, + DatumGetCString(con->constvalue), + -1); + if (!newcon->constisnull && newcon->constlen == -1) + newcon->constvalue = + PointerGetDatum(PG_DETOAST_DATUM(newcon->constvalue)); + + ReleaseSysCache(textType); + + node = (Node *) newcon; + s_typcategory = TYPCATEGORY_STRING; + inputBaseTypeId = TEXTOID; + } + + if (inputBaseTypeId == targetBaseTypeId) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cast type %s to %s while using a format template", + format_type_be(inputBaseTypeId), + format_type_be(targetBaseTypeId)), + errdetail("binary coercible type cast is not supported while using a format template"), + parser_coercion_errposition(pstate, location, node)); + + if (s_typcategory != TYPCATEGORY_NUMERIC && + s_typcategory != TYPCATEGORY_STRING && + s_typcategory != TYPCATEGORY_DATETIME && + s_typcategory != TYPCATEGORY_TIMESPAN) + { + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cast type %s to %s while using a format template", + format_type_be(inputBaseTypeId), + format_type_be(targetBaseTypeId)), + errdetail("Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting"), + parser_coercion_errposition(pstate, location, node)); + } + + if (targetBaseTypeId != NUMERICOID && + targetBaseTypeId != TIMESTAMPTZOID && + targetBaseTypeId != DATEOID && + t_typcategory != TYPCATEGORY_STRING) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot cast type %s to %s using formatted template", + format_type_be(inputTypeId), + format_type_be(targetTypeId)), + errhint("Only timestamptz, text, numeric and date data type are supported for formatted type casting"), + parser_coercion_errposition(pstate, location, node)); + + if (fmtcategory != TYPCATEGORY_STRING && fmtcategory != TYPCATEGORY_UNKNOWN) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("CAST FORMAT expression is not of type text"), + parser_errposition(pstate, exprLocation(fmt))); + + /* + * Internally, CAST FORMAT delegates to functions (e.g., to_char, to_date) + * where the format string parameter is typed as TEXT. Consequently, the + * FORMAT clause requires explicit coercion to TEXT. + */ + format = coerce_to_target_type(pstate, fmt, + exprType(fmt), TEXTOID, + exprTypmod(fmt), + ccontext, cformat, + exprLocation(fmt)); + + if (expression_returns_set(format)) + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("CAST FORMAT expression must not return a set"), + parser_errposition(pstate, exprLocation(format))); + + if (t_typcategory == TYPCATEGORY_STRING) + funcname = list_make2(makeString("pg_catalog"), + makeString("to_char")); + else if (t_typcategory == TYPCATEGORY_NUMERIC) + funcname = list_make2(makeString("pg_catalog"), + makeString("to_number")); + else if (targetBaseTypeId == DATEOID) + funcname = list_make2(makeString("pg_catalog"), + makeString("to_date")); + else if (t_typcategory == TYPCATEGORY_DATETIME) + funcname = list_make2(makeString("pg_catalog"), + makeString("to_timestamp")); + else + elog(ERROR, "failed to find conversion function from %s to %s while using a format template", + format_type_be(inputTypeId), format_type_be(targetTypeId)); + + args = list_make1(node); + args = lappend(args, format); + fn = makeFuncCall(funcname, args, + COERCE_SQL_SYNTAX, + -1); + + funcexpr = ParseFuncOrColumn(pstate, + fn->funcname, + fn->args, + NULL, + fn, + false, + fn->location); + + /* + * For CAST FORMAT, we do not enforce the exact source type; we allow + * certain categories of types. Therefore, for the produced FuncExpr node, + * we need to coerce it to the exact target type. This is also necessary + * since the target type may be a domain type. + */ + return coerce_to_target_type(pstate, + funcexpr, exprType(funcexpr), + targetTypeId, targetTypeMod, + ccontext, + cformat, + location); +} + /* * coerce_type() @@ -158,6 +348,18 @@ Node * coerce_type(ParseState *pstate, Node *node, Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, CoercionContext ccontext, CoercionForm cformat, int location) +{ + return coerce_type_extended(pstate, node, + inputTypeId, targetTypeId, targetTypeMod, + ccontext, cformat, location, + NULL); +} + +Node * +coerce_type_extended(ParseState *pstate, Node *node, + Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, + CoercionContext ccontext, CoercionForm cformat, int location, + Node *fmt) { Node *result; CoercionPathType pathtype; @@ -166,6 +368,14 @@ coerce_type(ParseState *pstate, Node *node, if (targetTypeId == inputTypeId || node == NULL) { + if (fmt != NULL) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s while using a format template", + format_type_be(inputTypeId), + format_type_be(targetTypeId)), + errdetail("binary coercible type cast is not supported while using a format template")); + /* no conversion needed */ return node; } @@ -175,6 +385,13 @@ coerce_type(ParseState *pstate, Node *node, targetTypeId == ANYCOMPATIBLEOID || targetTypeId == ANYCOMPATIBLENONARRAYOID) { + if (fmt != NULL) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s while using a format template", + format_type_be(inputTypeId), + format_type_be(targetTypeId))); + /* * Assume can_coerce_type verified that implicit coercion is okay. * @@ -197,6 +414,13 @@ coerce_type(ParseState *pstate, Node *node, targetTypeId == ANYCOMPATIBLERANGEOID || targetTypeId == ANYCOMPATIBLEMULTIRANGEOID) { + if (fmt != NULL) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("cannot cast type %s to %s while using a format template", + format_type_be(inputTypeId), + format_type_be(targetTypeId))); + /* * Assume can_coerce_type verified that implicit coercion is okay. * @@ -230,6 +454,17 @@ coerce_type(ParseState *pstate, Node *node, return node; } } + + if (fmt != NULL) + { + result = coerce_type_with_format(pstate, node, fmt, + inputTypeId, + targetTypeId, targetTypeMod, + ccontext, cformat, + location); + return result; + } + if (inputTypeId == UNKNOWNOID && IsA(node, Const)) { /* diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index dcfe1acc4c3..d1960799ee5 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -2717,6 +2717,7 @@ transformTypeCast(ParseState *pstate, TypeCast *tc) Node *result; Node *arg = tc->arg; Node *expr; + Node *format = NULL; Oid inputType; Oid targetType; int32 targetTypmod; @@ -2738,6 +2739,12 @@ transformTypeCast(ParseState *pstate, TypeCast *tc) int32 targetBaseTypmod; Oid elementType; + if (tc->format) + ereport(ERROR, + errcode(ERRCODE_CANNOT_COERCE), + errmsg("formmatted type cast is not supported for 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 @@ -2765,6 +2772,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 @@ -2774,11 +2784,12 @@ 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); + result = coerce_to_target_type_extended(pstate, expr, inputType, + targetType, targetTypmod, + COERCION_EXPLICIT, + COERCE_EXPLICIT_CAST, + location, + format); 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 b5f4c72459d..e2da366733f 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -685,6 +685,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 b5a7ad9066e..57b1576efb7 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -11420,6 +11420,25 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context) get_rule_expr((Node *) lsecond(expr->args), context, false); appendStringInfoString(buf, "))"); return true; + case F_TO_CHAR_TIMESTAMPTZ_TEXT: + case F_TO_CHAR_NUMERIC_TEXT: + case F_TO_CHAR_INT4_TEXT: + case F_TO_CHAR_INT8_TEXT: + case F_TO_CHAR_FLOAT4_TEXT: + case F_TO_CHAR_FLOAT8_TEXT: + case F_TO_NUMBER: + case F_TO_TIMESTAMP_TEXT_TEXT: + case F_TO_DATE: + case F_TO_CHAR_TIMESTAMP_TEXT: + /* CAST FORMAT */ + appendStringInfoString(buf, "CAST( "); + get_rule_expr((Node *) linitial(expr->args), context, false); + appendStringInfoString(buf, " AS "); + appendStringInfoString(buf, format_type_be(expr->funcresulttype)); + appendStringInfoString(buf, " FORMAT "); + get_rule_expr((Node *) lsecond(expr->args), context, false); + appendStringInfoChar(buf, ')'); + return true; } return false; } diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 646d6ced763..8d0ad867cf1 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -397,6 +397,7 @@ typedef struct TypeCast NodeTag type; Node *arg; /* the expression being casted */ TypeName *typeName; /* the target type */ + Node *format; /* the cast format template Const */ 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 aabacd49b65..147c15c8e5a 100644 --- a/src/include/parser/parse_coerce.h +++ b/src/include/parser/parse_coerce.h @@ -43,11 +43,22 @@ extern Node *coerce_to_target_type(ParseState *pstate, CoercionContext ccontext, CoercionForm cformat, int location); +extern Node *coerce_to_target_type_extended(ParseState *pstate, + Node *expr, Oid exprtype, + Oid targettype, int32 targettypmod, + CoercionContext ccontext, + CoercionForm cformat, + int location, + Node *format); 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); +extern Node *coerce_type_extended(ParseState *pstate, Node *node, + Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod, + CoercionContext ccontext, CoercionForm cformat, int location, + Node *format); 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/collate.linux.utf8.out b/src/test/regress/expected/collate.linux.utf8.out index c6e84c27b69..e416fb1a84c 100644 --- a/src/test/regress/expected/collate.linux.utf8.out +++ b/src/test/regress/expected/collate.linux.utf8.out @@ -463,6 +463,30 @@ SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR"); 01 NİS 2010 (1 row) +SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY'); + text +------------- + 01 ŞUB 2010 +(1 row) + +SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR"); + text +------------- + 01 ŞUB 2010 +(1 row) + +SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY'); + text +------------- + 01 NIS 2010 +(1 row) + +SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR"); + text +------------- + 01 NİS 2010 +(1 row) + -- to_date SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY'); to_date @@ -479,6 +503,15 @@ SELECT to_date('01 Şub 2010', 'DD TMMON YYYY'); SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail ERROR: invalid value "1234567890ab" for "MONTH" DETAIL: The given value did not match any of the allowed values for this field. +SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY'); --ok + date +------------ + 02-01-2010 +(1 row) + +SELECT CAST('1234567890ab 2010' as date format 'TMMONTH YYYY'); -- fail +ERROR: invalid value "1234567890ab" for "MONTH" +DETAIL: The given value did not match any of the allowed values for this field. -- backwards parsing CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out index 32cf62b6741..1df401f4027 100644 --- a/src/test/regress/expected/horology.out +++ b/src/test/regress/expected/horology.out @@ -3324,72 +3324,144 @@ SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ'); Sun Dec 18 08:38:00 2011 PST (1 row) +SELECT cast('2011-12-18 11:38 EST' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); + timestamptz +------------------------------ + Sun Dec 18 08:38:00 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ'); to_timestamp ------------------------------ Sun Dec 18 08:38:00 2011 PST (1 row) +SELECT cast('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); + timestamptz +------------------------------ + Sun Dec 18 08:38:00 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ'); to_timestamp ------------------------------ Sun Dec 18 02:08:00 2011 PST (1 row) +SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); + timestamptz +------------------------------ + Sun Dec 18 02:08:00 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38 MSK', 'YYYY-MM-DD HH12:MI TZ'); -- dyntz to_timestamp ------------------------------ 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'); -- dyntz + 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 ------------------------------ Sun Dec 18 08:38:24 2011 PST (1 row) +SELECT cast('2011-12-18 11:38ESTFOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS'); + timestamptz +------------------------------ + Sun Dec 18 08:38:24 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS'); to_timestamp ------------------------------ Sun Dec 18 08:38:24 2011 PST (1 row) +SELECT cast('2011-12-18 11:38-05FOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS'); + timestamptz +------------------------------ + Sun Dec 18 08:38:24 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ'); -- error ERROR: invalid value "JUNK" for "TZ" DETAIL: Time zone abbreviation is not recognized. +SELECT cast('2011-12-18 11:38 JUNK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error +ERROR: invalid value "JUNK" for "TZ" +DETAIL: Time zone abbreviation is not recognized. SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ'); -- error ERROR: invalid value ".." for "TZ" DETAIL: Value must be an integer. +SELECT cast('2011-12-18 11:38 ...' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error +ERROR: invalid value ".." for "TZ" +DETAIL: Value must be an integer. SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF'); to_timestamp ------------------------------ Sun Dec 18 08:38:00 2011 PST (1 row) +SELECT cast ('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); + timestamptz +------------------------------ + Sun Dec 18 08:38:00 2011 PST +(1 row) + SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF'); to_timestamp ------------------------------ Sun Dec 18 02:08:00 2011 PST (1 row) +SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); + timestamptz +------------------------------ + Sun Dec 18 02:08:00 2011 PST +(1 row) + 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 ---------------------------------- Fri Nov 02 12:34:56.025 2018 PDT (1 row) +SELECT cast('2018-11-02 12:34:56.025' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.MS'); + timestamptz +---------------------------------- + Fri Nov 02 12:34:56.025 2018 PDT +(1 row) + SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; i | to_timestamp ---+------------------------------ @@ -3469,6 +3541,8 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.123456', 'YYYY-MM-DD HH24:MI:SS.FF' 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, cast('2018-11-02 12:34:56.123456789' as timestamptz format '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; i | to_timestamp ---+------------------------------------- @@ -3486,18 +3560,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 -- @@ -3833,12 +3925,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) -- @@ -3868,6 +3972,31 @@ 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'), + to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); + text | to_char +----------------------------+---------------------------- + 2012-12-12 12:00:00 -01:30 | 2012-12-12 12:00:00 -01:30 +(1 row) + +SELECT cast('2012-12-12 12:00'::date as text format 'YYYY-MM-DD HH:MI:SS TZ'), + to_char('2012-12-12 12:00'::date, 'YYYY-MM-DD HH:MI:SS TZ'); + text | to_char +----------------------------+---------------------------- + 2012-12-12 12:00:00 -01:30 | 2012-12-12 12:00:00 -01:30 +(1 row) + +SELECT cast('12:00'::time as text format 'HH:MI:SS'), + to_char('12:00'::time, 'HH:MI:SS'); + text | to_char +----------+---------- + 12:00:00 | 12:00:00 +(1 row) + +SELECT cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ'); +ERROR: function pg_catalog.to_char(time with time zone, text) does not exist +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS'); to_char ------------------ diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index a16e3ccdb2e..4bcaaaf04a7 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -2263,12 +2263,24 @@ SELECT to_char('infinity'::interval, 'YYYY'); (1 row) +SELECT cast('infinity'::interval as text format 'YYYY'); + text +------ + +(1 row) + SELECT to_char('-infinity'::interval, 'YYYY'); to_char --------- (1 row) +SELECT cast('-infinity'::interval as text format 'YYYY'); + text +------ + +(1 row) + -- "ago" can only appear once at the end of an interval. SELECT INTERVAL '42 days 2 seconds ago ago'; ERROR: invalid input syntax for type interval: "42 days 2 seconds ago ago" diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out index 6e816c57f1f..4aed7e8a498 100644 --- a/src/test/regress/expected/misc.out +++ b/src/test/regress/expected/misc.out @@ -396,3 +396,219 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h; -- -- rewrite rules -- +select cast('2012-13-12' as date format 'YYYY-MM-DD'); --error +ERROR: date/time field value out of range: "2012-13-12" +select cast('1' as date format 'YYYY-MM-DD'); + date +------------ + 01-01-0001 +(1 row) + +select cast('1' collate "C" as date format 'YYYY-MM-DD'); + date +------------ + 01-01-0001 +(1 row) + +select cast('2012-13-12' as date format 'YYYY-DD-MM') as date; + date +------------ + 12-13-2012 +(1 row) + +select cast('2012-13-12' as timestamptz format 'YYYY-DD-MM') as date; + date +------------------------------ + Thu Dec 13 00:00:00 2012 PST +(1 row) + +select cast('1' as timestamp format 'YYYY-MM-DD') = to_timestamp('1', 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to timestamp without time zone using formatted template +LINE 1: select cast('1' as timestamp format 'YYYY-MM-DD') = to_times... + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('2026-01-28 13:29:12.324606+01'::text as timestamp format 'YYYY-MM-DD') = + to_timestamp('2026-01-28 13:29:12.324606+01'::text, 'YYYY-MM-DD'); +ERROR: cannot cast type text to timestamp without time zone using formatted template +LINE 1: select cast('2026-01-28 13:29:12.324606+01'::text as timesta... + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD'); + ?column? +---------- + t +(1 row) + +--CAST FORMAT is not supported for binary coercible type cast, below all should fail +select cast('2022-01-01' as unknown format null); +ERROR: cannot cast type unknown to unknown while using a format template +DETAIL: binary coercible type cast is not supported while using a format template +select cast('1' as text format '1'::text); +ERROR: cannot cast type text to text while using a format template +LINE 1: select cast('1' as text format '1'::text); + ^ +DETAIL: binary coercible type cast is not supported while using a format template +select cast('1'::text as text format '1'::text); +ERROR: cannot cast type text to text while using a format template +DETAIL: binary coercible type cast is not supported while using a format template +--CAST FORMAT can only be used in limited types, below case should all fail +select cast('-34,338,492' as bigint format '99G999G999'); +ERROR: cannot cast type unknown to bigint using formatted template +LINE 1: select cast('-34,338,492' as bigint format '99G999G999'); + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast(array[1] as text format 'YYYY'); +ERROR: formmatted type cast is not supported for array type +LINE 1: select cast(array[1] as text format 'YYYY'); + ^ +select cast('1' as timestamp[] format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to timestamp without time zone[] using formatted template +LINE 1: select cast('1' as timestamp[] format 'YYYY-MM-DD'); + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('1'::text as unknown format 'YYYY-MM-DD'); +ERROR: cannot cast type text to unknown using formatted template +LINE 1: select cast('1'::text as unknown format 'YYYY-MM-DD'); + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('1' as bool format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to boolean using formatted template +LINE 1: select cast('1' as bool format 'YYYY-MM-DD'); + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('1' as json format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to json using formatted template +LINE 1: select cast('1' as json format 'YYYY-MM-DD'); + ^ +HINT: Only timestamptz, text, numeric and date data type are supported for formatted type casting +select cast('1'::json as text format 'YYYY-MM-DD'); +ERROR: cannot cast type json to text while using a format template +LINE 1: select cast('1'::json as text format 'YYYY-MM-DD'); + ^ +DETAIL: Only categories of numeric, string, datetime, and timespan source data types are supported for formatted type casting +select cast('1' as anyelement format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to anyelement while using a format template +select cast('1' as anyenum format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to anyenum +LINE 1: select cast('1' as anyenum format 'YYYY-MM-DD'); + ^ +select cast(null::anyelement as anyelement format 'YYYY-MM-DD'); +ERROR: cannot cast type unknown to anyelement while using a format template +select cast('1' as date format 1); +ERROR: CAST FORMAT expression is not of type text +LINE 1: select cast('1' as date format 1); + ^ +select cast('1'::text collate "C" 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) + +--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) + +create function ret_settxt() returns setof text as +$$ +begin + return query execute 'select 1 union all select 1'; +end; +$$ +language plpgsql immutable; +--format expression cannot return a set +select cast(NULL as date format ret_settxt()); +ERROR: CAST FORMAT expression must not return a set +LINE 1: select cast(NULL as date format ret_settxt()); + ^ +drop function ret_settxt; +create table tcast(col1 text, col2 text, col3 date, col4 timestamptz, col5 int8); +insert into tcast(col1, col2, col5) values('2022-12-13', 'YYYY-MM-DD', 1234), ('2022-12-01', 'YYYY-DD-MM', -1234); +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: CAST FORMAT expression is not of type text +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 imm_const() returns text as $$ begin return 'YYYY-MM-DD'; end; $$ language plpgsql immutable;; +select cast(col1 as date format imm_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 +LINE 1: create index s1 on tcast(cast(col1 as date format 'YYYY-MM-D... + ^ +create view tcast_v1 as + select cast(col1 as date format 'YYYY-MM-DD') as to_date, + cast(col1 as timestamptz format 'YYYY-MM-DD') as to_timestamptz, + cast(NULL::interval as text format 'YYYY-MM-DD') as to_txt0, + cast(col3 as text format 'YYYY-MM-DD') as to_txt, + cast(col4 as text format 'YYYY-MM-DD') as to_txt1, + cast(numeric 'inf' as text format 'YYYY-MM-DD') as to_txt2, + cast(bigint '12324' as text format 'YYYY-MM-DD') as to_txt3 + from tcast; +select pg_get_viewdef('tcast_v1', true); + pg_get_viewdef +------------------------------------------------------------------------------------------ + SELECT CAST( col1 AS date FORMAT 'YYYY-MM-DD'::text) AS to_date, + + CAST( col1 AS timestamp with time zone FORMAT 'YYYY-MM-DD'::text) AS to_timestamptz,+ + to_char(NULL::interval, 'YYYY-MM-DD'::text) AS to_txt0, + + CAST( col3 AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt, + + CAST( col4 AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt1, + + CAST( 'Infinity'::numeric AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt2, + + CAST( '12324'::bigint AS text FORMAT 'YYYY-MM-DD'::text) AS to_txt3 + + FROM tcast; +(1 row) + +create view tcast_v2 as + select cast(col5 as text format '9.99EEEE') as to_txt0, + cast(col5::float8 as text format '9.99EEEE') as to_txt1, + cast(col5::float4 as text format '9.99EEEE') as to_txt2, + cast(col5::numeric as text format '9.99EEEE') as to_txt3, + cast(col5::int2 as text format '9.99EEEE') as to_txt4 + from tcast; +select pg_get_viewdef('tcast_v2', true); + pg_get_viewdef +------------------------------------------------------------------------------- + SELECT CAST( col5 AS text FORMAT '9.99EEEE'::text) AS to_txt0, + + CAST( col5::double precision AS text FORMAT '9.99EEEE'::text) AS to_txt1,+ + CAST( col5::real AS text FORMAT '9.99EEEE'::text) AS to_txt2, + + CAST( col5::numeric AS text FORMAT '9.99EEEE'::text) AS to_txt3, + + CAST( col5::smallint AS text FORMAT '9.99EEEE'::text) AS to_txt4 + + FROM tcast; +(1 row) + +select * from tcast_v2; + to_txt0 | to_txt1 | to_txt2 | to_txt3 | to_txt4 +-----------+-----------+-----------+-----------+----------- + 1.23e+03 | 1.23e+03 | 1.23e+03 | 1.23e+03 | 1.23e+03 + -1.23e+03 | -1.23e+03 | -1.23e+03 | -1.23e+03 | -1.23e+03 +(2 rows) + diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out index c58e232a263..09264b98bfb 100644 --- a/src/test/regress/expected/numeric.out +++ b/src/test/regress/expected/numeric.out @@ -2264,147 +2264,286 @@ SELECT to_number('-34,338,492', '99G999G999'); -34338492 (1 row) +SELECT CAST('-34,338,492' as numeric FORMAT '99G999G999'); + numeric +----------- + -34338492 +(1 row) + SELECT to_number('-34,338,492.654,878', '99G999G999D999G999'); to_number ------------------ -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 ---------------- -564646.654564 (1 row) +SELECT CAST('<564646.654564>' as numeric FORMAT '999999.999999PR'); + numeric +---------------- + -564646.654564 +(1 row) + SELECT to_number('0.00001-', '9.999999S'); to_number ----------- -0.00001 (1 row) +SELECT CAST('0.00001-' as numeric FORMAT '9.999999S'); + numeric +---------- + -0.00001 +(1 row) + SELECT to_number('5.01-', 'FM9.999999S'); to_number ----------- -5.01 (1 row) +SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999S'); + numeric +--------- + -5.01 +(1 row) + SELECT to_number('5.01-', 'FM9.999999MI'); to_number ----------- -5.01 (1 row) +SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999MI'); + numeric +--------- + -5.01 +(1 row) + SELECT to_number('5 4 4 4 4 8 . 7 8', '9 9 9 9 9 9 . 9 9'); to_number ----------- 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 ----------- 0.01 (1 row) +SELECT CAST('.01' as numeric FORMAT 'FM9.99'); + numeric +--------- + 0.01 +(1 row) + SELECT to_number('.0', '99999999.99999999'); to_number ----------- 0.0 (1 row) +SELECT CAST('.0' as numeric FORMAT '99999999.99999999'); + numeric +--------- + 0.0 +(1 row) + SELECT to_number('0', '99.99'); to_number ----------- 0 (1 row) +SELECT CAST('0' as numeric FORMAT '99.99'); + numeric +--------- + 0 +(1 row) + SELECT to_number('.-01', 'S99.99'); to_number ----------- -0.01 (1 row) +SELECT CAST('.-01' as numeric FORMAT 'S99.99'); + numeric +--------- + -0.01 +(1 row) + SELECT to_number('.01-', '99.99S'); to_number ----------- -0.01 (1 row) +SELECT CAST('.01-' as numeric FORMAT '99.99S'); + numeric +--------- + -0.01 +(1 row) + SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S'); to_number ----------- -0.01 (1 row) +SELECT CAST(' . 0 1-' as numeric FORMAT ' 9 9 . 9 9 S'); + numeric +--------- + -0.01 +(1 row) + SELECT to_number('34,50','999,99'); to_number ----------- 3450 (1 row) +SELECT CAST('34,50' as numeric FORMAT '999,99'); + numeric +--------- + 3450 +(1 row) + SELECT to_number('123,000','999G'); to_number ----------- 123 (1 row) +SELECT CAST('123,000' as numeric FORMAT '999G'); + numeric +--------- + 123 +(1 row) + SELECT to_number('123456','999G999'); to_number ----------- 123456 (1 row) +SELECT CAST('123456' as numeric FORMAT '999G999'); + numeric +--------- + 123456 +(1 row) + SELECT to_number('$1234.56','L9,999.99'); to_number ----------- 1234.56 (1 row) +SELECT CAST('$1234.56' as numeric FORMAT 'L9,999.99'); + numeric +--------- + 1234.56 +(1 row) + SELECT to_number('$1234.56','L99,999.99'); to_number ----------- 1234.56 (1 row) +SELECT CAST('$1234.56' as numeric FORMAT 'L99,999.99'); + numeric +--------- + 1234.56 +(1 row) + SELECT to_number('$1,234.56','L99,999.99'); to_number ----------- 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 ----------- 1234.56 (1 row) +SELECT CAST('1234.56' as numeric FORMAT 'L99,999.99'); + numeric +--------- + 1234.56 +(1 row) + SELECT to_number('1,234.56','L99,999.99'); to_number ----------- 1234.56 (1 row) +SELECT CAST('1,234.56' as numeric FORMAT 'L99,999.99'); + numeric +--------- + 1234.56 +(1 row) + SELECT to_number('42nd', '99th'); to_number ----------- 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 diff --git a/src/test/regress/sql/collate.linux.utf8.sql b/src/test/regress/sql/collate.linux.utf8.sql index 132d13af0a8..840a3350a07 100644 --- a/src/test/regress/sql/collate.linux.utf8.sql +++ b/src/test/regress/sql/collate.linux.utf8.sql @@ -182,12 +182,19 @@ SELECT to_char(date '2010-02-01', 'DD TMMON YYYY' COLLATE "tr_TR"); SELECT to_char(date '2010-04-01', 'DD TMMON YYYY'); SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR"); +SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY'); +SELECT CAST(date '2010-02-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR"); +SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY'); +SELECT CAST(date '2010-04-01' as text format 'DD TMMON YYYY' COLLATE "tr_TR"); + -- to_date SELECT to_date('01 ŞUB 2010', 'DD TMMON YYYY'); SELECT to_date('01 Şub 2010', 'DD TMMON YYYY'); SELECT to_date('1234567890ab 2010', 'TMMONTH YYYY'); -- fail +SELECT CAST('01 ŞUB 2010' as date format 'DD TMMON YYYY'); --ok +SELECT CAST('1234567890ab 2010' as date format 'TMMONTH YYYY'); -- fail -- backwards parsing diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql index 8978249a5dc..714e375b088 100644 --- a/src/test/regress/sql/horology.sql +++ b/src/test/regress/sql/horology.sql @@ -539,21 +539,46 @@ SELECT to_timestamp('2011-12-18 11:38 -05:20', 'YYYY-MM-DD HH12:MI TZH:TZM'); SELECT to_timestamp('2011-12-18 11:38 20', 'YYYY-MM-DD HH12:MI TZM'); SELECT to_timestamp('2011-12-18 11:38 EST', 'YYYY-MM-DD HH12:MI TZ'); +SELECT cast('2011-12-18 11:38 EST' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); + SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI TZ'); +SELECT cast('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); + SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI TZ'); +SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format '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'); -- dyntz + 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 cast('2011-12-18 11:38ESTFOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS'); + SELECT to_timestamp('2011-12-18 11:38-05FOO24', 'YYYY-MM-DD HH12:MITZFOOSS'); +SELECT cast('2011-12-18 11:38-05FOO24' as timestamptz format 'YYYY-MM-DD HH12:MITZFOOSS'); + SELECT to_timestamp('2011-12-18 11:38 JUNK', 'YYYY-MM-DD HH12:MI TZ'); -- error +SELECT cast('2011-12-18 11:38 JUNK' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error + SELECT to_timestamp('2011-12-18 11:38 ...', 'YYYY-MM-DD HH12:MI TZ'); -- error +SELECT cast('2011-12-18 11:38 ...' as timestamptz format 'YYYY-MM-DD HH12:MI TZ'); -- error SELECT to_timestamp('2011-12-18 11:38 -05', 'YYYY-MM-DD HH12:MI OF'); +SELECT cast ('2011-12-18 11:38 -05' as timestamptz format 'YYYY-MM-DD HH12:MI OF'); + SELECT to_timestamp('2011-12-18 11:38 +01:30', 'YYYY-MM-DD HH12:MI OF'); +SELECT cast('2011-12-18 11:38 +01:30' as timestamptz format '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'); +SELECT cast('2018-11-02 12:34:56.025' as timestamptz format 'YYYY-MM-DD HH24:MI:SS.MS'); SELECT i, to_timestamp('2018-11-02 12:34:56', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; SELECT i, to_timestamp('2018-11-02 12:34:56.1', 'YYYY-MM-DD HH24:MI:SS.FF' || i) FROM generate_series(1, 6) i; @@ -563,11 +588,15 @@ SELECT i, to_timestamp('2018-11-02 12:34:56.1234', 'YYYY-MM-DD HH24:MI:SS.FF' || 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 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, cast('2018-11-02 12:34:56.123456789' as timestamptz format '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 +706,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,6 +723,17 @@ 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'), + to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD HH:MI:SS TZ'); + +SELECT cast('2012-12-12 12:00'::date as text format 'YYYY-MM-DD HH:MI:SS TZ'), + to_char('2012-12-12 12:00'::date, 'YYYY-MM-DD HH:MI:SS TZ'); + +SELECT cast('12:00'::time as text format 'HH:MI:SS'), + to_char('12:00'::time, 'HH:MI:SS'); + +SELECT cast('2012-12-12 12:00'::timetz as text format 'YYYY-MM-DD HH:MI:SS TZ'); + SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSS'); SELECT to_char('2012-12-12 12:00'::timestamptz, 'YYYY-MM-DD SSSSS'); diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index 43bc793925e..a5b4d63e9a4 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -801,7 +801,9 @@ SELECT 'infinity'::interval::time; SELECT '-infinity'::interval::time; SELECT to_char('infinity'::interval, 'YYYY'); +SELECT cast('infinity'::interval as text format 'YYYY'); SELECT to_char('-infinity'::interval, 'YYYY'); +SELECT cast('-infinity'::interval as text format 'YYYY'); -- "ago" can only appear once at the end of an interval. SELECT INTERVAL '42 days 2 seconds ago ago'; diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql index 165a2e175fb..ea578db22d7 100644 --- a/src/test/regress/sql/misc.sql +++ b/src/test/regress/sql/misc.sql @@ -273,3 +273,83 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h; -- -- rewrite rules -- + +select cast('2012-13-12' as date format 'YYYY-MM-DD'); --error +select cast('1' as date format 'YYYY-MM-DD'); +select cast('1' collate "C" as date format 'YYYY-MM-DD'); +select cast('2012-13-12' as date format 'YYYY-DD-MM') as date; +select cast('2012-13-12' as timestamptz format 'YYYY-DD-MM') as date; +select cast('1' as timestamp format 'YYYY-MM-DD') = to_timestamp('1', 'YYYY-MM-DD'); +select cast('2026-01-28 13:29:12.324606+01'::text as timestamp format 'YYYY-MM-DD') = + to_timestamp('2026-01-28 13:29:12.324606+01'::text, 'YYYY-MM-DD'); +select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD'); + +--CAST FORMAT is not supported for binary coercible type cast, below all should fail +select cast('2022-01-01' as unknown format null); +select cast('1' as text format '1'::text); +select cast('1'::text as text format '1'::text); + +--CAST FORMAT can only be used in limited types, below case should all fail +select cast('-34,338,492' as bigint format '99G999G999'); +select cast(array[1] as text format 'YYYY'); +select cast('1' as timestamp[] format 'YYYY-MM-DD'); +select cast('1'::text as unknown format 'YYYY-MM-DD'); +select cast('1' as bool format 'YYYY-MM-DD'); +select cast('1' as json format 'YYYY-MM-DD'); +select cast('1'::json as text format 'YYYY-MM-DD'); +select cast('1' as anyelement format 'YYYY-MM-DD'); +select cast('1' as anyenum format 'YYYY-MM-DD'); +select cast(null::anyelement as anyelement format 'YYYY-MM-DD'); +select cast('1' as date format 1); + +select cast('1'::text collate "C" as date format 'YYYY-MM-DD'); +select cast('1' as date format 'YYYY-MM-DD') = to_date('1', 'YYYY-MM-DD') as expect_true; + +--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 + +create function ret_settxt() returns setof text as +$$ +begin + return query execute 'select 1 union all select 1'; +end; +$$ +language plpgsql immutable; +--format expression cannot return a set +select cast(NULL as date format ret_settxt()); +drop function ret_settxt; + +create table tcast(col1 text, col2 text, col3 date, col4 timestamptz, col5 int8); +insert into tcast(col1, col2, col5) values('2022-12-13', 'YYYY-MM-DD', 1234), ('2022-12-01', 'YYYY-DD-MM', -1234); + +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 imm_const() returns text as $$ begin return 'YYYY-MM-DD'; end; $$ language plpgsql immutable;; +select cast(col1 as date format imm_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') as to_date, + cast(col1 as timestamptz format 'YYYY-MM-DD') as to_timestamptz, + cast(NULL::interval as text format 'YYYY-MM-DD') as to_txt0, + cast(col3 as text format 'YYYY-MM-DD') as to_txt, + cast(col4 as text format 'YYYY-MM-DD') as to_txt1, + cast(numeric 'inf' as text format 'YYYY-MM-DD') as to_txt2, + cast(bigint '12324' as text format 'YYYY-MM-DD') as to_txt3 + from tcast; + +select pg_get_viewdef('tcast_v1', true); + +create view tcast_v2 as + select cast(col5 as text format '9.99EEEE') as to_txt0, + cast(col5::float8 as text format '9.99EEEE') as to_txt1, + cast(col5::float4 as text format '9.99EEEE') as to_txt2, + cast(col5::numeric as text format '9.99EEEE') as to_txt3, + cast(col5::int2 as text format '9.99EEEE') as to_txt4 + from tcast; +select pg_get_viewdef('tcast_v2', true); +select * from tcast_v2; diff --git a/src/test/regress/sql/numeric.sql b/src/test/regress/sql/numeric.sql index 640c6d92f4c..fd7bc26887f 100644 --- a/src/test/regress/sql/numeric.sql +++ b/src/test/regress/sql/numeric.sql @@ -1065,34 +1065,58 @@ SELECT to_char('100'::numeric, 'f"ool\\"999'); -- SET lc_numeric = 'C'; SELECT to_number('-34,338,492', '99G999G999'); +SELECT CAST('-34,338,492' as numeric FORMAT '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 CAST('<564646.654564>' as numeric FORMAT '999999.999999PR'); SELECT to_number('0.00001-', '9.999999S'); +SELECT CAST('0.00001-' as numeric FORMAT '9.999999S'); SELECT to_number('5.01-', 'FM9.999999S'); +SELECT CAST('5.01-' as numeric FORMAT 'FM9.999999S'); SELECT to_number('5.01-', 'FM9.999999MI'); +SELECT CAST('5.01-' as numeric FORMAT '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 CAST('.01' as numeric FORMAT 'FM9.99'); SELECT to_number('.0', '99999999.99999999'); +SELECT CAST('.0' as numeric FORMAT '99999999.99999999'); SELECT to_number('0', '99.99'); +SELECT CAST('0' as numeric FORMAT '99.99'); SELECT to_number('.-01', 'S99.99'); +SELECT CAST('.-01' as numeric FORMAT 'S99.99'); SELECT to_number('.01-', '99.99S'); +SELECT CAST('.01-' as numeric FORMAT '99.99S'); SELECT to_number(' . 0 1-', ' 9 9 . 9 9 S'); +SELECT CAST(' . 0 1-' as numeric FORMAT ' 9 9 . 9 9 S'); SELECT to_number('34,50','999,99'); +SELECT CAST('34,50' as numeric FORMAT '999,99'); SELECT to_number('123,000','999G'); +SELECT CAST('123,000' as numeric FORMAT '999G'); SELECT to_number('123456','999G999'); +SELECT CAST('123456' as numeric FORMAT '999G999'); SELECT to_number('$1234.56','L9,999.99'); +SELECT CAST('$1234.56' as numeric FORMAT 'L9,999.99'); SELECT to_number('$1234.56','L99,999.99'); +SELECT CAST('$1234.56' as numeric FORMAT '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 CAST('1234.56' as numeric FORMAT '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('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 -- 2.34.1
