On Tue, Jul 22, 2025 at 8:26 PM Vik Fearing <v...@postgresfriends.org> wrote:
>
> The actual syntax is:
>
>
> <cast specification> ::=
> CAST <left paren>
> <cast operand> AS <cast target>
> [ FORMAT <cast template> ]
> [ <cast error behavior> ON CONVERSION ERROR ]
> <right paren>
>
>
> "CONVERSION" is probably a noise word, but it is there because A) Oracle
> wanted it there, and B) it makes sense because if the <cast error
> behavior> fails, that is still a failure of the entire CAST.
>
>
> The <cast error behavior> is:
>
> <cast error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
>
>
> but I am planning on removing the NULL variant in favor of having the
> <value expression> be a <contextually typed value specification>. So it
> would be either ERROR ON CONVERSION ERROR (postgres's current behavior),
> or DEFAULT NULL ON CONVERSION ERROR.
>
>
> An example of B) above would be: CAST('five' AS INTEGER DEFAULT 'six' ON
> CONVERSION ERROR). 'six' is no more an integer than 'five' is, so that
> would error out because the conversion error does not happen on the
> operand but on the default clause. CAST('five' AS INTEGER DEFAULT 6 ON
> CONVERSION ERROR) would work.
>
hi.
> <cast error behavior> ::=
> ERROR
> | NULL
> | DEFAULT <value expression>
for <value expression>
I disallow it from returning a set, or using aggregate or window functions.
For example, the following three cases will fail:
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) --error
(ret_setint function is warped as (select 1 union all select 2))
for array coerce, which you already mentioned, i think the following
is what we expected.
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON
CONVERSION ERROR);
+ int4
+---------
+ {-1011}
+(1 row)
I didn't implement the [ FORMAT <cast template> ] part for now.
please check the attached regress test and tests expected result.
From 47c181eee593468c3d7b7cb57aec3a1ea8cb3c1d Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 18 Jul 2025 13:00:19 +0800
Subject: [PATCH v2 1/2] make ArrayCoerceExpr error safe
similar to https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
---
src/backend/executor/execExpr.c | 3 +++
src/backend/executor/execExprInterp.c | 4 ++++
src/backend/utils/adt/arrayfuncs.c | 7 +++++++
3 files changed, 14 insertions(+)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..1f3f899874f 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1702,6 +1702,9 @@ ExecInitExprRec(Expr *node, ExprState *state,
elemstate->innermost_caseval = (Datum *) palloc(sizeof(Datum));
elemstate->innermost_casenull = (bool *) palloc(sizeof(bool));
+ if (state->escontext != NULL)
+ elemstate->escontext = state->escontext;
+
ExecInitExprRec(acoerce->elemexpr, elemstate,
&elemstate->resvalue, &elemstate->resnull);
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 1a37737d4a2..81e46cff725 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -3644,6 +3644,10 @@ ExecEvalArrayCoerce(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
econtext,
op->d.arraycoerce.resultelemtype,
op->d.arraycoerce.amstate);
+
+ if (SOFT_ERROR_OCCURRED(op->d.arraycoerce.elemexprstate->escontext)
+ && (*op->resvalue = (Datum) 0))
+ *op->resnull = true;
}
/*
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index c8f53c6fbe7..b5f98bf22f9 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3288,6 +3288,13 @@ array_map(Datum arrayd,
/* Apply the given expression to source element */
values[i] = ExecEvalExpr(exprstate, econtext, &nulls[i]);
+ if (SOFT_ERROR_OCCURRED(exprstate->escontext))
+ {
+ pfree(values);
+ pfree(nulls);
+ return (Datum) 0;
+ }
+
if (nulls[i])
hasnulls = true;
else
--
2.34.1
From 7ec9361ba5d6225d6adbda28ae218d82b4a74b7a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 24 Jul 2025 09:10:35 +0800
Subject: [PATCH v2 2/2] CAST(expr AS newtype DEFAULT ON ERROR)
Type cast nodes are generally one of: FuncExpr, CoerceViaIO, or ArrayCoerceExpr;
see build_coercion_expression. We've already made CoerceViaIO error safe[0]; we
just need to teach it to evaluate default expression after coercion failures.
ArrayCoerceExpr is also handling errors safely.
However, FuncExpr can't be made error-safe directly (see example like int84), so
instead, we construct a CoerceViaIO node using the source expression as its
argument.
[0]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
discussion: https://postgr.es/m/CADkLM=fv1JfY4Ufa-jcwwNbjQixNViskQ8jZu3Tz_p656i_4hQ@mail.gmail.com
demo:
SELECT CAST('1' AS date DEFAULT '2011-01-01' ON ERROR),
CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON ERROR);
date | int4
------------+---------
2011-01-01 | {-1011}
---
src/backend/executor/execExpr.c | 98 +++++++++
src/backend/executor/execExprInterp.c | 30 +++
src/backend/nodes/nodeFuncs.c | 57 +++++
src/backend/nodes/queryjumblefuncs.c | 14 ++
src/backend/optimizer/util/clauses.c | 19 ++
src/backend/parser/gram.y | 31 ++-
src/backend/parser/parse_expr.c | 296 +++++++++++++++++++++++++-
src/backend/parser/parse_target.c | 14 ++
src/backend/parser/parse_type.c | 13 ++
src/backend/utils/adt/arrayfuncs.c | 6 +
src/backend/utils/adt/ruleutils.c | 15 ++
src/backend/utils/fmgr/fmgr.c | 13 ++
src/include/executor/execExpr.h | 8 +
src/include/fmgr.h | 3 +
src/include/nodes/execnodes.h | 17 ++
src/include/nodes/parsenodes.h | 6 +
src/include/nodes/primnodes.h | 33 +++
src/include/parser/parse_type.h | 2 +
src/test/regress/expected/misc.out | 151 +++++++++++++
src/test/regress/sql/misc.sql | 60 ++++++
src/tools/pgindent/typedefs.list | 3 +
21 files changed, 881 insertions(+), 8 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 1f3f899874f..94f04265ba7 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -99,6 +99,9 @@ static void ExecBuildAggTransCall(ExprState *state, AggState *aggstate,
static void ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state,
Datum *resv, bool *resnull,
ExprEvalStep *scratch);
+static void ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr, ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch);
static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
ErrorSaveContext *escontext, bool omit_quotes,
bool exists_coerce,
@@ -2180,6 +2183,14 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ ExecInitSafeTypeCastExpr(stcexpr, state, resv, resnull, &scratch);
+ break;
+ }
+
case T_CoalesceExpr:
{
CoalesceExpr *coalesce = (CoalesceExpr *) node;
@@ -4744,6 +4755,93 @@ ExecBuildParamSetEqual(TupleDesc desc,
return state;
}
+/*
+ * Push steps to evaluate a SafeTypeCastExpr and its various subsidiary expressions.
+ * We already handle CoerceViaIO, CoerceToDomain, and ArrayCoerceExpr error
+ * softly. However, FuncExpr (e.g., int84) cannot be made error-safe.
+ * In such cases, we wrap the source expression and target type information into
+ * a CoerceViaIO node instead.
+ */
+static void
+ExecInitSafeTypeCastExpr(SafeTypeCastExpr *stcexpr , ExprState *state,
+ Datum *resv, bool *resnull,
+ ExprEvalStep *scratch)
+{
+ /*
+ * If coercion to the target type fails, fall back to the default expression
+ * specified in the ON ERROR clause.
+ */
+ if (stcexpr->cast_expr == NULL)
+ {
+ ExecInitExprRec((Expr *) stcexpr->default_expr,
+ state, resv, resnull);
+ return;
+ }
+ else
+ {
+ CoerceViaIO *newexpr;
+ SafeTypeCastState *stcstate;
+ ErrorSaveContext *escontext;
+ ErrorSaveContext *saved_escontext;
+ List *jumps_to_end = NIL;
+
+ stcstate = palloc0(sizeof(SafeTypeCastState));
+ stcstate->stcexpr = stcexpr;
+ stcstate->escontext.type = T_ErrorSaveContext;
+ escontext = &stcstate->escontext;
+ state->escontext = escontext;
+
+ Assert(stcexpr->cast_expr);
+ /* evaluate argument expression into step's result area */
+ if (IsA(stcexpr->cast_expr, CoerceViaIO) ||
+ IsA(stcexpr->cast_expr, CoerceToDomain) ||
+ IsA(stcexpr->cast_expr, ArrayCoerceExpr) ||
+ IsA(stcexpr->cast_expr, Var))
+ ExecInitExprRec((Expr *) stcexpr->cast_expr,
+ state, resv, resnull);
+ else
+ {
+ newexpr = makeNode(CoerceViaIO);
+ newexpr->arg = (Expr *) stcexpr->source_expr;
+ newexpr->resulttype = stcexpr->resulttype;
+ newexpr->resultcollid = exprCollation(stcexpr->source_expr);
+ newexpr->coerceformat = COERCE_EXPLICIT_CAST;
+ newexpr->location = exprLocation(stcexpr->source_expr);
+
+ ExecInitExprRec((Expr *) newexpr,
+ state, resv, resnull);
+ }
+
+ scratch->opcode = EEOP_SAFETYPE_CAST;
+ scratch->d.stcexpr.stcstate = stcstate;
+ ExprEvalPushStep(state, scratch);
+
+ stcstate->jump_error = state->steps_len;
+ /* JUMP to end if false, that is, skip the ON ERROR expression. */
+ jumps_to_end = lappend_int(jumps_to_end, state->steps_len);
+ scratch->opcode = EEOP_JUMP_IF_NOT_TRUE;
+ scratch->resvalue = &stcstate->error.value;
+ scratch->resnull = &stcstate->error.isnull;
+ scratch->d.jump.jumpdone = -1; /* set below */
+ ExprEvalPushStep(state, scratch);
+
+ /* Steps to evaluate the ON ERROR expression */
+ saved_escontext = state->escontext;
+ state->escontext = NULL;
+ ExecInitExprRec((Expr *) stcstate->stcexpr->default_expr,
+ state, resv, resnull);
+ state->escontext = saved_escontext;
+
+ foreach_int(lc, jumps_to_end)
+ {
+ ExprEvalStep *as = &state->steps[lc];
+
+ as->d.jump.jumpdone = state->steps_len;
+ }
+ stcstate->jump_end = state->steps_len;
+ }
+}
+
/*
* Push steps to evaluate a JsonExpr and its various subsidiary expressions.
*/
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 81e46cff725..98e8a9bde62 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -568,6 +568,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_XMLEXPR,
&&CASE_EEOP_JSON_CONSTRUCTOR,
&&CASE_EEOP_IS_JSON,
+ &&CASE_EEOP_SAFETYPE_CAST,
&&CASE_EEOP_JSONEXPR_PATH,
&&CASE_EEOP_JSONEXPR_COERCION,
&&CASE_EEOP_JSONEXPR_COERCION_FINISH,
@@ -1926,6 +1927,11 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ EEO_CASE(EEOP_SAFETYPE_CAST)
+ {
+ EEO_JUMP(ExecEvalSafeTypeCast(state, op));
+ }
+
EEO_CASE(EEOP_JSONEXPR_PATH)
{
/* too complex for an inline implementation */
@@ -5187,6 +5193,30 @@ GetJsonBehaviorValueString(JsonBehavior *behavior)
return pstrdup(behavior_names[behavior->btype]);
}
+int
+ExecEvalSafeTypeCast(ExprState *state, ExprEvalStep *op)
+{
+ SafeTypeCastState *stcstate = op->d.stcexpr.stcstate;
+
+ if (SOFT_ERROR_OCCURRED(&stcstate->escontext))
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+
+ stcstate->error.value = BoolGetDatum(true);
+
+ /*
+ * Reset for next use such as for catching errors when coercing a
+ * stcexpr expression.
+ */
+ stcstate->escontext.error_occurred = false;
+ stcstate->escontext.details_wanted = false;
+
+ return stcstate->jump_error;
+ }
+ return stcstate->jump_end;
+}
+
/*
* Checks if an error occurred in ExecEvalJsonCoercion(). If so, this sets
* JsonExprState.error to trigger the ON ERROR handling steps, unless the
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..5da7bd5d88f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -206,6 +206,9 @@ exprType(const Node *expr)
case T_RowCompareExpr:
type = BOOLOID;
break;
+ case T_SafeTypeCastExpr:
+ type = ((const SafeTypeCastExpr *) expr)->resulttype;
+ break;
case T_CoalesceExpr:
type = ((const CoalesceExpr *) expr)->coalescetype;
break;
@@ -450,6 +453,8 @@ exprTypmod(const Node *expr)
return typmod;
}
break;
+ case T_SafeTypeCastExpr:
+ return ((const SafeTypeCastExpr *) expr)->resulttypmod;
case T_CoalesceExpr:
{
/*
@@ -965,6 +970,9 @@ exprCollation(const Node *expr)
/* RowCompareExpr's result is boolean ... */
coll = InvalidOid; /* ... so it has no collation */
break;
+ case T_SafeTypeCastExpr:
+ coll = ((const SafeTypeCastExpr *) expr)->resultcollid;
+ break;
case T_CoalesceExpr:
coll = ((const CoalesceExpr *) expr)->coalescecollid;
break;
@@ -1232,6 +1240,9 @@ exprSetCollation(Node *expr, Oid collation)
/* RowCompareExpr's result is boolean ... */
Assert(!OidIsValid(collation)); /* ... so never set a collation */
break;
+ case T_SafeTypeCastExpr:
+ ((SafeTypeCastExpr *) expr)->resultcollid = collation;
+ break;
case T_CoalesceExpr:
((CoalesceExpr *) expr)->coalescecollid = collation;
break;
@@ -1554,6 +1565,15 @@ exprLocation(const Node *expr)
/* just use leftmost argument's location */
loc = exprLocation((Node *) ((const RowCompareExpr *) expr)->largs);
break;
+ case T_SafeTypeCastExpr:
+ {
+ const SafeTypeCastExpr *cast_expr = (const SafeTypeCastExpr *) expr;
+ if (cast_expr->cast_expr)
+ loc = exprLocation(cast_expr->cast_expr);
+ else
+ loc = exprLocation(cast_expr->default_expr);
+ break;
+ }
case T_CoalesceExpr:
/* COALESCE keyword should always be the first thing */
loc = ((const CoalesceExpr *) expr)->location;
@@ -2325,6 +2345,18 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *scexpr = (SafeTypeCastExpr *) node;
+
+ if (WALK(scexpr->source_expr))
+ return true;
+ if (WALK(scexpr->cast_expr))
+ return true;
+ if (WALK(scexpr->default_expr))
+ return true;
+ }
+ break;
case T_CoalesceExpr:
return WALK(((CoalesceExpr *) node)->args);
case T_MinMaxExpr:
@@ -3334,6 +3366,19 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *scexpr = (SafeTypeCastExpr *) node;
+ SafeTypeCastExpr *newnode;
+
+ FLATCOPY(newnode, scexpr, SafeTypeCastExpr);
+ MUTATE(newnode->source_expr, scexpr->source_expr, Node *);
+ MUTATE(newnode->cast_expr, scexpr->cast_expr, Node *);
+ MUTATE(newnode->default_expr, scexpr->default_expr, Node *);
+
+ return (Node *) newnode;
+ }
+ break;
case T_CoalesceExpr:
{
CoalesceExpr *coalesceexpr = (CoalesceExpr *) node;
@@ -4468,6 +4513,18 @@ raw_expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stc = (SafeTypeCastExpr *) node;
+
+ if (WALK(stc->source_expr))
+ return true;
+ if (WALK(stc->cast_expr))
+ return true;
+ if (WALK(stc->default_expr))
+ return true;
+ }
+ break;
case T_CollateClause:
return WALK(((CollateClause *) node)->arg);
case T_SortBy:
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 31f97151977..76426c88e9a 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -74,6 +74,7 @@ static void _jumbleElements(JumbleState *jstate, List *elements, Node *node);
static void _jumbleParam(JumbleState *jstate, Node *node);
static void _jumbleA_Const(JumbleState *jstate, Node *node);
static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
+static void _jumbleSafeTypeCastExpr(JumbleState *jstate, Node *node);
static void _jumbleRangeTblEntry_eref(JumbleState *jstate,
RangeTblEntry *rte,
Alias *expr);
@@ -758,6 +759,19 @@ _jumbleVariableSetStmt(JumbleState *jstate, Node *node)
JUMBLE_LOCATION(location);
}
+static void
+_jumbleSafeTypeCastExpr(JumbleState *jstate, Node *node)
+{
+ SafeTypeCastExpr *expr = (SafeTypeCastExpr *) node;
+
+ if (expr->cast_expr == NULL)
+ JUMBLE_NODE(source_expr);
+ else
+ JUMBLE_NODE(cast_expr);
+
+ JUMBLE_NODE(default_expr);
+}
+
/*
* Custom query jumble function for RangeTblEntry.eref.
*/
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index f45131c34c5..a7b4e809716 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2938,6 +2938,25 @@ eval_const_expressions_mutator(Node *node,
copyObject(jve->format));
}
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stc = (SafeTypeCastExpr *) node;
+ SafeTypeCastExpr *newexpr;
+ Node *source_expr = stc->source_expr;
+ Node *default_expr = stc->default_expr;
+
+ source_expr = eval_const_expressions_mutator(source_expr, context);
+ default_expr = eval_const_expressions_mutator(default_expr, context);
+
+ newexpr = makeNode(SafeTypeCastExpr);
+ newexpr->source_expr = source_expr;
+ newexpr->cast_expr = stc->cast_expr;
+ newexpr->default_expr = default_expr;
+ newexpr->resulttype = stc->resulttype;
+ newexpr->resulttypmod = stc->resulttypmod;
+ newexpr->resultcollid = stc->resultcollid;
+ return (Node *) newexpr;
+ }
case T_SubPlan:
case T_AlternativeSubPlan:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 73345bb3c70..66f05113a5c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -642,6 +642,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <partboundspec> PartitionBoundSpec
%type <list> hash_partbound
%type <defelt> hash_partbound_elem
+%type <node> cast_on_error_clause
+%type <node> cast_on_error_action
%type <node> json_format_clause
json_format_clause_opt
@@ -15943,8 +15945,25 @@ func_expr_common_subexpr:
{
$$ = makeSQLValueFunction(SVFOP_CURRENT_SCHEMA, -1, @1);
}
- | CAST '(' a_expr AS Typename ')'
- { $$ = makeTypeCast($3, $5, @1); }
+ | CAST '(' a_expr AS Typename cast_on_error_clause ')'
+ {
+ TypeCast *cast = (TypeCast *) makeTypeCast($3, $5, @1);
+ if ($6 == NULL)
+ $$ = (Node *) cast;
+ else
+ {
+ SafeTypeCast *safecast = makeNode(SafeTypeCast);
+
+ safecast->cast = (Node *) cast;
+ safecast->expr = $6;
+
+ /*
+ * On-error actions must themselves be typecast to the
+ * same type as the original expression.
+ */
+ $$ = (Node *) safecast;
+ }
+ }
| EXTRACT '(' extract_list ')'
{
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
@@ -16330,6 +16349,14 @@ func_expr_common_subexpr:
}
;
+cast_on_error_clause: cast_on_error_action ON CONVERSION_P ERROR_P { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
+cast_on_error_action: ERROR_P { $$ = NULL; }
+ | NULL_P { $$ = makeNullAConst(-1); }
+ | DEFAULT a_expr { $$ = $2; }
+ ;
/*
* SQL/XML support
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..57dff4b7760 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,6 +16,7 @@
#include "postgres.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
@@ -37,6 +38,7 @@
#include "utils/date.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/xml.h"
@@ -60,7 +62,10 @@ static Node *transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref);
static Node *transformCaseExpr(ParseState *pstate, CaseExpr *c);
static Node *transformSubLink(ParseState *pstate, SubLink *sublink);
static Node *transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
- Oid array_type, Oid element_type, int32 typmod);
+ Oid array_type, Oid element_type, int32 typmod,
+ bool *can_coerce);
+static Node *transformArrayExprSafe(ParseState *pstate, A_ArrayExpr *a,
+ Oid array_type, Oid element_type, int32 typmod);
static Node *transformRowExpr(ParseState *pstate, RowExpr *r, bool allowDefault);
static Node *transformCoalesceExpr(ParseState *pstate, CoalesceExpr *c);
static Node *transformMinMaxExpr(ParseState *pstate, MinMaxExpr *m);
@@ -76,6 +81,7 @@ static Node *transformWholeRowRef(ParseState *pstate,
int sublevels_up, int location);
static Node *transformIndirection(ParseState *pstate, A_Indirection *ind);
static Node *transformTypeCast(ParseState *pstate, TypeCast *tc);
+static Node *transformTypeSafeCast(ParseState *pstate, SafeTypeCast *tc);
static Node *transformCollateClause(ParseState *pstate, CollateClause *c);
static Node *transformJsonObjectConstructor(ParseState *pstate,
JsonObjectConstructor *ctor);
@@ -106,6 +112,8 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
Node *ltree, Node *rtree, int location);
static Node *make_nulltest_from_distinct(ParseState *pstate,
A_Expr *distincta, Node *arg);
+static bool CovertUnknownConstSafe(ParseState *pstate, Node *node,
+ Oid targetTypeId, int32 targetTypeMod);
/*
@@ -163,13 +171,17 @@ transformExprRecurse(ParseState *pstate, Node *expr)
case T_A_ArrayExpr:
result = transformArrayExpr(pstate, (A_ArrayExpr *) expr,
- InvalidOid, InvalidOid, -1);
+ InvalidOid, InvalidOid, -1, NULL);
break;
case T_TypeCast:
result = transformTypeCast(pstate, (TypeCast *) expr);
break;
+ case T_SafeTypeCast:
+ result = transformTypeSafeCast(pstate, (SafeTypeCast *) expr);
+ break;
+
case T_CollateClause:
result = transformCollateClause(pstate, (CollateClause *) expr);
break;
@@ -2004,6 +2016,106 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
return result;
}
+static bool CovertUnknownConstSafe(ParseState *pstate, Node *node,
+ Oid targetTypeId, int32 targetTypeMod)
+{
+ Oid baseTypeId;
+ int32 baseTypeMod;
+ int32 inputTypeMod;
+ Type baseType;
+ char *string;
+ Datum datum;
+ bool converted;
+ Const *con;
+
+ Assert(IsA(node, Const));
+ Assert(exprType(node) == UNKNOWNOID);
+
+ con = (Const *) node;
+ baseTypeMod = targetTypeMod;
+ baseTypeId = getBaseTypeAndTypmod(targetTypeId, &baseTypeMod);
+
+ if (baseTypeId == INTERVALOID)
+ inputTypeMod = baseTypeMod;
+ else
+ inputTypeMod = -1;
+ baseType = typeidType(baseTypeId);
+
+ /*
+ * We assume here that UNKNOWN's internal representation is the same as
+ * CSTRING.
+ */
+ if (!con->constisnull)
+ string = DatumGetCString(con->constvalue);
+ else
+ string = NULL;
+
+ converted = stringTypeDatumSafe(baseType,
+ string,
+ inputTypeMod,
+ &datum);
+
+ ReleaseSysCache(baseType);
+
+ return converted;
+}
+
+/*
+ * See transformArrayExpr.
+*/
+static Node *
+transformArrayExprSafe(ParseState *pstate, A_ArrayExpr *a,
+ Oid array_type, Oid element_type, int32 typmod)
+{
+ ArrayExpr *newa = makeNode(ArrayExpr);
+ List *newelems = NIL;
+ ListCell *element;
+
+ newa->multidims = false;
+ foreach(element, a->elements)
+ {
+ Node *e = (Node *) lfirst(element);
+ Node *newe;
+
+ /*
+ * If an element is itself an A_ArrayExpr, recurse directly so that we
+ * can pass down any target type we were given.
+ */
+ if (IsA(e, A_ArrayExpr))
+ {
+ newe = transformArrayExprSafe(pstate,(A_ArrayExpr *) e, array_type, element_type, typmod);
+ /* we certainly have an array here */
+ Assert(array_type == InvalidOid || array_type == exprType(newe));
+ newa->multidims = true;
+ }
+ else
+ {
+ newe = transformExprRecurse(pstate, e);
+
+ if (!newa->multidims)
+ {
+ Oid newetype = exprType(newe);
+
+ if (newetype != INT2VECTOROID && newetype != OIDVECTOROID &&
+ type_is_array(newetype))
+ newa->multidims = true;
+ }
+ }
+
+ newelems = lappend(newelems, newe);
+ }
+
+ newa->array_typeid = array_type;
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = element_type;
+ newa->elements = newelems;
+ newa->list_start = a->list_start;
+ newa->list_end = -1;
+ newa->location = -1;
+
+ return (Node *) newa;
+}
+
/*
* transformArrayExpr
*
@@ -2013,7 +2125,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
*/
static Node *
transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
- Oid array_type, Oid element_type, int32 typmod)
+ Oid array_type, Oid element_type, int32 typmod, bool *can_coerce)
{
ArrayExpr *newa = makeNode(ArrayExpr);
List *newelems = NIL;
@@ -2044,9 +2156,10 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
(A_ArrayExpr *) e,
array_type,
element_type,
- typmod);
+ typmod,
+ can_coerce);
/* we certainly have an array here */
- Assert(array_type == InvalidOid || array_type == exprType(newe));
+ Assert(can_coerce || array_type == InvalidOid || array_type == exprType(newe));
newa->multidims = true;
}
else
@@ -2072,6 +2185,9 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
newelems = lappend(newelems, newe);
}
+ if (can_coerce && !*can_coerce)
+ return NULL;
+
/*
* Select a target type for the elements.
*
@@ -2139,6 +2255,17 @@ transformArrayExpr(ParseState *pstate, A_ArrayExpr *a,
Node *e = (Node *) lfirst(element);
Node *newe;
+ if (can_coerce && (*can_coerce) && IsA(e, Const) && exprType(e) == UNKNOWNOID)
+ {
+ if (!CovertUnknownConstSafe(pstate, e, coerce_type, typmod))
+ {
+ *can_coerce = false;
+ list_free(newcoercedelems);
+ newcoercedelems = NIL;
+ return NULL;
+ }
+ }
+
if (coerce_hard)
{
newe = coerce_to_target_type(pstate, e,
@@ -2742,7 +2869,8 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
(A_ArrayExpr *) arg,
targetBaseType,
elementType,
- targetBaseTypmod);
+ targetBaseTypmod,
+ NULL);
}
else
expr = transformExprRecurse(pstate, arg);
@@ -2779,6 +2907,162 @@ transformTypeCast(ParseState *pstate, TypeCast *tc)
return result;
}
+
+/*
+ * Handle an explicit CAST construct.
+ *
+ * Transform the argument, look up the type name, and apply any necessary
+ * coercion function(s).
+ * must do via coerce via IO.
+ * See transformJsonFuncExpr, ExecInitJsonExpr
+ * maybe we also need a JsonExpr Node represent
+ */
+static Node *
+transformTypeSafeCast(ParseState *pstate, SafeTypeCast *tc)
+{
+ SafeTypeCastExpr *result;
+ Node *def_expr;
+ Node *cast_expr = NULL;
+ Node *source_expr = NULL;
+ Node *array_expr = NULL;
+ Oid inputType = InvalidOid;
+ Oid targetType;
+ int32 targetTypmod;
+ int location;
+ TypeCast *tcast = (TypeCast *) tc->cast;
+ Node *tc_arg = tcast->arg;
+ bool can_coerce = true;
+ int def_expr_loc = -1;
+
+ result = makeNode(SafeTypeCastExpr);
+
+ /* Look up the type name first */
+ typenameTypeIdAndMod(pstate, tcast->typeName, &targetType, &targetTypmod);
+
+ result->resulttype = targetType;
+ result->resulttypmod = targetTypmod;
+
+ /* now looking at cast fail default expression */
+ def_expr_loc = exprLocation(tc->expr);
+ def_expr = transformExprRecurse(pstate, tc->expr);
+
+ if (expression_returns_set(def_expr))
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("DEFAULT expression must not return a set"),
+ parser_coercion_errposition(pstate, def_expr_loc, def_expr));
+
+ if (IsA(def_expr, Aggref) || IsA(def_expr, WindowFunc))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DEFAULT expression function must be a normal function"),
+ parser_coercion_errposition(pstate, def_expr_loc, def_expr));
+
+ if (IsA(def_expr, FuncExpr))
+ {
+ if (get_func_prokind(((FuncExpr *) def_expr)->funcid) != PROKIND_FUNCTION)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("DEFAULT expression function must be a normal function"),
+ parser_coercion_errposition(pstate, def_expr_loc, def_expr));
+ }
+
+ def_expr = coerce_to_target_type(pstate, def_expr, exprType(def_expr),
+ targetType, targetTypmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ exprLocation(def_expr));
+ if (def_expr == NULL)
+ ereport(ERROR,
+ errcode(ERRCODE_CANNOT_COERCE),
+ errmsg("cannot cast on_error default expression to type %s",
+ format_type_be(targetType)),
+ parser_coercion_errposition(pstate, def_expr_loc, def_expr));
+
+ /*
+ * If the subject of the typecast is an ARRAY[] construct and the target
+ * type is an array type, we invoke transformArrayExpr() directly so that
+ * we can pass down the type information. This avoids some cases where
+ * transformArrayExpr() might not infer the correct type. Otherwise, just
+ * transform the argument normally.
+ */
+ if (IsA(tc_arg, A_ArrayExpr))
+ {
+ Oid targetBaseType;
+ int32 targetBaseTypmod;
+ Oid elementType;
+
+ /*
+ * 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
+ * usual case that the target is not a domain, the remaining steps
+ * will be a no-op.
+ */
+ targetBaseTypmod = targetTypmod;
+ targetBaseType = getBaseTypeAndTypmod(targetType, &targetBaseTypmod);
+ elementType = get_element_type(targetBaseType);
+
+ if (OidIsValid(elementType))
+ {
+ array_expr = copyObject(tc_arg);
+
+ source_expr = transformArrayExpr(pstate,
+ (A_ArrayExpr *) tc_arg,
+ targetBaseType,
+ elementType,
+ targetBaseTypmod,
+ &can_coerce);
+ if (!can_coerce)
+ source_expr = transformArrayExprSafe(pstate,
+ (A_ArrayExpr *) array_expr,
+ targetBaseType,
+ elementType,
+ targetBaseTypmod);
+ }
+ else
+ source_expr = transformExprRecurse(pstate, tc_arg);
+ }
+ else
+ source_expr = transformExprRecurse(pstate, tc_arg);
+
+ inputType = exprType(source_expr);
+
+ if (inputType == InvalidOid && can_coerce)
+ return (Node *) result; /* do nothing if NULL input */
+
+ if (can_coerce && IsA(source_expr, Const) && exprType(source_expr) == UNKNOWNOID)
+ {
+ can_coerce = CovertUnknownConstSafe(pstate,
+ source_expr,
+ targetType,
+ targetTypmod);
+ }
+
+ /*
+ * 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
+ * name (this can happen in TypeName 'string' syntax, for instance).
+ */
+ location = tcast->location;
+ if (location < 0)
+ location = tcast->typeName->location;
+
+ if (can_coerce)
+ {
+ cast_expr = coerce_to_target_type(pstate, source_expr, inputType,
+ targetType, targetTypmod,
+ COERCION_EXPLICIT,
+ COERCE_EXPLICIT_CAST,
+ location);
+ }
+
+ result->source_expr = source_expr;
+ result->cast_expr = cast_expr;
+ result->default_expr = def_expr;
+
+ return (Node *) result;
+}
+
/*
* Handle an explicit COLLATE clause.
*
diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c
index 4aba0d9d4d5..812ed18c162 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1823,6 +1823,20 @@ FigureColnameInternal(Node *node, char **name)
}
}
break;
+ case T_SafeTypeCast:
+ strength = FigureColnameInternal(((SafeTypeCast *) node)->cast,
+ name);
+ if (strength <= 1)
+ {
+ TypeCast *node_cast;
+ node_cast = (TypeCast *)((SafeTypeCast *) node)->cast;
+ if (node_cast->typeName != NULL)
+ {
+ *name = strVal(llast(node_cast->typeName->names));
+ return 1;
+ }
+ }
+ break;
case T_CollateClause:
return FigureColnameInternal(((CollateClause *) node)->arg, name);
case T_GroupingFunc:
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index 7713bdc6af0..02e5f9c92d7 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -19,6 +19,7 @@
#include "catalog/pg_type.h"
#include "lib/stringinfo.h"
#include "nodes/makefuncs.h"
+#include "nodes/miscnodes.h"
#include "parser/parse_type.h"
#include "parser/parser.h"
#include "utils/array.h"
@@ -660,6 +661,18 @@ stringTypeDatum(Type tp, char *string, int32 atttypmod)
return OidInputFunctionCall(typinput, string, typioparam, atttypmod);
}
+bool
+stringTypeDatumSafe(Type tp, char *string, int32 atttypmod, Datum *result)
+{
+ Form_pg_type typform = (Form_pg_type) GETSTRUCT(tp);
+ Oid typinput = typform->typinput;
+ Oid typioparam = getTypeIOParam(tp);
+ ErrorSaveContext escontext = {T_ErrorSaveContext};
+
+ return OidInputFunctionCallSafe(typinput, string, typioparam, atttypmod,
+ (fmNodePtr) &escontext, result);
+}
+
/*
* Given a typeid, return the type's typrelid (associated relation), if any.
* Returns InvalidOid if type is not a composite type.
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index b5f98bf22f9..6bd8a989dbd 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3295,6 +3295,12 @@ array_map(Datum arrayd,
return (Datum) 0;
}
+ if (SOFT_ERROR_OCCURRED(exprstate->escontext))
+ {
+ pfree(values);
+ pfree(nulls);
+ return (Datum) 0;
+ }
if (nulls[i])
hasnulls = true;
else
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..ee868de2c64 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10534,6 +10534,21 @@ get_rule_expr(Node *node, deparse_context *context,
}
break;
+ case T_SafeTypeCastExpr:
+ {
+ SafeTypeCastExpr *stcexpr = castNode(SafeTypeCastExpr, node);
+
+ appendStringInfoString(buf, "CAST(");
+ get_rule_expr(stcexpr->source_expr, context, showimplicit);
+
+ appendStringInfo(buf, " AS %s ",
+ format_type_with_typemod(stcexpr->resulttype, stcexpr->resulttypmod));
+
+ appendStringInfoString(buf, "DEFAULT ");
+ get_rule_expr(stcexpr->default_expr, context, showimplicit);
+ appendStringInfoString(buf, " ON CONVERSION ERROR)");
+ }
+ break;
case T_JsonExpr:
{
JsonExpr *jexpr = (JsonExpr *) node;
diff --git a/src/backend/utils/fmgr/fmgr.c b/src/backend/utils/fmgr/fmgr.c
index 782291d9998..9de895e682f 100644
--- a/src/backend/utils/fmgr/fmgr.c
+++ b/src/backend/utils/fmgr/fmgr.c
@@ -1759,6 +1759,19 @@ OidInputFunctionCall(Oid functionId, char *str, Oid typioparam, int32 typmod)
return InputFunctionCall(&flinfo, str, typioparam, typmod);
}
+bool
+OidInputFunctionCallSafe(Oid functionId, char *str, Oid typioparam,
+ int32 typmod, fmNodePtr escontext,
+ Datum *result)
+{
+ FmgrInfo flinfo;
+
+ fmgr_info(functionId, &flinfo);
+
+ return InputFunctionCallSafe(&flinfo, str, typioparam, typmod,
+ escontext, result);
+}
+
char *
OidOutputFunctionCall(Oid functionId, Datum val)
{
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 75366203706..0afcf09c086 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -265,6 +265,7 @@ typedef enum ExprEvalOp
EEOP_XMLEXPR,
EEOP_JSON_CONSTRUCTOR,
EEOP_IS_JSON,
+ EEOP_SAFETYPE_CAST,
EEOP_JSONEXPR_PATH,
EEOP_JSONEXPR_COERCION,
EEOP_JSONEXPR_COERCION_FINISH,
@@ -750,6 +751,12 @@ typedef struct ExprEvalStep
JsonIsPredicate *pred; /* original expression node */
} is_json;
+ /* for EEOP_SAFECAST */
+ struct
+ {
+ struct SafeTypeCastState *stcstate; /* original expression node */
+ } stcexpr;
+
/* for EEOP_JSONEXPR_PATH */
struct
{
@@ -892,6 +899,7 @@ extern int ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
+int ExecEvalSafeTypeCast(ExprState *state, ExprEvalStep *op);
extern void ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op);
extern void ExecEvalGroupingFunc(ExprState *state, ExprEvalStep *op);
extern void ExecEvalMergeSupportFunc(ExprState *state, ExprEvalStep *op,
diff --git a/src/include/fmgr.h b/src/include/fmgr.h
index 0fe7b4ebc77..299d4eef4ed 100644
--- a/src/include/fmgr.h
+++ b/src/include/fmgr.h
@@ -750,6 +750,9 @@ extern bool DirectInputFunctionCallSafe(PGFunction func, char *str,
Datum *result);
extern Datum OidInputFunctionCall(Oid functionId, char *str,
Oid typioparam, int32 typmod);
+extern bool OidInputFunctionCallSafe(Oid functionId, char *str, Oid typioparam,
+ int32 typmod, fmNodePtr escontext,
+ Datum *result);
extern char *OutputFunctionCall(FmgrInfo *flinfo, Datum val);
extern char *OidOutputFunctionCall(Oid functionId, Datum val);
extern Datum ReceiveFunctionCall(FmgrInfo *flinfo, fmStringInfo buf,
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index e107d6e5f81..fd26e1c98b6 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1058,6 +1058,23 @@ typedef struct DomainConstraintState
ExprState *check_exprstate; /* check_expr's eval state, or NULL */
} DomainConstraintState;
+typedef struct SafeTypeCastState
+{
+ NodeTag type;
+
+ SafeTypeCastExpr *stcexpr;
+
+ /* Set to true if source_expr evaluation cause an error. */
+ NullableDatum error;
+
+ int jump_error;
+
+ int jump_end;
+
+ ErrorSaveContext escontext;
+
+} SafeTypeCastState;
+
/*
* State for JsonExpr evaluation, too big to inline.
*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..95174e0feef 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -399,6 +399,12 @@ typedef struct TypeCast
ParseLoc location; /* token location, or -1 if unknown */
} TypeCast;
+typedef struct SafeTypeCast
+{
+ NodeTag type;
+ Node *cast;
+ Node *expr; /* default expr */
+} SafeTypeCast;
/*
* CollateClause - a COLLATE expression
*/
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 6dfca3cb35b..c66c9aaf556 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -756,6 +756,39 @@ typedef enum CoercionForm
COERCE_SQL_SYNTAX, /* display with SQL-mandated special syntax */
} CoercionForm;
+/*
+ * SafeTypeCastExpr -
+ * Transformed representation of
+ * CAST(expr AS typename DEFAULT expr2 ON ERROR)
+ * CAST(expr AS typename NULL ON ERROR)
+ */
+typedef struct SafeTypeCastExpr
+{
+ pg_node_attr(custom_query_jumble)
+
+ Expr xpr;
+
+ /* transformed expression being casted */
+ Node *source_expr;
+
+ /* transformed cast expression, it maybe NULL! */
+ Node *cast_expr;
+
+ /* in case cast expression evaulation failed, fallback default expression */
+ Node *default_expr;
+
+ /* cast result data type */
+ Oid resulttype pg_node_attr(query_jumble_ignore);
+
+ /* cast result data type typmod (usually -1) */
+ int32 resulttypmod pg_node_attr(query_jumble_ignore);
+
+ /* cast result data type collation (usually -1) */
+ Oid resultcollid pg_node_attr(query_jumble_ignore);
+
+
+} SafeTypeCastExpr;
+
/*
* FuncExpr - expression node for a function call
*
diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h
index 0d919d8bfa2..12381aed64c 100644
--- a/src/include/parser/parse_type.h
+++ b/src/include/parser/parse_type.h
@@ -47,6 +47,8 @@ extern char *typeTypeName(Type t);
extern Oid typeTypeRelid(Type typ);
extern Oid typeTypeCollation(Type typ);
extern Datum stringTypeDatum(Type tp, char *string, int32 atttypmod);
+extern bool stringTypeDatumSafe(Type tp, char *string, int32 atttypmod,
+ Datum *result);
extern Oid typeidTypeRelid(Oid type_id);
extern Oid typeOrDomainTypeRelid(Oid type_id);
diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
index 6e816c57f1f..515e761ddcf 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,154 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+-- CAST DEFAULT ON CONVERSION ERROR
+VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); --error
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR));
+ ^
+VALUES (CAST('error' AS integer NULL ON CONVERSION ERROR));
+ column1
+---------
+
+(1 row)
+
+VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR));
+ column1
+---------
+ 42
+(1 row)
+
+SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR);
+ date
+------
+
+(1 row)
+
+CREATE OR REPLACE FUNCTION ret_int8() RETURNS bigint AS
+$$
+BEGIN RETURN 2147483648; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); --error
+ERROR: integer out of range
+SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); --error
+ERROR: cannot cast on_error default expression to type date
+LINE 1: SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERR...
+ ^
+-- test valid DEFAULT expression for CAST = ON CONVERSION ERROR
+CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
+$$
+BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM hobbies_r; --error
+ERROR: DEFAULT expression must not return a set
+LINE 1: SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ER...
+ ^
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+ERROR: DEFAULT expression function must be a normal function
+LINE 1: SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR);
+ ^
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
+ERROR: DEFAULT expression function must be a normal function
+LINE 1: SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION E...
+ ^
+SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); --error
+ERROR: invalid input syntax for type integer: "b"
+LINE 1: SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR);
+ ^
+-- test array coerce
+SELECT CAST('{123,abc,456}' AS integer[] DEFAULT '{-789}' ON CONVERSION ERROR);
+ int4
+--------
+ {-789}
+(1 row)
+
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR);
+ int4
+---------
+ {-1011}
+(1 row)
+
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS INTEGER[] DEFAULT '{1,2}' ON CONVERSION ERROR);
+ array
+-------
+ {1,2}
+(1 row)
+
+SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS text[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+ array
+-------------------
+ {{1,2},{three,a}}
+(1 row)
+
+CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY);
+INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}');
+SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t;
+ t
+-----------
+ {21,22,1}
+ {21,22,2}
+ {21,22,3}
+ {21,22,4}
+(4 rows)
+
+SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t;
+ a
+-----------
+ {12}
+ {21,22,2}
+ {21,22,3}
+ {13}
+(4 rows)
+
+-- test with domain
+CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL;
+CREATE DOMAIN d_char3_not_null as char(3) NOT NULL;
+CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int);
+SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); --error
+ERROR: value for domain d_int42 violates check constraint "d_int42_check"
+SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR); --ok
+ d_int42
+---------
+ 42
+(1 row)
+
+SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); --error
+ERROR: domain d_int42 does not allow null values
+SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR); --ok
+ d_int42
+---------
+ 42
+(1 row)
+
+SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR);
+ comp_domain_with_typmod
+-------------------------
+
+(1 row)
+
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR);
+ comp_domain_with_typmod
+-------------------------
+ ("1 ",2)
+(1 row)
+
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); --error
+ERROR: value too long for type character(3)
+LINE 1: ...ST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)'...
+ ^
+-- test deparse
+CREATE VIEW safecastview AS
+SELECT CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR),
+ CAST(1 as date DEFAULT ((now()::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as safecast,
+ CAST(ARRAY[['1'], ['three'],['a']] AS INTEGER[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2'], ['three', 'a']] AS text[] DEFAULT '{21,22}' ON CONVERSION ERROR) as cast2;
+\sv safecastview
+CREATE OR REPLACE VIEW public.safecastview AS
+ SELECT CAST('1234' AS character(3) DEFAULT '-1111'::integer::character(3) ON CONVERSION ERROR) AS bpchar,
+ CAST(1 AS date DEFAULT now()::date + random(min => 1, max => 1) ON CONVERSION ERROR) AS safecast,
+ CAST(ARRAY[ARRAY['1'], ARRAY['three'], ARRAY['a']] AS integer[] DEFAULT '{1,2}'::integer[] ON CONVERSION ERROR) AS cast1,
+ CAST(ARRAY[ARRAY['1'::text, '2'::text], ARRAY['three'::text, 'a'::text]] AS text[] DEFAULT '{21,22}'::text[] ON CONVERSION ERROR) AS cast2
+CREATE INDEX cast_error_idx ON hobbies_r((cast(name as int DEFAULT random(min=>1, max=>1) ON CONVERSION ERROR))); --error
+ERROR: functions in index expression must be marked IMMUTABLE
diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql
index 165a2e175fb..3d0b59313ce 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -273,3 +273,63 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+
+-- CAST DEFAULT ON CONVERSION ERROR
+VALUES (CAST('error' AS integer ERROR ON CONVERSION ERROR)); --error
+VALUES (CAST('error' AS integer NULL ON CONVERSION ERROR));
+VALUES (CAST('error' AS integer DEFAULT 42 ON CONVERSION ERROR));
+SELECT CAST(1 AS date DEFAULT NULL ON CONVERSION ERROR);
+
+CREATE OR REPLACE FUNCTION ret_int8() RETURNS bigint AS
+$$
+BEGIN RETURN 2147483648; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+SELECT CAST('a' as int DEFAULT ret_int8() ON CONVERSION ERROR); --error
+SELECT CAST('a' as date DEFAULT ret_int8() ON CONVERSION ERROR); --error
+
+-- test valid DEFAULT expression for CAST = ON CONVERSION ERROR
+CREATE OR REPLACE FUNCTION ret_setint() RETURNS SETOF integer AS
+$$
+BEGIN RETURN QUERY EXECUTE 'select 1 union all select 1'; END;
+$$
+LANGUAGE plpgsql IMMUTABLE;
+
+SELECT CAST('a' as int DEFAULT ret_setint() ON CONVERSION ERROR) FROM hobbies_r; --error
+SELECT CAST('a' as int DEFAULT sum(1) ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT sum(1) over() ON CONVERSION ERROR); --error
+SELECT CAST('a' as int DEFAULT 'b' ON CONVERSION ERROR); --error
+
+-- test array coerce
+SELECT CAST('{123,abc,456}' AS integer[] DEFAULT '{-789}' ON CONVERSION ERROR);
+SELECT CAST('{234,def,567}'::text[] AS integer[] DEFAULT '{-1011}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[['1'], ['three'],['a']] AS INTEGER[] DEFAULT '{1,2}' ON CONVERSION ERROR);
+SELECT CAST(ARRAY[['1', '2'], ['three', 'a']] AS text[] DEFAULT '{21,22}' ON CONVERSION ERROR);
+
+CREATE TABLE tcast(a text[], b int GENERATED BY DEFAULT AS IDENTITY);
+INSERT INTO tcast VALUES ('{12}'), ('{1,a, b}'), ('{{1,2}, {c,d}}'), ('{13}');
+SELECT CAST(t AS text[] DEFAULT '{21,22, ' || b || '}' ON CONVERSION ERROR) FROM tcast as t;
+SELECT CAST(t.a AS int[] DEFAULT '{21,22}'::int[] || b ON CONVERSION ERROR) FROM tcast as t;
+
+-- test with domain
+CREATE DOMAIN d_int42 as int check (value = 42) NOT NULL;
+CREATE DOMAIN d_char3_not_null as char(3) NOT NULL;
+CREATE TYPE comp_domain_with_typmod AS (a d_char3_not_null, b int);
+SELECT CAST(11 AS d_int42 DEFAULT 41 ON CONVERSION ERROR); --error
+SELECT CAST(11 AS d_int42 DEFAULT 42 ON CONVERSION ERROR); --ok
+SELECT CAST(NULL AS d_int42 DEFAULT NULL ON CONVERSION ERROR); --error
+SELECT CAST(NULL AS d_int42 DEFAULT 42 ON CONVERSION ERROR); --ok
+SELECT CAST('(,42)' AS comp_domain_with_typmod DEFAULT NULL ON CONVERSION ERROR);
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1,2)' ON CONVERSION ERROR);
+SELECT CAST('(NULL,42)' AS comp_domain_with_typmod DEFAULT '(1234,2)' ON CONVERSION ERROR); --error
+
+-- test deparse
+CREATE VIEW safecastview AS
+SELECT CAST('1234' as char(3) DEFAULT -1111 ON CONVERSION ERROR),
+ CAST(1 as date DEFAULT ((now()::date + random(min=>1, max=>1::int))) ON CONVERSION ERROR) as safecast,
+ CAST(ARRAY[['1'], ['three'],['a']] AS INTEGER[] DEFAULT '{1,2}' ON CONVERSION ERROR) as cast1,
+ CAST(ARRAY[['1', '2'], ['three', 'a']] AS text[] DEFAULT '{21,22}' ON CONVERSION ERROR) as cast2;
+\sv safecastview
+
+CREATE INDEX cast_error_idx ON hobbies_r((cast(name as int DEFAULT random(min=>1, max=>1) ON CONVERSION ERROR))); --error
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index ff050e93a50..2e79046d83b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2648,6 +2648,9 @@ STRLEN
SV
SYNCHRONIZATION_BARRIER
SYSTEM_INFO
+SafeTypeCast
+SafeTypeCastExpr
+SafeTypeCastState
SampleScan
SampleScanGetSampleSize_function
SampleScanState
--
2.34.1
