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