pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Andrew Dunstan <andrew@dunslane.net>
To: Jonathan S. Katz <jkatz@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: John Naylor <john.naylor@enterprisedb.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Subject: Re: SQL/JSON features for v15
Date: Thu, 25 Aug 2022 03:05:15 +0300
Message-ID: <3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru> (raw)
In-Reply-To: <cc17d718-56a1-359f-2792-01c5debd67e3@dunslane.net>
References: <abd9b83b-aa66-f230-3d6d-734817f0995d@postgresql.org>
	<20220815223853.itrlpqgpqheg6r6a@awork3.anarazel.de>
	<225d341b-f310-eed0-ceff-b8ec61669d9f@postgrespro.ru>
	<20220816021425.5sa6gx67zmpou3bc@awork3.anarazel.de>
	<8d0ffc54-469a-ffab-68a3-8b547e24a08d@postgresql.org>
	<7d83684b-7932-9f29-400b-0beedfafcdd4@postgrespro.ru>
	<69022273-e7e3-6d1a-10f8-d1b4740e4b97@postgresql.org>
	<92cab50c-152b-84fc-fd49-12b936113a3d@postgresql.org>
	<CA+TgmoaAZq6rRdVA65PBXBKs=jUHhf42Jf78kBLOaX3c6J7MfQ@mail.gmail.com>
	<692364.1661267311@sss.pgh.pa.us>
	<35468585-e18f-3be6-a228-4ec478f796ac@postgresql.org>
	<716936.1661275451@sss.pgh.pa.us>
	<f1c79751-2916-a9cf-c8b9-668b9700edb8@dunslane.net>
	<ab10b5df-fcc2-5871-7027-e7f73b11559d@postgresql.org>
	<3596205e-ce1e-6959-7559-6ddd67f8294b@postgrespro.ru>
	<cc17d718-56a1-359f-2792-01c5debd67e3@dunslane.net>

Hi,

On 24.08.2022 01:12, Andrew Dunstan wrote:
> On 2022-08-23 Tu 17:29, Nikita Glukhov wrote:
>
>> Here is my plan:
>>
>> 0. Take my last v7-0001 patch as a base.  It already contains refactoring
>> of JsonCoercion code.  (Fix 0002 is not needed anymore, because it is for
>> json[b] domains, which simply will not be supported.)
>>
>> 1. Replace JSON_COERCION_VIA_EXPR in JsonCoercion with new
>> JsonCoercionType(s) for hardcoded coercions.

JsonCoerion node were completely removed because they are not needed
anymore (see p. 4).

>> 2. Disable all non-JSON-compatible output types in coerceJsonFuncExpr().
>>
>> 3. Add missing safe type input functions for integers, numerics, and
>> maybe others.

We need to write much more functions, than I expected. And I still didn't
implemented safe  input functions for numeric and datetime types.
I will start to do it tomorrow.

>> 4. Implement hardcoded coercions using these functions in
>> ExecEvalJsonExprCoercion().

That was done using simple `switch (returning_typid) { .. }`,
which can be nested into `switch (jbv->type)`.

>> 5. Try to allow only constants (and also maybe column/parameter
>> references) in JSON_VALUE's DEFAULT expressions.  This should be enough
>> for the most of practical cases.  JSON_QUERY even does not have DEFAULT
>> expressions -- it has only EMPTY ARRAY and EMPTY OBJECT, which can be
>> treated as simple JSON constants.

I have not tried to implement this yet.



> er, really? This is from the regression output:
>
>
> SELECT JSON_QUERY(jsonb '[]', '$[*]' DEFAULT '"empty"' ON EMPTY);
>   json_query
> ------------
>   "empty"
> (1 row)
>
> SELECT JSON_QUERY(jsonb '[1,2]', '$[*]' DEFAULT '"empty"' ON ERROR);
>   json_query
> ------------
>   "empty"
> (1 row)
>
This is another extension.  SQL standard defines only
EMPTY ARRAY an EMPTY OBJECT behavior for JSON_QUERY:

<JSON query empty behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT

<JSON query error behavior> ::=
ERROR
| NULL
| EMPTY ARRAY
| EMPTY OBJECT


>> But it is possible to allow all other expressions in ERROR ON ERROR
>> case, and I don't know if it will be consistent enough to allow some
>> expressions in one case and deny in other.
>>
>> And there is another problem: expressions can be only checked for
>> Const-ness only after expression simplification.  AFAIU, at the
>> parsing stage they look like 'string'::type.  So, it's unclear if it
>> is correct to check expressions in ExecInitExpr().
>>
>> 6. Remove subtransactions.

They were completely removed. Only DEFAULT expression needs to be fixed now.


> Sounds like a good plan, modulo the issues in item 5. I would rather
> lose some features temporarily than try to turn handsprings to make them
> work and jeopardize the rest.
>
> I'll look forward to seeing your patch in the morning :-)
>
v8 - is a highly WIP patch, which I failed to finish today.
Even some test cases fail now, and they simply show unfinished
things like casts to bytea (they can be simply removed) and missing
safe input functions.


-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

Attachments:

v8-0001-WIP-Remove-subtransactions-in-JsonExpr-execution.patch (text/x-patch, 110.4K) download
From d02fea59799aceaf95dd81b8f2416434788e04c0 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Thu, 25 Aug 2022 02:42:17 +0300
Subject: [PATCH v8] WIP: Remove subtransactions in JsonExpr execution

---
 src/backend/executor/execExpr.c             |  84 +-
 src/backend/executor/execExprInterp.c       | 892 ++++++++++++--------
 src/backend/jit/llvm/llvmjit_expr.c         |   2 +-
 src/backend/jit/llvm/llvmjit_types.c        |   2 +-
 src/backend/nodes/nodeFuncs.c               |  91 +-
 src/backend/optimizer/util/clauses.c        |  12 -
 src/backend/parser/parse_expr.c             | 200 ++---
 src/backend/utils/adt/bool.c                |  31 +-
 src/backend/utils/adt/date.c                | 158 +++-
 src/backend/utils/adt/jsonb.c               |  26 +-
 src/backend/utils/adt/numeric.c             |  64 +-
 src/backend/utils/adt/numutils.c            |  20 +-
 src/backend/utils/adt/timestamp.c           |  27 +-
 src/include/executor/execExpr.h             |  49 +-
 src/include/nodes/primnodes.h               |  36 +-
 src/include/utils/builtins.h                |   2 +
 src/include/utils/date.h                    |  10 +
 src/include/utils/jsonb.h                   |   2 +
 src/include/utils/numeric.h                 |   4 +-
 src/include/utils/timestamp.h               |   2 +
 src/test/regress/expected/jsonb_sqljson.out | 330 ++++++--
 src/test/regress/sql/jsonb_sqljson.sql      |  76 +-
 22 files changed, 1244 insertions(+), 876 deletions(-)

diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index d0a57c7aaee..558992df909 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2561,58 +2561,41 @@ ExecInitExprRec(Expr *node, ExprState *state,
 		case T_JsonExpr:
 			{
 				JsonExpr   *jexpr = castNode(JsonExpr, node);
-				JsonExprState *jsestate = palloc0(sizeof(JsonExprState));
+				JsonExprState *jsestate;
 				ListCell   *argexprlc;
 				ListCell   *argnamelc;
 
-				scratch.opcode = EEOP_JSONEXPR;
-				scratch.d.jsonexpr.jsestate = jsestate;
+				/* JSON_TABLE preudo-function returns context item as a result */
+				if (jexpr->op == JSON_TABLE_OP)
+				{
+					ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
+									resv, resnull);
+					break;
+				}
 
+				jsestate = palloc0(sizeof(JsonExprState));
 				jsestate->jsexpr = jexpr;
 
-				jsestate->formatted_expr =
-					palloc(sizeof(*jsestate->formatted_expr));
+				scratch.opcode = EEOP_JSONEXPR;
+				scratch.d.jsonexpr.jsestate = jsestate;
 
 				ExecInitExprRec((Expr *) jexpr->formatted_expr, state,
-								&jsestate->formatted_expr->value,
-								&jsestate->formatted_expr->isnull);
-
-				jsestate->pathspec =
-					palloc(sizeof(*jsestate->pathspec));
+								&jsestate->formatted_expr.value,
+								&jsestate->formatted_expr.isnull);
 
 				ExecInitExprRec((Expr *) jexpr->path_spec, state,
-								&jsestate->pathspec->value,
-								&jsestate->pathspec->isnull);
-
-				jsestate->res_expr =
-					palloc(sizeof(*jsestate->res_expr));
-
-				jsestate->result_expr = jexpr->result_coercion
-					? ExecInitExprWithCaseValue((Expr *) jexpr->result_coercion->expr,
-												state->parent,
-												&jsestate->res_expr->value,
-												&jsestate->res_expr->isnull)
-					: NULL;
+								&jsestate->pathspec.value,
+								&jsestate->pathspec.isnull);
 
-				jsestate->default_on_empty = !jexpr->on_empty ? NULL :
-					ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
-								 state->parent);
+				if (jexpr->on_empty)
+					jsestate->default_on_empty =
+						ExecInitExpr((Expr *) jexpr->on_empty->default_expr,
+									 state->parent);
 
 				jsestate->default_on_error =
 					ExecInitExpr((Expr *) jexpr->on_error->default_expr,
 								 state->parent);
 
-				if (jexpr->omit_quotes ||
-					(jexpr->result_coercion && jexpr->result_coercion->via_io))
-				{
-					Oid			typinput;
-
-					/* lookup the result type's input function */
-					getTypeInputInfo(jexpr->returning->typid, &typinput,
-									 &jsestate->input.typioparam);
-					fmgr_info(typinput, &jsestate->input.func);
-				}
-
 				jsestate->args = NIL;
 
 				forboth(argexprlc, jexpr->passing_values,
@@ -2636,35 +2619,8 @@ ExecInitExprRec(Expr *node, ExprState *state,
 						lappend(jsestate->args, var);
 				}
 
-				jsestate->cache = NULL;
-
-				if (jexpr->coercions)
-				{
-					JsonCoercion **coercion;
-					struct JsonCoercionState *cstate;
-					Datum	   *caseval;
-					bool	   *casenull;
-
-					jsestate->coercion_expr =
-						palloc(sizeof(*jsestate->coercion_expr));
-
-					caseval = &jsestate->coercion_expr->value;
-					casenull = &jsestate->coercion_expr->isnull;
-
-					for (cstate = &jsestate->coercions.null,
-						 coercion = &jexpr->coercions->null;
-						 coercion <= &jexpr->coercions->composite;
-						 coercion++, cstate++)
-					{
-						cstate->coercion = *coercion;
-						cstate->estate = *coercion ?
-							ExecInitExprWithCaseValue((Expr *) (*coercion)->expr,
-													  state->parent,
-													  caseval, casenull) : NULL;
-					}
-				}
-
 				ExprEvalPushStep(state, &scratch);
+
 				break;
 			}
 
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 636794ca6f1..b2accedf8ca 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -1844,7 +1844,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
 		EEO_CASE(EEOP_JSONEXPR)
 		{
 			/* too complex for an inline implementation */
-			ExecEvalJson(state, op, econtext);
+			ExecEvalJsonExpr(state, op, econtext);
 			EEO_NEXT();
 		}
 
@@ -4700,96 +4700,6 @@ ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 	*op->resnull = isnull;
 }
 
-/*
- * Evaluate a JSON error/empty behavior result.
- */
-static Datum
-ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
-					 ExprState *default_estate, bool *is_null)
-{
-	*is_null = false;
-
-	switch (behavior->btype)
-	{
-		case JSON_BEHAVIOR_EMPTY_ARRAY:
-			return JsonbPGetDatum(JsonbMakeEmptyArray());
-
-		case JSON_BEHAVIOR_EMPTY_OBJECT:
-			return JsonbPGetDatum(JsonbMakeEmptyObject());
-
-		case JSON_BEHAVIOR_TRUE:
-			return BoolGetDatum(true);
-
-		case JSON_BEHAVIOR_FALSE:
-			return BoolGetDatum(false);
-
-		case JSON_BEHAVIOR_NULL:
-		case JSON_BEHAVIOR_UNKNOWN:
-		case JSON_BEHAVIOR_EMPTY:
-			*is_null = true;
-			return (Datum) 0;
-
-		case JSON_BEHAVIOR_DEFAULT:
-			return ExecEvalExpr(default_estate, econtext, is_null);
-
-		default:
-			elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
-			return (Datum) 0;
-	}
-}
-
-/*
- * Evaluate a coercion of a JSON item to the target type.
- */
-static Datum
-ExecEvalJsonExprCoercion(ExprEvalStep *op, ExprContext *econtext,
-						 Datum res, bool *isNull, void *p, bool *error)
-{
-	ExprState  *estate = p;
-	JsonExprState *jsestate;
-
-	if (estate)					/* coerce using specified expression */
-		return ExecEvalExpr(estate, econtext, isNull);
-
-	jsestate = op->d.jsonexpr.jsestate;
-
-	if (jsestate->jsexpr->op != JSON_EXISTS_OP)
-	{
-		JsonCoercion *coercion = jsestate->jsexpr->result_coercion;
-		JsonExpr   *jexpr = jsestate->jsexpr;
-		Jsonb	   *jb = *isNull ? NULL : DatumGetJsonbP(res);
-
-		if ((coercion && coercion->via_io) ||
-			(jexpr->omit_quotes && !*isNull &&
-			 JB_ROOT_IS_SCALAR(jb)))
-		{
-			/* strip quotes and call typinput function */
-			char	   *str = *isNull ? NULL : JsonbUnquote(jb);
-
-			return InputFunctionCall(&jsestate->input.func, str,
-									 jsestate->input.typioparam,
-									 jexpr->returning->typmod);
-		}
-		else if (coercion && coercion->via_populate)
-			return json_populate_type(res, JSONBOID,
-									  jexpr->returning->typid,
-									  jexpr->returning->typmod,
-									  &jsestate->cache,
-									  econtext->ecxt_per_query_memory,
-									  isNull);
-	}
-
-	if (jsestate->result_expr)
-	{
-		jsestate->res_expr->value = res;
-		jsestate->res_expr->isnull = *isNull;
-
-		res = ExecEvalExpr(jsestate->result_expr, econtext, isNull);
-	}
-
-	return res;
-}
-
 /*
  * Evaluate a JSON path variable caching computed value.
  */
@@ -4844,84 +4754,394 @@ EvalJsonPathVar(void *cxt, char *varName, int varNameLen,
 }
 
 /*
- * Prepare SQL/JSON item coercion to the output type. Returned a datum of the
- * corresponding SQL type and a pointer to the coercion state.
+ * Check whether we need to override default coercion in
+ * JSON_QUERY(OMIT QUOTES) case.
  */
-Datum
-ExecPrepareJsonItemCoercion(JsonbValue *item,
-							JsonReturning *returning,
-							struct JsonCoercionsState *coercions,
-							struct JsonCoercionState **pcoercion)
+static bool
+ExecJsonQueryNeedsIOCoercion(JsonExpr *jsexpr, Datum res, bool isnull)
 {
-	struct JsonCoercionState *coercion;
-	Datum		res;
-	JsonbValue	buf;
+	if (jsexpr->omit_quotes && !isnull)
+	{
+		Jsonb	   *jb = DatumGetJsonbP(res);
+		JsonbValue	jbv;
+
+		/* Coerce string items via I/O in OMIT QUOTES case */
+		return JsonbExtractScalar(&jb->root, &jbv) &&
+			jbv.type == jbvString;
+	}
+
+	return false;
+}
+
+/* Coerce C string to text, varchar(N), or bpchar(N) */
+static Datum
+ExecJsonStringCoercion(const char *str, int32 len, Oid typid, int32 typmod)
+{
+	if (typid == TEXTOID || typid == JSONOID || typmod <= VARHDRSZ)
+		return PointerGetDatum(cstring_to_text_with_len(str, len));
+	else
+	{
+		char	   *txt;
+		int32		size;
+
+		len = VARHDRSZ + len;
 
-	if (item->type == jbvBinary &&
-		JsonContainerIsScalar(item->val.binary.data))
+		if (typid == BPCHAROID)
+			size = typmod;
+		else
+			size = Min(len, typmod);
+
+		txt = palloc(size);
+		SET_VARSIZE(txt, size);
+
+		memcpy(VARDATA(txt), str, Min(size, len) - VARHDRSZ);
+
+		if (len < size)
+			memset(txt + len, ' ', size - len);
+
+		return PointerGetDatum(txt);
+	}
+}
+
+/* Coerce SQL/JSON item to text */
+static Datum
+ExecJsonCoercionToText(PGFunction outfunc, Datum value, Oid typid, int32 typmod)
+{
+	char	   *str = DatumGetCString(DirectFunctionCall1(outfunc, value));
+
+	return ExecJsonStringCoercion(str, strlen(str), typid, typmod);
+}
+
+/* Coerce datetime SQL/JSON item to the output typid */
+static Datum
+ExecJsonDatetimeCoercion(Datum val, Oid val_typid, Oid typid, int32 typmod,
+						 bool *isnull, bool *error)
+{
+	if (val_typid == typid)
+		return val;
+
+	switch (val_typid)
 	{
-		bool		res PG_USED_FOR_ASSERTS_ONLY;
+		case DATEOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(date_out, val, typid, typmod);
+			else if (typid == DATEOID)
+				return val;
+			else if (typid == TIMESTAMPOID)
+			{
+				int			overflow = 0;
+				Timestamp	ts =
+					date2timestamp_opt_overflow(DatumGetDateADT(val),
+												error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
 
-		res = JsonbExtractScalar(item->val.binary.data, &buf);
-		item = &buf;
-		Assert(res);
+				return TimestampGetDatum(ts);
+			}
+			else if (typid == TIMESTAMPTZOID)
+			{
+				int			overflow = 0;
+				TimestampTz	res =
+					date2timestamptz_opt_overflow(DatumGetDateADT(val),
+												  error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
+
+				return TimestampTzGetDatum(res);
+			}
+			else
+				break; /* No cast */
+
+		case TIMEOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(time_out, val, typid, typmod);
+			else if (typid == TIMEOID)
+				return val;
+			else if (typid == TIMETZOID)
+				return DirectFunctionCall1(time_timetz, val);
+			else
+				break;	/* No cast */
+
+		case TIMETZOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timetz_out, val, typid, typmod);
+			else if (typid == TIMETZOID)
+				return val;
+			else if (typid == TIMEOID)
+				return DirectFunctionCall1(timetz_time, val);
+			else
+				break;
+
+		case TIMESTAMPOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timestamp_out, val, typid, typmod);
+			else if (typid == TIMESTAMPOID)
+				return val;
+			else if (typid == DATEOID)
+				return timestamp_date_opt_error(DatumGetTimestamp(val), error);
+			else if (typid == TIMEOID)
+			{
+				TimeADT		time =
+					timestamp_time_opt_error(DatumGetTimestamp(val),
+											 isnull, error);
+
+				if ((error && *error) || *isnull)
+					return (Datum) 0;
+				else
+					return TimeADTGetDatum(time);
+			}
+			else if (typid == TIMESTAMPTZOID)
+			{
+				int			overflow = 0;
+				TimestampTz tstz =
+					timestamp2timestamptz_opt_overflow(DatumGetTimestamp(val),
+													   error ? &overflow : NULL);
+
+				if (overflow)
+				{
+					*error = true;
+					return (Datum) 0;
+				}
+
+				return TimestampTzGetDatum(tstz);
+			}
+			else
+				break;	/* No cast */
+
+		case TIMESTAMPTZOID:
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+				return ExecJsonCoercionToText(timestamptz_out, val, typid, typmod);
+			else if (typid == TIMESTAMPTZOID)
+				return val;
+			else if (typid == DATEOID)
+				return timestamptz_date_opt_error(DatumGetTimestampTz(val), error);
+			else if (typid == TIMEOID)
+			{
+				TimeADT		time =
+					timestamptz_time_opt_error(DatumGetTimestampTz(val),
+											   isnull, error);
+
+				if ((error && *error) || *isnull)
+					return (Datum) 0;
+				else
+					return TimeADTGetDatum(time);
+			}
+			else if (typid == TIMETZOID)
+			{
+				TimeTzADT  *result =
+					timestamptz_timetz_opt_error(DatumGetTimestampTz(val), error);
+
+				if ((error && *error) || !result)
+				{
+					*isnull = true;
+					return (Datum) 0;
+				}
+
+				return TimeTzADTPGetDatum(result);
+			}
+			else if (typid == TIMESTAMPOID)
+			{
+				Timestamp	ts =
+					timestamptz2timestamp_opt_error(DatumGetTimestampTz(val),
+													error);
+
+				if (error && *error)
+					return (Datum) 0;
+
+				return TimestampGetDatum(ts);
+			}
+			else
+				break;	/* No cast */
+
+		default:
+			elog(ERROR, "unexpected jsonb datetime type oid %u", val_typid);
+			break;
 	}
 
-	/* get coercion state reference and datum of the corresponding SQL type */
+	if (!error)
+		ereport(ERROR,
+			(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+			 errmsg("SQL/JSON item cannot be cast to target type")));
+
+	*error = true;
+	*isnull = true;
+
+	return (Datum) 0;
+}
+
+/* Coerce boolean SQL/JSON item or JSON_EXISTS result to the output type */
+static bool
+ExecJsonBoolCoercion(bool val, Oid typid, int32 typmod, Datum *res)
+{
+	if (typid == BOOLOID)
+		*res = BoolGetDatum(val);
+	else if (typid == INT4OID)
+		/* We only have cast bool::int4 in the catalog. */
+		*res = Int32GetDatum(val ? 1 : 0);
+	else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+		/*
+		 * bool::text returns 'true' / 'false',
+		 * boolout() returns 't' / 'f'.
+		 */
+		*res = ExecJsonStringCoercion(val ? "true" : "false", val ? 4 : 5,
+									  typid, typmod);
+	else
+		return false;
+
+	return true;
+}
+
+static Datum
+JsonbPGetTextDatum(Jsonb *jb)
+{
+	char	   *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+	Datum		res = CStringGetTextDatum(str);
+
+	pfree(str);
+	return res;
+}
+
+/* Coerce SQL/JSON item to the output typid */
+static Datum
+ExecJsonValueCoercion(JsonbValue *item, Oid typid, int32 typmod,
+					  bool *isnull, bool *error)
+{
+	*isnull = false;
+
+	/* Special case for json and jsonb output types */
+	if (typid == JSONBOID)
+		return JsonbPGetDatum(JsonbValueToJsonb(item));
+
+	if (typid == JSONOID)
+		return JsonbPGetTextDatum(JsonbValueToJsonb(item));
+
+	/*
+	 * Coercion method and set of supported output types are determined
+	 * by the item type.
+	 */
 	switch (item->type)
 	{
 		case jbvNull:
-			coercion = &coercions->null;
-			res = (Datum) 0;
-			break;
+			Assert(0); /* must be handled by the caller */
+			*isnull = true;
+			return (Datum) 0;
 
 		case jbvString:
-			coercion = &coercions->string;
-			res = PointerGetDatum(cstring_to_text_with_len(item->val.string.val,
+			if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+			{
+				return ExecJsonStringCoercion(item->val.string.val,
+											  item->val.string.len,
+											  typid, typmod);
+			}
+			else if (typid == INT2OID || typid == INT4OID || typid == INT8OID)
+			{
+				char	   *str = pnstrdup(item->val.string.val,
+										   item->val.string.len);
+
+				if (error)
+				{
+					int64		val = pg_strtoint64_opt_error(str, error);
+
+					if (*error)
+						return (Datum) 0;
+
+					if (typid == INT2OID)
+					{
+						if (val <= PG_INT16_MAX || val >= PG_INT16_MIN)
+							return Int16GetDatum((int16) val);
+					}
+					else if (typid == INT4OID)
+					{
+						if (val <= PG_INT32_MAX || val >= PG_INT32_MIN)
+							return Int32GetDatum((int32) val);
+					}
+					else
+						return Int64GetDatum(val);
+
+					*error = true;
+					return (Datum) 0;
+				}
+				else if (typid == INT2OID)
+					return Int16GetDatum(pg_strtoint16(str));
+				else if (typid == INT4OID)
+					return Int32GetDatum(pg_strtoint32(str));
+				else
+					return Int64GetDatum(pg_strtoint64(str));
+			}
+			else if (typid == BOOLOID)
+			{
+				return BoolGetDatum(boolin_opt_error(item->val.string.val,
+													 item->val.string.len,
+													 error));
+			}
+			else
+			{
+				Datum		str = CStringGetDatum(pnstrdup(item->val.string.val,
 														   item->val.string.len));
-			break;
+
+				if (typid == NUMERICOID)
+					return DirectFunctionCall3(numeric_in, str,
+											   ObjectIdGetDatum(0),
+											   Int32GetDatum(typmod));	/* FIXME errors */
+				else if (typid == DATEOID)
+					return DirectFunctionCall1(date_in, str);	/* FIXME errors */
+				else if (typid == TIMEOID)
+					return DirectFunctionCall1(time_in, str);	/* FIXME errors */
+				else if (typid == TIMETZOID)
+					return DirectFunctionCall1(timetz_in, str);	/* FIXME errors */
+				else if (typid == TIMESTAMPOID)
+					return DirectFunctionCall1(timestamp_in, str);	/* FIXME errors */
+				else if (typid == TIMESTAMPTZOID)
+					return DirectFunctionCall1(timestamptz_in, str);	/* FIXME errors */
+				else
+					break;	/* No cast */
+			}
 
 		case jbvNumeric:
-			coercion = &coercions->numeric;
-			res = NumericGetDatum(item->val.numeric);
-			break;
+			{
+				Numeric		num = item->val.numeric;
+
+				if (typid == NUMERICOID)
+					return NumericGetDatum(num);
+				else if (typid == TEXTOID || typid == VARCHAROID || typid == BPCHAROID)
+					return ExecJsonCoercionToText(numeric_out, NumericGetDatum(num), typid, typmod);
+				else if (typid == INT2OID)
+					return Int16GetDatum(numeric_int2_opt_error(num, error));
+				else if (typid == INT4OID)
+					return Int32GetDatum(numeric_int4_opt_error(num, error));
+				else if (typid == INT8OID)
+					return Int64GetDatum(numeric_int8_opt_error(num, error));
+				else
+					break;	/* No cast */
+			}
 
 		case jbvBool:
-			coercion = &coercions->boolean;
-			res = BoolGetDatum(item->val.boolean);
-			break;
-
-		case jbvDatetime:
-			res = item->val.datetime.value;
-			switch (item->val.datetime.typid)
 			{
-				case DATEOID:
-					coercion = &coercions->date;
-					break;
-				case TIMEOID:
-					coercion = &coercions->time;
-					break;
-				case TIMETZOID:
-					coercion = &coercions->timetz;
-					break;
-				case TIMESTAMPOID:
-					coercion = &coercions->timestamp;
-					break;
-				case TIMESTAMPTZOID:
-					coercion = &coercions->timestamptz;
-					break;
-				default:
-					elog(ERROR, "unexpected jsonb datetime type oid %u",
-						 item->val.datetime.typid);
-					return (Datum) 0;
+				Datum		res;
+
+				if (ExecJsonBoolCoercion(item->val.boolean, typid, typmod, &res))
+					return res;
+
+				break; /* No cast */
 			}
-			break;
+
+		case jbvDatetime:
+			return ExecJsonDatetimeCoercion(item->val.datetime.value,
+											item->val.datetime.typid,
+											typid, typmod, isnull, error);
 
 		case jbvArray:
 		case jbvObject:
 		case jbvBinary:
-			coercion = &coercions->composite;
-			res = JsonbPGetDatum(JsonbValueToJsonb(item));
+			Assert(0);	/* non-scalars must be rejected by JsonPathValue() */
 			break;
 
 		default:
@@ -4929,99 +5149,98 @@ ExecPrepareJsonItemCoercion(JsonbValue *item,
 			return (Datum) 0;
 	}
 
-	*pcoercion = coercion;
+	if (!error)
+		ereport(ERROR,
+			(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
+			 errmsg("SQL/JSON item cannot be cast to target type")));
 
-	return res;
-}
+	*error = true;
+	*isnull = true;
 
-typedef Datum (*JsonFunc) (ExprEvalStep *op, ExprContext *econtext,
-						   Datum item, bool *resnull, void *p, bool *error);
+	return (Datum) 0;
+}
 
+/*
+ * Evaluate a JSON error/empty behavior and coerce result to the output
+ * type.
+ */
 static Datum
-ExecEvalJsonExprSubtrans(JsonFunc func, ExprEvalStep *op,
-						 ExprContext *econtext,
-						 Datum res, bool *resnull,
-						 void *p, bool *error, bool subtrans)
+ExecEvalJsonBehavior(ExprContext *econtext, JsonBehavior *behavior,
+					 ExprState *default_estate,
+					 Oid ret_typid, int32 ret_typmod, bool *is_null)
 {
-	MemoryContext oldcontext;
-	ResourceOwner oldowner;
-
-	if (!subtrans)
-		/* No need to use subtransactions. */
-		return func(op, econtext, res, resnull, p, error);
+	*is_null = false;
 
-	/*
-	 * We should catch exceptions of category ERRCODE_DATA_EXCEPTION and
-	 * execute the corresponding ON ERROR behavior then.
-	 */
-	oldcontext = CurrentMemoryContext;
-	oldowner = CurrentResourceOwner;
+	switch (behavior->btype)
+	{
+		case JSON_BEHAVIOR_EMPTY_ARRAY:
+			if (ret_typid == JSONBOID)
+				return JsonbPGetDatum(JsonbMakeEmptyArray());
+			else
+			{
+				Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+					   ret_typid == VARCHAROID || ret_typid == BPCHAROID);
 
-	Assert(error);
+				return ExecJsonStringCoercion("[]", 2, ret_typid, ret_typmod);
+			}
 
-	BeginInternalSubTransaction(NULL);
-	/* Want to execute expressions inside function's memory context */
-	MemoryContextSwitchTo(oldcontext);
+		case JSON_BEHAVIOR_EMPTY_OBJECT:
+			if (ret_typid == JSONBOID)
+				return JsonbPGetDatum(JsonbMakeEmptyObject());
+			else
+			{
+				Assert(ret_typid == JSONOID || ret_typid == TEXTOID ||
+					   ret_typid == VARCHAROID || ret_typid == BPCHAROID);
 
-	PG_TRY();
-	{
-		res = func(op, econtext, res, resnull, p, error);
+				return ExecJsonStringCoercion("{}", 2, ret_typid, ret_typmod);
+			}
 
-		/* Commit the inner transaction, return to outer xact context */
-		ReleaseCurrentSubTransaction();
-		MemoryContextSwitchTo(oldcontext);
-		CurrentResourceOwner = oldowner;
-	}
-	PG_CATCH();
-	{
-		ErrorData  *edata;
-		int			ecategory;
+		case JSON_BEHAVIOR_TRUE:
+		case JSON_BEHAVIOR_FALSE:
+			{
+				Datum		res;
+				bool		ok =
+					ExecJsonBoolCoercion(behavior->btype == JSON_BEHAVIOR_TRUE,
+										 ret_typid, ret_typmod, &res);
 
-		/* Save error info in oldcontext */
-		MemoryContextSwitchTo(oldcontext);
-		edata = CopyErrorData();
-		FlushErrorState();
+				Assert(ok); /* returning type must be check in parser */
 
-		/* Abort the inner transaction */
-		RollbackAndReleaseCurrentSubTransaction();
-		MemoryContextSwitchTo(oldcontext);
-		CurrentResourceOwner = oldowner;
+				return res;
+			}
 
-		ecategory = ERRCODE_TO_CATEGORY(edata->sqlerrcode);
+		case JSON_BEHAVIOR_NULL:
+		case JSON_BEHAVIOR_UNKNOWN:
+		case JSON_BEHAVIOR_EMPTY:
+			*is_null = true;
+			return (Datum) 0;
 
-		if (ecategory != ERRCODE_DATA_EXCEPTION &&	/* jsonpath and other data
-													 * errors */
-			ecategory != ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION)	/* domain errors */
-			ReThrowError(edata);
+		case JSON_BEHAVIOR_DEFAULT:
+			/*
+			 * Execute DEFAULT expression.
+			 * Coercion is not needed here, because expression is
+			 * already coerced to the target type by the parser.
+			 */
+			return ExecEvalExpr(default_estate, econtext, is_null);
 
-		res = (Datum) 0;
-		*error = true;
+		default:
+			elog(ERROR, "unrecognized SQL/JSON behavior %d", behavior->btype);
+			return (Datum) 0;
 	}
-	PG_END_TRY();
-
-	return res;
 }
 
-
-typedef struct
-{
-	JsonPath   *path;
-	bool	   *error;
-	bool		coercionInSubtrans;
-} ExecEvalJsonExprContext;
-
 static Datum
-ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
-				 Datum item, bool *resnull, void *pcxt,
-				 bool *error)
+ExecEvalJsonExprInternal(ExprState *state,
+						 JsonExprState *jsestate, ExprContext *econtext,
+						 JsonPath *path, Datum item, bool *resnull,
+						 bool *error)
 {
-	ExecEvalJsonExprContext *cxt = pcxt;
-	JsonPath   *path = cxt->path;
-	JsonExprState *jsestate = op->d.jsonexpr.jsestate;
 	JsonExpr   *jexpr = jsestate->jsexpr;
-	ExprState  *estate = NULL;
 	bool		empty = false;
 	Datum		res = (Datum) 0;
+	Oid			ret_typid = jexpr->returning->typid;
+	int32		ret_typmod = jexpr->returning->typmod;
+
+	*resnull = true;
 
 	switch (jexpr->op)
 	{
@@ -5029,70 +5248,94 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
 			res = JsonPathQuery(item, path, jexpr->wrapper, &empty, error,
 								jsestate->args);
 			if (error && *error)
-			{
-				*resnull = true;
 				return (Datum) 0;
-			}
+
+			if (empty)
+				break;
+
 			*resnull = !DatumGetPointer(res);
-			break;
+
+			/* Override default coercion in OMIT QUOTES case */
+			if (ExecJsonQueryNeedsIOCoercion(jexpr, res, *resnull))
+			{
+				char	   *str = JsonbUnquote(DatumGetJsonbP(res));
+
+				if (ret_typid == JSONBOID)
+				{
+					res = jsonb_from_cstring(str, strlen(str), false, error);
+
+					if (error && *error)
+						return (Datum) 0;
+
+					return res;
+				}
+				else if (ret_typid == JSONOID)
+				{
+					text	   *json = cstring_to_text(str);
+					JsonLexContext *lex;
+					JsonParseErrorType result;
+
+					/* validate it */
+					lex = makeJsonLexContext(json, false);
+					result = pg_parse_json(lex, &nullSemAction);
+
+					if (result != JSON_SUCCESS)
+					{
+						if (error)
+						{
+							*error = true;
+							return (Datum) 0;
+						}
+
+						json_ereport_error(result, lex);
+					}
+
+					return PointerGetDatum(json);
+				}
+				else if (ret_typid == TEXTOID ||
+						 ret_typid == VARCHAROID ||
+						 ret_typid == BPCHAROID)
+					return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+			}
+			else if (ret_typid == JSONBOID)
+				return res;
+			else if (ret_typid == JSONOID || ret_typid == TEXTOID)
+				return JsonbPGetTextDatum(DatumGetJsonbP(res));
+			else if (ret_typid == VARCHAROID || ret_typid == BPCHAROID)
+			{
+				Jsonb	   *jb = DatumGetJsonbP(res);
+				char	   *str = JsonbToCString(NULL, &jb->root, VARSIZE(jb));
+
+				return ExecJsonStringCoercion(str, strlen(str), ret_typid, ret_typmod);
+			}
+
+			Assert(0);	/* unsupported output type */
+			*error = *resnull = true;
+			return (Datum) 0;
 
 		case JSON_VALUE_OP:
 			{
-				struct JsonCoercionState *jcstate;
 				JsonbValue *jbv = JsonPathValue(item, path, &empty, error,
 												jsestate->args);
 
 				if (error && *error)
 					return (Datum) 0;
 
-				if (!jbv)		/* NULL or empty */
+				if (empty)
 					break;
 
-				Assert(!empty);
+				if (!jbv)
+					return (Datum) 0;	/* NULL */
 
-				*resnull = false;
+				Assert(jbv->type != jbvNull);
 
-				/* coerce scalar item to the output type */
-				if (jexpr->returning->typid == JSONOID ||
-					jexpr->returning->typid == JSONBOID)
-				{
-					/* Use result coercion from json[b] to the output type */
-					res = JsonbPGetDatum(JsonbValueToJsonb(jbv));
-					break;
-				}
+				res = ExecJsonValueCoercion(jbv, ret_typid, ret_typmod,
+											resnull, error);
 
-				/* Use coercion from SQL/JSON item type to the output type */
-				res = ExecPrepareJsonItemCoercion(jbv,
-												  jsestate->jsexpr->returning,
-												  &jsestate->coercions,
-												  &jcstate);
-
-				if (jcstate->coercion &&
-					(jcstate->coercion->via_io ||
-					 jcstate->coercion->via_populate))
-				{
-					if (error)
-					{
-						*error = true;
-						return (Datum) 0;
-					}
-
-					/*
-					 * Coercion via I/O means here that the cast to the target
-					 * type simply does not exist.
-					 */
-					ereport(ERROR,
-							(errcode(ERRCODE_SQL_JSON_ITEM_CANNOT_BE_CAST_TO_TARGET_TYPE),
-							 errmsg("SQL/JSON item cannot be cast to target type")));
-				}
-				else if (!jcstate->estate)
-					return res; /* no coercion */
+				if (error && *error)
+					return (Datum) 0;
 
-				/* coerce using specific expression */
-				estate = jcstate->estate;
-				jsestate->coercion_expr->value = res;
-				jsestate->coercion_expr->isnull = *resnull;
-				break;
+				return res;
 			}
 
 		case JSON_EXISTS_OP:
@@ -5101,113 +5344,81 @@ ExecEvalJsonExpr(ExprEvalStep *op, ExprContext *econtext,
 													jsestate->args,
 													error);
 
-				*resnull = error && *error;
-				res = BoolGetDatum(exists);
-
-				if (!jsestate->result_expr)
-					return res;
+				if (!error || !*error)
+				{
+					/* Should succeed, output type is checked by parser */
+					(void) ExecJsonBoolCoercion(exists, ret_typid, ret_typmod, &res);
+					*resnull = false;
+				}
 
-				/* coerce using result expression */
-				estate = jsestate->result_expr;
-				jsestate->res_expr->value = res;
-				jsestate->res_expr->isnull = *resnull;
-				break;
+				return res;
 			}
 
-		case JSON_TABLE_OP:
-			*resnull = false;
-			return item;
-
 		default:
 			elog(ERROR, "unrecognized SQL/JSON expression op %d", jexpr->op);
 			return (Datum) 0;
 	}
 
-	if (empty)
-	{
-		Assert(jexpr->on_empty);	/* it is not JSON_EXISTS */
+	Assert(empty);
+	Assert(jexpr->on_empty);	/* it is not JSON_EXISTS */
 
-		if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+	if (jexpr->on_empty->btype == JSON_BEHAVIOR_ERROR)
+	{
+		if (error)
 		{
-			if (error)
-			{
-				*error = true;
-				return (Datum) 0;
-			}
-
-			ereport(ERROR,
-					(errcode(ERRCODE_NO_SQL_JSON_ITEM),
-					 errmsg("no SQL/JSON item")));
+			*error = true;
+			return (Datum) 0;
 		}
 
-		if (jexpr->on_empty->btype == JSON_BEHAVIOR_DEFAULT)
-
-			/*
-			 * Execute DEFAULT expression as a coercion expression, because
-			 * its result is already coerced to the target type.
-			 */
-			estate = jsestate->default_on_empty;
-		else
-			/* Execute ON EMPTY behavior */
-			res = ExecEvalJsonBehavior(econtext, jexpr->on_empty,
-									   jsestate->default_on_empty,
-									   resnull);
+		ereport(ERROR,
+				(errcode(ERRCODE_NO_SQL_JSON_ITEM),
+				 errmsg("no SQL/JSON item")));
 	}
 
-	return ExecEvalJsonExprSubtrans(ExecEvalJsonExprCoercion, op, econtext,
-									res, resnull, estate, error,
-									cxt->coercionInSubtrans);
+	/* Execute ON EMPTY behavior */
+	return ExecEvalJsonBehavior(econtext, jexpr->on_empty,
+								jsestate->default_on_empty,
+								ret_typid, ret_typmod, resnull);
 }
 
 bool
-ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
-								struct JsonCoercionsState *coercions)
+ExecEvalExprCanThrowErrors(Node *expr)
 {
-	if (jsexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
+	if (!expr)
 		return false;
 
-	if (jsexpr->op == JSON_EXISTS_OP && !jsexpr->result_coercion)
+	if (IsA(expr, Const))
 		return false;
 
-	if (!coercions)
-		return true;
-
-	return false;
+	/* TODO consider more cases */
+	return true;
 }
 
 /* ----------------------------------------------------------------
- *		ExecEvalJson
+ *		ExecEvalJsonExpr
  * ----------------------------------------------------------------
  */
 void
-ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 {
-	ExecEvalJsonExprContext cxt;
 	JsonExprState *jsestate = op->d.jsonexpr.jsestate;
 	JsonExpr   *jexpr = jsestate->jsexpr;
 	Datum		item;
-	Datum		res = (Datum) 0;
+	Datum		res;
 	JsonPath   *path;
 	ListCell   *lc;
 	bool		error = false;
-	bool		needSubtrans;
 	bool		throwErrors = jexpr->on_error->btype == JSON_BEHAVIOR_ERROR;
 
-	*op->resnull = true;		/* until we get a result */
-	*op->resvalue = (Datum) 0;
-
-	if (jsestate->formatted_expr->isnull || jsestate->pathspec->isnull)
+	if (jsestate->formatted_expr.isnull || jsestate->pathspec.isnull)
 	{
-		/* execute domain checks for NULLs */
-		(void) ExecEvalJsonExprCoercion(op, econtext, res, op->resnull,
-										NULL, NULL);
-
-		Assert(*op->resnull);
+		*op->resnull = true;
+		*op->resvalue = (Datum) 0;
 		return;
 	}
 
-	item = jsestate->formatted_expr->value;
-	path = DatumGetJsonPathP(jsestate->pathspec->value);
+	item = jsestate->formatted_expr.value;
+	path = DatumGetJsonPathP(jsestate->pathspec.value);
 
 	/* reset JSON path variable contexts */
 	foreach(lc, jsestate->args)
@@ -5218,29 +5429,20 @@ ExecEvalJson(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
 		var->evaluated = false;
 	}
 
-	needSubtrans = ExecEvalJsonNeedsSubTransaction(jexpr, &jsestate->coercions);
-
-	cxt.path = path;
-	cxt.error = throwErrors ? NULL : &error;
-	cxt.coercionInSubtrans = !needSubtrans && !throwErrors;
-	Assert(!needSubtrans || cxt.error);
-
-	res = ExecEvalJsonExprSubtrans(ExecEvalJsonExpr, op, econtext, item,
-								   op->resnull, &cxt, cxt.error,
-								   needSubtrans);
+	res = ExecEvalJsonExprInternal(state, jsestate, econtext,
+								   path, item, op->resnull,
+								   throwErrors ? NULL : &error);
 
 	if (error)
 	{
+		Assert(!throwErrors);
+
 		/* Execute ON ERROR behavior */
 		res = ExecEvalJsonBehavior(econtext, jexpr->on_error,
 								   jsestate->default_on_error,
+								   jexpr->returning->typid,
+								   jexpr->returning->typmod,
 								   op->resnull);
-
-		/* result is already coerced in DEFAULT behavior case */
-		if (jexpr->on_error->btype != JSON_BEHAVIOR_DEFAULT)
-			res = ExecEvalJsonExprCoercion(op, econtext, res,
-										   op->resnull,
-										   NULL, NULL);
 	}
 
 	*op->resvalue = res;
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index bd3965143da..fd72630f5e6 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2408,7 +2408,7 @@ llvm_compile_expr(ExprState *state)
 				break;
 
 			case EEOP_JSONEXPR:
-				build_EvalXFunc(b, mod, "ExecEvalJson",
+				build_EvalXFunc(b, mod, "ExecEvalJsonExpr",
 								v_state, op, v_econtext);
 				LLVMBuildBr(b, opblocks[opno + 1]);
 				break;
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index 373471ad27f..37fe64654b6 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -135,7 +135,7 @@ void	   *referenced_functions[] =
 	ExecEvalXmlExpr,
 	ExecEvalJsonConstructor,
 	ExecEvalJsonIsPredicate,
-	ExecEvalJson,
+	ExecEvalJsonExpr,
 	MakeExpandedObjectReadOnlyInternal,
 	slot_getmissingattrs,
 	slot_getsomeattrs_int,
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c334daae392..e0240beeeab 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -266,9 +266,6 @@ exprType(const Node *expr)
 		case T_JsonExpr:
 			type = ((const JsonExpr *) expr)->returning->typid;
 			break;
-		case T_JsonCoercion:
-			type = exprType(((const JsonCoercion *) expr)->expr);
-			break;
 		default:
 			elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
 			type = InvalidOid;	/* keep compiler quiet */
@@ -507,8 +504,6 @@ exprTypmod(const Node *expr)
 			return ((const JsonConstructorExpr *) expr)->returning->typmod;
 		case T_JsonExpr:
 			return ((JsonExpr *) expr)->returning->typmod;
-		case T_JsonCoercion:
-			return exprTypmod(((const JsonCoercion *) expr)->expr);
 		default:
 			break;
 	}
@@ -1010,14 +1005,12 @@ exprCollation(const Node *expr)
 		case T_JsonExpr:
 			{
 				JsonExpr   *jexpr = (JsonExpr *) expr;
-				JsonCoercion *coercion = jexpr->result_coercion;
 
-				if (!coercion)
-					coll = InvalidOid;
-				else if (coercion->expr)
-					coll = exprCollation(coercion->expr);
-				else if (coercion->via_io || coercion->via_populate)
-					coll = coercion->collation;
+				if (jexpr->returning->typid == TEXTOID ||
+					jexpr->returning->typid == CHAROID ||
+					jexpr->returning->typid == VARCHAROID ||
+					jexpr->returning->typid == BPCHAROID)
+					coll = jexpr->collation;
 				else
 					coll = InvalidOid;
 			}
@@ -1255,14 +1248,12 @@ exprSetCollation(Node *expr, Oid collation)
 		case T_JsonExpr:
 			{
 				JsonExpr   *jexpr = (JsonExpr *) expr;
-				JsonCoercion *coercion = jexpr->result_coercion;
 
-				if (!coercion)
-					Assert(!OidIsValid(collation));
-				else if (coercion->expr)
-					exprSetCollation(coercion->expr, collation);
-				else if (coercion->via_io || coercion->via_populate)
-					coercion->collation = collation;
+				if (jexpr->returning->typid == TEXTOID ||
+					jexpr->returning->typid == CHAROID ||
+					jexpr->returning->typid == VARCHAROID ||
+					jexpr->returning->typid == BPCHAROID)
+					jexpr->collation = collation;
 				else
 					Assert(!OidIsValid(collation));
 			}
@@ -2507,8 +2498,6 @@ expression_tree_walker(Node *node,
 
 				if (walker(jexpr->formatted_expr, context))
 					return true;
-				if (walker(jexpr->result_coercion, context))
-					return true;
 				if (walker(jexpr->passing_values, context))
 					return true;
 				/* we assume walker doesn't care about passing_names */
@@ -2517,36 +2506,6 @@ expression_tree_walker(Node *node,
 					return true;
 				if (walker(jexpr->on_error->default_expr, context))
 					return true;
-				if (walker(jexpr->coercions, context))
-					return true;
-			}
-			break;
-		case T_JsonCoercion:
-			return walker(((JsonCoercion *) node)->expr, context);
-		case T_JsonItemCoercions:
-			{
-				JsonItemCoercions *coercions = (JsonItemCoercions *) node;
-
-				if (walker(coercions->null, context))
-					return true;
-				if (walker(coercions->string, context))
-					return true;
-				if (walker(coercions->numeric, context))
-					return true;
-				if (walker(coercions->boolean, context))
-					return true;
-				if (walker(coercions->date, context))
-					return true;
-				if (walker(coercions->time, context))
-					return true;
-				if (walker(coercions->timetz, context))
-					return true;
-				if (walker(coercions->timestamp, context))
-					return true;
-				if (walker(coercions->timestamptz, context))
-					return true;
-				if (walker(coercions->composite, context))
-					return true;
 			}
 			break;
 		default:
@@ -3576,7 +3535,6 @@ expression_tree_mutator(Node *node,
 				FLATCOPY(newnode, jexpr, JsonExpr);
 				MUTATE(newnode->path_spec, jexpr->path_spec, Node *);
 				MUTATE(newnode->formatted_expr, jexpr->formatted_expr, Node *);
-				MUTATE(newnode->result_coercion, jexpr->result_coercion, JsonCoercion *);
 				MUTATE(newnode->passing_values, jexpr->passing_values, List *);
 				/* assume mutator does not care about passing_names */
 				if (newnode->on_empty)
@@ -3587,35 +3545,6 @@ expression_tree_mutator(Node *node,
 				return (Node *) newnode;
 			}
 			break;
-		case T_JsonCoercion:
-			{
-				JsonCoercion *coercion = (JsonCoercion *) node;
-				JsonCoercion *newnode;
-
-				FLATCOPY(newnode, coercion, JsonCoercion);
-				MUTATE(newnode->expr, coercion->expr, Node *);
-				return (Node *) newnode;
-			}
-			break;
-		case T_JsonItemCoercions:
-			{
-				JsonItemCoercions *coercions = (JsonItemCoercions *) node;
-				JsonItemCoercions *newnode;
-
-				FLATCOPY(newnode, coercions, JsonItemCoercions);
-				MUTATE(newnode->null, coercions->null, JsonCoercion *);
-				MUTATE(newnode->string, coercions->string, JsonCoercion *);
-				MUTATE(newnode->numeric, coercions->numeric, JsonCoercion *);
-				MUTATE(newnode->boolean, coercions->boolean, JsonCoercion *);
-				MUTATE(newnode->date, coercions->date, JsonCoercion *);
-				MUTATE(newnode->time, coercions->time, JsonCoercion *);
-				MUTATE(newnode->timetz, coercions->timetz, JsonCoercion *);
-				MUTATE(newnode->timestamp, coercions->timestamp, JsonCoercion *);
-				MUTATE(newnode->timestamptz, coercions->timestamptz, JsonCoercion *);
-				MUTATE(newnode->composite, coercions->composite, JsonCoercion *);
-				return (Node *) newnode;
-			}
-			break;
 		default:
 			elog(ERROR, "unrecognized node type: %d",
 				 (int) nodeTag(node));
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 533df86ff77..3c14ddfba2f 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -896,18 +896,6 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context)
 								 context, 0);
 	}
 
-	/* JsonExpr is parallel-unsafe if subtransactions can be used. */
-	else if (IsA(node, JsonExpr))
-	{
-		JsonExpr   *jsexpr = (JsonExpr *) node;
-
-		if (ExecEvalJsonNeedsSubTransaction(jsexpr, NULL))
-		{
-			context->max_hazard = PROPARALLEL_UNSAFE;
-			return true;
-		}
-	}
-
 	/* Recurse to check arguments */
 	return expression_tree_walker(node,
 								  max_parallel_hazard_walker,
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index fabb5f72076..a6e5fe1eb93 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4108,7 +4108,6 @@ transformJsonExprCommon(ParseState *pstate, JsonFuncExpr *func)
 	/* format is determined by context item type */
 	format = exprType(jsexpr->formatted_expr) == JSONBOID ? JS_FORMAT_JSONB : JS_FORMAT_JSON;
 
-	jsexpr->result_coercion = NULL;
 	jsexpr->omit_quotes = false;
 
 	jsexpr->format = func->common->expr->format;
@@ -4169,40 +4168,6 @@ assignDefaultJsonReturningType(Node *context_item, JsonFormat *context_format,
 	ret->typmod = -1;
 }
 
-/*
- * Try to coerce expression to the output type or
- * use json_populate_type() for composite, array and domain types or
- * use coercion via I/O.
- */
-static JsonCoercion *
-coerceJsonExpr(ParseState *pstate, Node *expr, const JsonReturning *returning)
-{
-	char		typtype;
-	JsonCoercion *coercion = makeNode(JsonCoercion);
-
-	coercion->expr = coerceJsonFuncExpr(pstate, expr, returning, false);
-
-	if (coercion->expr)
-	{
-		if (coercion->expr == expr)
-			coercion->expr = NULL;
-
-		return coercion;
-	}
-
-	typtype = get_typtype(returning->typid);
-
-	if (returning->typid == RECORDOID ||
-		typtype == TYPTYPE_COMPOSITE ||
-		typtype == TYPTYPE_DOMAIN ||
-		type_is_array(returning->typid))
-		coercion->via_populate = true;
-	else
-		coercion->via_io = true;
-
-	return coercion;
-}
-
 /*
  * Transform a JSON output clause of JSON_VALUE and JSON_QUERY.
  */
@@ -4210,8 +4175,6 @@ static void
 transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 							JsonExpr *jsexpr)
 {
-	Node	   *expr = jsexpr->formatted_expr;
-
 	jsexpr->returning = transformJsonOutput(pstate, func->output, false);
 
 	/* JSON_VALUE returns text by default */
@@ -4225,14 +4188,41 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 	{
 		JsonReturning ret;
 
-		if (func->op == JSON_VALUE_OP &&
-			jsexpr->returning->typid != JSONOID &&
-			jsexpr->returning->typid != JSONBOID)
+		if (func->op == JSON_VALUE_OP)
 		{
-			/* Forced coercion via I/O for JSON_VALUE for non-JSON types */
-			jsexpr->result_coercion = makeNode(JsonCoercion);
-			jsexpr->result_coercion->expr = NULL;
-			jsexpr->result_coercion->via_io = true;
+			/*
+			 * Only a limited list of output types is supported in
+			 * JSON_VALUE() now.
+			 */
+			switch (jsexpr->returning->typid)
+			{
+				case JSONBOID:
+				case JSONOID:
+				case TEXTOID:
+				case VARCHAROID:
+				case BPCHAROID:
+				case BOOLOID:
+				case NUMERICOID:
+				case INT2OID:
+				case INT4OID:
+				case INT8OID:
+				/* case FLOAT4OID: FIXME */
+				/* case FLOAT8OID: FIXME */
+				case DATEOID:
+				case TIMEOID:
+				case TIMETZOID:
+				case TIMESTAMPOID:
+				case TIMESTAMPTZOID:
+					break; /* Ok */
+
+				default:
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("returning type %s is not supported in JSON_VALUE()",
+									format_type_be(jsexpr->returning->typid)),
+							 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+			}
+
 			return;
 		}
 
@@ -4241,13 +4231,22 @@ transformJsonFuncExprOutput(ParseState *pstate, JsonFuncExpr *func,
 		if (ret.typid != jsexpr->returning->typid ||
 			ret.typmod != jsexpr->returning->typmod)
 		{
-			Node	   *placeholder = makeCaseTestExpr(expr);
-
-			Assert(((CaseTestExpr *) placeholder)->typeId == ret.typid);
-			Assert(((CaseTestExpr *) placeholder)->typeMod == ret.typmod);
+			switch (jsexpr->returning->typid)
+			{
+				case JSONBOID:
+				case JSONOID:
+				case TEXTOID:
+				case VARCHAROID:
+				case BPCHAROID:
+					break;
 
-			jsexpr->result_coercion = coerceJsonExpr(pstate, placeholder,
-													 jsexpr->returning);
+				default:
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("returning type %s is not supported in JSON_QUERY()",
+									format_type_be(jsexpr->returning->typid)),
+							 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+			}
 		}
 	}
 	else
@@ -4293,62 +4292,6 @@ coerceDefaultJsonExpr(ParseState *pstate, JsonExpr *jsexpr, Node *defexpr)
 	return defexpr;
 }
 
-/*
- * Initialize SQL/JSON item coercion from the SQL type "typid" to the target
- * "returning" type.
- */
-static JsonCoercion *
-initJsonItemCoercion(ParseState *pstate, Oid typid,
-					 const JsonReturning *returning)
-{
-	Node	   *expr;
-
-	if (typid == UNKNOWNOID)
-	{
-		expr = (Node *) makeNullConst(UNKNOWNOID, -1, InvalidOid);
-	}
-	else
-	{
-		CaseTestExpr *placeholder = makeNode(CaseTestExpr);
-
-		placeholder->typeId = typid;
-		placeholder->typeMod = -1;
-		placeholder->collation = InvalidOid;
-
-		expr = (Node *) placeholder;
-	}
-
-	return coerceJsonExpr(pstate, expr, returning);
-}
-
-static void
-initJsonItemCoercions(ParseState *pstate, JsonItemCoercions *coercions,
-					  const JsonReturning *returning, Oid contextItemTypeId)
-{
-	struct
-	{
-		JsonCoercion **coercion;
-		Oid			typid;
-	}		   *p,
-				coercionTypids[] =
-	{
-		{&coercions->null, UNKNOWNOID},
-		{&coercions->string, TEXTOID},
-		{&coercions->numeric, NUMERICOID},
-		{&coercions->boolean, BOOLOID},
-		{&coercions->date, DATEOID},
-		{&coercions->time, TIMEOID},
-		{&coercions->timetz, TIMETZOID},
-		{&coercions->timestamp, TIMESTAMPOID},
-		{&coercions->timestamptz, TIMESTAMPTZOID},
-		{&coercions->composite, contextItemTypeId},
-		{NULL, InvalidOid}
-	};
-
-	for (p = coercionTypids; p->coercion; p++)
-		*p->coercion = initJsonItemCoercion(pstate, p->typid, returning);
-}
-
 /*
  * Transform JSON_VALUE, JSON_QUERY, JSON_EXISTS functions into a JsonExpr node.
  */
@@ -4377,10 +4320,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 				coerceDefaultJsonExpr(pstate, jsexpr,
 									  jsexpr->on_error->default_expr);
 
-			jsexpr->coercions = makeNode(JsonItemCoercions);
-			initJsonItemCoercions(pstate, jsexpr->coercions, jsexpr->returning,
-								  exprType(contextItemExpr));
-
 			break;
 
 		case JSON_QUERY_OP:
@@ -4409,6 +4348,7 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			jsexpr->returning->format->format_type = JS_FORMAT_DEFAULT;
 			jsexpr->returning->format->encoding = JS_ENC_DEFAULT;
 
+			/* Coerce intermediate boolean result to the output type if needed */
 			if (!OidIsValid(jsexpr->returning->typid))
 			{
 				jsexpr->returning->typid = BOOLOID;
@@ -4416,32 +4356,21 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 			}
 			else if (jsexpr->returning->typid != BOOLOID)
 			{
-				CaseTestExpr *placeholder = makeNode(CaseTestExpr);
-				int			location = exprLocation((Node *) jsexpr);
-
-				placeholder->typeId = BOOLOID;
-				placeholder->typeMod = -1;
-				placeholder->collation = InvalidOid;
-
-				jsexpr->result_coercion = makeNode(JsonCoercion);
-				jsexpr->result_coercion->expr =
-					coerce_to_target_type(pstate, (Node *) placeholder, BOOLOID,
-										  jsexpr->returning->typid,
-										  jsexpr->returning->typmod,
-										  COERCION_EXPLICIT,
-										  COERCE_IMPLICIT_CAST,
-										  location);
-
-				if (!jsexpr->result_coercion->expr)
-					ereport(ERROR,
-							(errcode(ERRCODE_CANNOT_COERCE),
-							 errmsg("cannot cast type %s to %s",
-									format_type_be(BOOLOID),
-									format_type_be(jsexpr->returning->typid)),
-							 parser_coercion_errposition(pstate, location, (Node *) jsexpr)));
+				switch (jsexpr->returning->typid)
+				{
+					case INT4OID:
+					case TEXTOID:
+					case VARCHAROID:
+					case BPCHAROID:
+						break;
 
-				if (jsexpr->result_coercion->expr == (Node *) placeholder)
-					jsexpr->result_coercion->expr = NULL;
+					default:
+						ereport(ERROR,
+								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+								 errmsg("returning type %s is not supported in JSON_EXISTS()",
+										format_type_be(jsexpr->returning->typid)),
+								 parser_coercion_errposition(pstate, func->location, (Node *) jsexpr)));
+				}
 			}
 			break;
 
@@ -4457,7 +4386,6 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
 						 errmsg("JSON_TABLE() is not yet implemented for the json type"),
 						 errhint("Try casting the argument to jsonb"),
 						 parser_errposition(pstate, func->location)));
-
 			break;
 	}
 
diff --git a/src/backend/utils/adt/bool.c b/src/backend/utils/adt/bool.c
index cd7335287f9..78557c99dc5 100644
--- a/src/backend/utils/adt/bool.c
+++ b/src/backend/utils/adt/bool.c
@@ -126,12 +126,10 @@ parse_bool_with_len(const char *value, size_t len, bool *result)
  *
  * In the switch statement, check the most-used possibilities first.
  */
-Datum
-boolin(PG_FUNCTION_ARGS)
+bool
+boolin_opt_error(const char *in_str, size_t	len, bool *error)
 {
-	const char *in_str = PG_GETARG_CSTRING(0);
 	const char *str;
-	size_t		len;
 	bool		result;
 
 	/*
@@ -141,20 +139,29 @@ boolin(PG_FUNCTION_ARGS)
 	while (isspace((unsigned char) *str))
 		str++;
 
-	len = strlen(str);
+	len -= str - in_str;
 	while (len > 0 && isspace((unsigned char) str[len - 1]))
 		len--;
 
 	if (parse_bool_with_len(str, len, &result))
-		PG_RETURN_BOOL(result);
+		return result;
+
+	if (!error)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+				 errmsg("invalid input syntax for type %s: \"%s\"",
+						"boolean", in_str)));
+
+	*error = true;
+	return false;
+}
 
-	ereport(ERROR,
-			(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
-			 errmsg("invalid input syntax for type %s: \"%s\"",
-					"boolean", in_str)));
+Datum
+boolin(PG_FUNCTION_ARGS)
+{
+	char	   *str = PG_GETARG_CSTRING(0);
 
-	/* not reached */
-	PG_RETURN_BOOL(false);
+	PG_RETURN_BOOL(boolin_opt_error(str, strlen(str), NULL));
 }
 
 /*
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index 081dfa2450f..778bfc90687 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1286,10 +1286,9 @@ date_timestamp(PG_FUNCTION_ARGS)
 /* timestamp_date()
  * Convert timestamp to date data type.
  */
-Datum
-timestamp_date(PG_FUNCTION_ARGS)
+DateADT
+timestamp_date_opt_error(Timestamp timestamp, bool *error)
 {
-	Timestamp	timestamp = PG_GETARG_TIMESTAMP(0);
 	DateADT		result;
 	struct pg_tm tt,
 			   *tm = &tt;
@@ -1302,16 +1301,32 @@ timestamp_date(PG_FUNCTION_ARGS)
 	else
 	{
 		if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+		{
+			if (error)
+			{
+				*error = true;
+				return 0;
+			}
+
 			ereport(ERROR,
 					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 					 errmsg("timestamp out of range")));
+		}
 
 		result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
 	}
 
-	PG_RETURN_DATEADT(result);
+	return result;
 }
 
+/* timestamp_date()
+ * Convert timestamp to date data type.
+ */
+Datum
+timestamp_date(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_DATEADT(timestamp_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL));
+}
 
 /* date_timestamptz()
  * Convert date to timestamp with time zone data type.
@@ -1327,14 +1342,9 @@ date_timestamptz(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(result);
 }
 
-
-/* timestamptz_date()
- * Convert timestamp with time zone to date data type.
- */
-Datum
-timestamptz_date(PG_FUNCTION_ARGS)
+DateADT
+timestamptz_date_opt_error(TimestampTz timestamp, bool *error)
 {
-	TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
 	DateADT		result;
 	struct pg_tm tt,
 			   *tm = &tt;
@@ -1348,14 +1358,31 @@ timestamptz_date(PG_FUNCTION_ARGS)
 	else
 	{
 		if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+		{
+			if (error)
+			{
+				*error = true;
+				return 0;
+			}
+
 			ereport(ERROR,
 					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 					 errmsg("timestamp out of range")));
+		}
 
 		result = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) - POSTGRES_EPOCH_JDATE;
 	}
 
-	PG_RETURN_DATEADT(result);
+	return result;
+}
+
+/* timestamptz_date()
+ * Convert timestamp with time zone to date data type.
+ */
+Datum
+timestamptz_date(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_DATEADT(timestamptz_date_opt_error(PG_GETARG_TIMESTAMP(0), NULL));
 }
 
 
@@ -1889,22 +1916,32 @@ overlaps_time(PG_FUNCTION_ARGS)
 /* timestamp_time()
  * Convert timestamp to time data type.
  */
-Datum
-timestamp_time(PG_FUNCTION_ARGS)
+TimeADT
+timestamp_time_opt_error(Timestamp timestamp, bool *isnull, bool *error)
 {
-	Timestamp	timestamp = PG_GETARG_TIMESTAMP(0);
 	TimeADT		result;
 	struct pg_tm tt,
 			   *tm = &tt;
 	fsec_t		fsec;
 
 	if (TIMESTAMP_NOT_FINITE(timestamp))
-		PG_RETURN_NULL();
+	{
+		*isnull = true;
+		return 0;
+	}
 
 	if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
+	{
+		if (error)
+		{
+			*error = *isnull = true;
+			return (Datum) 0;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("timestamp out of range")));
+	}
 
 	/*
 	 * Could also do this with time = (timestamp / USECS_PER_DAY *
@@ -1912,17 +1949,33 @@ timestamp_time(PG_FUNCTION_ARGS)
 	 */
 	result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
 			  USECS_PER_SEC) + fsec;
+	*isnull = false;
 
-	PG_RETURN_TIMEADT(result);
+	return result;
 }
 
-/* timestamptz_time()
- * Convert timestamptz to time data type.
+/* timestamp_time()
+ * Convert timestamp to time data type.
  */
 Datum
-timestamptz_time(PG_FUNCTION_ARGS)
+timestamp_time(PG_FUNCTION_ARGS)
+{
+	bool		isnull;
+	TimeADT		time =
+		timestamp_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL);
+
+	if (isnull)
+		PG_RETURN_NULL();
+	else
+		PG_RETURN_TIMEADT(time);
+}
+
+/* timestamptz_time_opt_error()
+ * Convert timestamptz to time data type.
+ */
+TimeADT
+timestamptz_time_opt_error(TimestampTz timestamp, bool *isnull, bool *error)
 {
-	TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
 	TimeADT		result;
 	struct pg_tm tt,
 			   *tm = &tt;
@@ -1930,12 +1983,23 @@ timestamptz_time(PG_FUNCTION_ARGS)
 	fsec_t		fsec;
 
 	if (TIMESTAMP_NOT_FINITE(timestamp))
-		PG_RETURN_NULL();
+	{
+		*isnull = true;
+		return 0;
+	}
 
 	if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+	{
+		if (error)
+		{
+			*error = *isnull = true;
+			return 0;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("timestamp out of range")));
+	}
 
 	/*
 	 * Could also do this with time = (timestamp / USECS_PER_DAY *
@@ -1943,8 +2007,25 @@ timestamptz_time(PG_FUNCTION_ARGS)
 	 */
 	result = ((((tm->tm_hour * MINS_PER_HOUR + tm->tm_min) * SECS_PER_MINUTE) + tm->tm_sec) *
 			  USECS_PER_SEC) + fsec;
+	*isnull = false;
 
-	PG_RETURN_TIMEADT(result);
+	return result;
+}
+
+/* timestamptz_time()
+ * Convert timestamptz to time data type.
+ */
+Datum
+timestamptz_time(PG_FUNCTION_ARGS)
+{
+	bool		isnull;
+	TimeADT		result =
+		timestamptz_time_opt_error(PG_GETARG_TIMESTAMP(0), &isnull, NULL);
+
+	if (isnull)
+		PG_RETURN_NULL();
+	else
+		PG_RETURN_TIMEADT(result);
 }
 
 /* datetime_timestamp()
@@ -2812,10 +2893,9 @@ time_timetz(PG_FUNCTION_ARGS)
 /* timestamptz_timetz()
  * Convert timestamp to timetz data type.
  */
-Datum
-timestamptz_timetz(PG_FUNCTION_ARGS)
+TimeTzADT *
+timestamptz_timetz_opt_error(TimestampTz timestamp, bool *error)
 {
-	TimestampTz timestamp = PG_GETARG_TIMESTAMP(0);
 	TimeTzADT  *result;
 	struct pg_tm tt,
 			   *tm = &tt;
@@ -2823,20 +2903,42 @@ timestamptz_timetz(PG_FUNCTION_ARGS)
 	fsec_t		fsec;
 
 	if (TIMESTAMP_NOT_FINITE(timestamp))
-		PG_RETURN_NULL();
+		return NULL;
 
 	if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+	{
+		if (error)
+		{
+			*error = true;
+			return NULL;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 				 errmsg("timestamp out of range")));
+	}
 
 	result = (TimeTzADT *) palloc(sizeof(TimeTzADT));
 
 	tm2timetz(tm, fsec, tz, result);
 
-	PG_RETURN_TIMETZADT_P(result);
+	return result;
 }
 
+/* timestamptz_timetz()
+ * Convert timestamp to timetz data type.
+ */
+Datum
+timestamptz_timetz(PG_FUNCTION_ARGS)
+{
+	TimeTzADT  *result =
+		timestamptz_timetz_opt_error(PG_GETARG_TIMESTAMP(0), NULL);
+
+	if (result)
+		PG_RETURN_TIMETZADT_P(result);
+	else
+		PG_RETURN_NULL();
+}
 
 /* datetimetz_timestamptz()
  * Convert date and timetz to timestamp with time zone data type.
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index f700c5b4c93..1da519daaef 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -46,7 +46,6 @@ typedef struct JsonbAggState
 	Oid			val_output_func;
 } JsonbAggState;
 
-static inline Datum jsonb_from_cstring(char *json, int len, bool unique_keys);
 static size_t checkStringLen(size_t len);
 static void jsonb_in_object_start(void *pstate);
 static void jsonb_in_object_end(void *pstate);
@@ -77,7 +76,7 @@ jsonb_in(PG_FUNCTION_ARGS)
 {
 	char	   *json = PG_GETARG_CSTRING(0);
 
-	return jsonb_from_cstring(json, strlen(json), false);
+	PG_RETURN_DATUM(jsonb_from_cstring(json, strlen(json), false, NULL));
 }
 
 /*
@@ -101,7 +100,7 @@ jsonb_recv(PG_FUNCTION_ARGS)
 	else
 		elog(ERROR, "unsupported jsonb version number %d", version);
 
-	return jsonb_from_cstring(str, nbytes, false);
+	return jsonb_from_cstring(str, nbytes, false, NULL);
 }
 
 /*
@@ -147,7 +146,7 @@ jsonb_from_text(text *js, bool unique_keys)
 {
 	return jsonb_from_cstring(VARDATA_ANY(js),
 							  VARSIZE_ANY_EXHDR(js),
-							  unique_keys);
+							  unique_keys, NULL);
 }
 
 /*
@@ -239,12 +238,13 @@ jsonb_typeof(PG_FUNCTION_ARGS)
  *
  * Uses the json parser (with hooks) to construct a jsonb.
  */
-static inline Datum
-jsonb_from_cstring(char *json, int len, bool unique_keys)
+Datum
+jsonb_from_cstring(char *json, int len, bool unique_keys, bool *error)
 {
 	JsonLexContext *lex;
 	JsonbInState state;
 	JsonSemAction sem;
+	JsonParseErrorType result;
 
 	memset(&state, 0, sizeof(state));
 	memset(&sem, 0, sizeof(sem));
@@ -261,10 +261,20 @@ jsonb_from_cstring(char *json, int len, bool unique_keys)
 	sem.scalar = jsonb_in_scalar;
 	sem.object_field_start = jsonb_in_object_field_start;
 
-	pg_parse_json_or_ereport(lex, &sem);
+	result = pg_parse_json(lex, &sem);
+	if (result != JSON_SUCCESS)
+	{
+		if (error)
+		{
+			*error = true;
+			return (Datum) 0;
+		}
+
+		json_ereport_error(result, lex);
+	}
 
 	/* after parsing, the item member has the composed jsonb structure */
-	PG_RETURN_POINTER(JsonbValueToJsonb(state.res));
+	return JsonbPGetDatum(JsonbValueToJsonb(state.res));
 }
 
 static size_t
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 920a63b0081..98b1a9ac9ee 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4308,15 +4308,20 @@ int8_numeric(PG_FUNCTION_ARGS)
 }
 
 
-Datum
-numeric_int8(PG_FUNCTION_ARGS)
+int64
+numeric_int8_opt_error(Numeric num, bool *error)
 {
-	Numeric		num = PG_GETARG_NUMERIC(0);
 	NumericVar	x;
 	int64		result;
 
 	if (NUMERIC_IS_SPECIAL(num))
 	{
+		if (error)
+		{
+			*error = true;
+			return 0;
+		}
+
 		if (NUMERIC_IS_NAN(num))
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4331,13 +4336,26 @@ numeric_int8(PG_FUNCTION_ARGS)
 	init_var_from_num(num, &x);
 
 	if (!numericvar_to_int64(&x, &result))
+	{
+		if (error)
+		{
+			*error = true;
+			return 0;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
 				 errmsg("bigint out of range")));
+	}
 
-	PG_RETURN_INT64(result);
+	return result;
 }
 
+Datum
+numeric_int8(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT64(numeric_int8_opt_error(PG_GETARG_NUMERIC(0), NULL));
+}
 
 Datum
 int2_numeric(PG_FUNCTION_ARGS)
@@ -4347,17 +4365,20 @@ int2_numeric(PG_FUNCTION_ARGS)
 	PG_RETURN_NUMERIC(int64_to_numeric(val));
 }
 
-
-Datum
-numeric_int2(PG_FUNCTION_ARGS)
+int16
+numeric_int2_opt_error(Numeric num, bool *error)
 {
-	Numeric		num = PG_GETARG_NUMERIC(0);
 	NumericVar	x;
 	int64		val;
-	int16		result;
 
 	if (NUMERIC_IS_SPECIAL(num))
 	{
+		if (error)
+		{
+			*error = true;
+			return 0;
+		}
+
 		if (NUMERIC_IS_NAN(num))
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -4372,21 +4393,40 @@ numeric_int2(PG_FUNCTION_ARGS)
 	init_var_from_num(num, &x);
 
 	if (!numericvar_to_int64(&x, &val))
+	{
+		if (error)
+		{
+			*error = true;
+			return 0;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
 				 errmsg("smallint out of range")));
+	}
 
 	if (unlikely(val < PG_INT16_MIN) || unlikely(val > PG_INT16_MAX))
+	{
+		if (error)
+		{
+			*error = true;
+			return 0;
+		}
+
 		ereport(ERROR,
 				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
 				 errmsg("smallint out of range")));
+	}
 
 	/* Down-convert to int2 */
-	result = (int16) val;
-
-	PG_RETURN_INT16(result);
+	return (int16) val;
 }
 
+Datum
+numeric_int2(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_INT16(numeric_int2_opt_error(PG_GETARG_NUMERIC(0), NULL));
+}
 
 Datum
 float8_numeric(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/adt/numutils.c b/src/backend/utils/adt/numutils.c
index cc3f95d3990..b82b30f9013 100644
--- a/src/backend/utils/adt/numutils.c
+++ b/src/backend/utils/adt/numutils.c
@@ -248,7 +248,7 @@ invalid_syntax:
  * positive number.
  */
 int64
-pg_strtoint64(const char *s)
+pg_strtoint64_opt_error(const char *s, bool *error)
 {
 	const char *ptr = s;
 	int64		tmp = 0;
@@ -307,12 +307,24 @@ pg_strtoint64(const char *s)
 	return tmp;
 
 out_of_range:
+	if (error)
+	{
+		*error = true;
+		return 0;
+	}
+
 	ereport(ERROR,
 			(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
 			 errmsg("value \"%s\" is out of range for type %s",
 					s, "bigint")));
 
 invalid_syntax:
+	if (error)
+	{
+		*error = true;
+		return 0;
+	}
+
 	ereport(ERROR,
 			(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
 			 errmsg("invalid input syntax for type %s: \"%s\"",
@@ -321,6 +333,12 @@ invalid_syntax:
 	return 0;					/* keep compiler quiet */
 }
 
+int64
+pg_strtoint64(const char *s)
+{
+	return pg_strtoint64_opt_error(s, NULL);
+}
+
 /*
  * pg_itoa: converts a signed 16-bit integer to its string representation
  * and returns strlen(a).
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 49cdb290ac2..889d3476e56 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5614,8 +5614,8 @@ timestamptz_timestamp(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMP(timestamptz2timestamp(timestamp));
 }
 
-static Timestamp
-timestamptz2timestamp(TimestampTz timestamp)
+Timestamp
+timestamptz2timestamp_opt_error(TimestampTz timestamp, bool *error)
 {
 	Timestamp	result;
 	struct pg_tm tt,
@@ -5628,17 +5628,40 @@ timestamptz2timestamp(TimestampTz timestamp)
 	else
 	{
 		if (timestamp2tm(timestamp, &tz, tm, &fsec, NULL, NULL) != 0)
+		{
+			if (error)
+			{
+				*error = true;
+				return 0;
+			}
+
 			ereport(ERROR,
 					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 					 errmsg("timestamp out of range")));
+		}
+
 		if (tm2timestamp(tm, fsec, NULL, &result) != 0)
+		{
+			if (error)
+			{
+				*error = true;
+				return 0;
+			}
+
 			ereport(ERROR,
 					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 					 errmsg("timestamp out of range")));
+		}
 	}
 	return result;
 }
 
+static Timestamp
+timestamptz2timestamp(TimestampTz timestamp)
+{
+	return timestamptz2timestamp_opt_error(timestamp, NULL);
+}
+
 /* timestamptz_zone()
  * Evaluate timestamp with time zone type at the specified time zone.
  * Returns a timestamp without time zone.
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index c8ef917ffe0..adabf57c97b 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -762,43 +762,14 @@ typedef struct JsonExprState
 {
 	JsonExpr   *jsexpr;			/* original expression node */
 
-	struct
-	{
-		FmgrInfo	func;		/* typinput function for output type */
-		Oid			typioparam;
-	}			input;			/* I/O info for output type */
-
 	NullableDatum
-			   *formatted_expr, /* formatted context item value */
-			   *res_expr,		/* result item */
-			   *coercion_expr,	/* input for JSON item coercion */
-			   *pathspec;		/* path specification value */
+				formatted_expr,	/* formatted context item value */
+				coercion_expr,	/* input for JSON item coercion */
+				pathspec;		/* path specification value */
 
-	ExprState  *result_expr;	/* coerced to output type */
 	ExprState  *default_on_empty;	/* ON EMPTY DEFAULT expression */
 	ExprState  *default_on_error;	/* ON ERROR DEFAULT expression */
 	List	   *args;			/* passing arguments */
-
-	void	   *cache;			/* cache for json_populate_type() */
-
-	struct JsonCoercionsState
-	{
-		struct JsonCoercionState
-		{
-			JsonCoercion *coercion; /* coercion expression */
-			ExprState  *estate; /* coercion expression state */
-		}			null,
-					string,
-		numeric    ,
-					boolean,
-					date,
-					time,
-					timetz,
-					timestamp,
-					timestamptz,
-					composite;
-	}			coercions;		/* states for coercion from SQL/JSON item
-								 * types directly to the output type */
 } JsonExprState;
 
 /* functions in execExpr.c */
@@ -860,18 +831,8 @@ extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
 						   ExprContext *econtext, TupleTableSlot *slot);
 extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op,
 									ExprContext *econtext);
-extern void ExecEvalJson(ExprState *state, ExprEvalStep *op,
-						 ExprContext *econtext);
-extern Datum ExecPrepareJsonItemCoercion(struct JsonbValue *item,
-										 JsonReturning *returning,
-										 struct JsonCoercionsState *coercions,
-										 struct JsonCoercionState **pjcstate);
-extern bool ExecEvalJsonNeedsSubTransaction(JsonExpr *jsexpr,
-											struct JsonCoercionsState *);
-extern Datum ExecEvalExprPassingCaseValue(ExprState *estate,
-										  ExprContext *econtext, bool *isnull,
-										  Datum caseval_datum,
-										  bool caseval_isnull);
+extern void ExecEvalJsonExpr(ExprState *state, ExprEvalStep *op,
+							 ExprContext *econtext);
 
 extern void ExecAggInitGroup(AggState *aggstate, AggStatePerTrans pertrans, AggStatePerGroup pergroup,
 							 ExprContext *aggcontext);
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 3aa96bb6855..14fbb32ce9c 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1551,39 +1551,6 @@ typedef struct JsonBehavior
 	Node	   *default_expr;	/* default expression, if any */
 } JsonBehavior;
 
-/*
- * JsonCoercion -
- *		coercion from SQL/JSON item types to SQL types
- */
-typedef struct JsonCoercion
-{
-	NodeTag		type;
-	Node	   *expr;			/* resulting expression coerced to target type */
-	bool		via_populate;	/* coerce result using json_populate_type()? */
-	bool		via_io;			/* coerce result using type input function? */
-	Oid			collation;		/* collation for coercion via I/O or populate */
-} JsonCoercion;
-
-/*
- * JsonItemCoercions -
- *		expressions for coercion from SQL/JSON item types directly to the
- *		output SQL type
- */
-typedef struct JsonItemCoercions
-{
-	NodeTag		type;
-	JsonCoercion *null;
-	JsonCoercion *string;
-	JsonCoercion *numeric;
-	JsonCoercion *boolean;
-	JsonCoercion *date;
-	JsonCoercion *time;
-	JsonCoercion *timetz;
-	JsonCoercion *timestamp;
-	JsonCoercion *timestamptz;
-	JsonCoercion *composite;	/* arrays and objects */
-} JsonItemCoercions;
-
 /*
  * JsonExpr -
  *		transformed representation of JSON_VALUE(), JSON_QUERY(), JSON_EXISTS()
@@ -1593,7 +1560,6 @@ typedef struct JsonExpr
 	Expr		xpr;
 	JsonExprOp	op;				/* json function ID */
 	Node	   *formatted_expr; /* formatted context item expression */
-	JsonCoercion *result_coercion;	/* resulting coercion to RETURNING type */
 	JsonFormat *format;			/* context item format (JSON/JSONB) */
 	Node	   *path_spec;		/* JSON path specification expression */
 	List	   *passing_names;	/* PASSING argument names */
@@ -1601,8 +1567,8 @@ typedef struct JsonExpr
 	JsonReturning *returning;	/* RETURNING clause type/format info */
 	JsonBehavior *on_empty;		/* ON EMPTY behavior */
 	JsonBehavior *on_error;		/* ON ERROR behavior */
-	JsonItemCoercions *coercions;	/* coercions for JSON_VALUE */
 	JsonWrapper wrapper;		/* WRAPPER for JSON_QUERY */
+	Oid			collation;		/* OID of collation, or InvalidOid if none */
 	bool		omit_quotes;	/* KEEP/OMIT QUOTES for JSON_QUERY */
 	int			location;		/* token location, or -1 if unknown */
 } JsonExpr;
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 221c3e6c3de..5e1aa37c1f5 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -24,6 +24,7 @@
 /* bool.c */
 extern bool parse_bool(const char *value, bool *result);
 extern bool parse_bool_with_len(const char *value, size_t len, bool *result);
+extern bool boolin_opt_error(const char *value, size_t	len, bool *error);
 
 /* domains.c */
 extern void domain_check(Datum value, bool isnull, Oid domainType,
@@ -46,6 +47,7 @@ extern int	namestrcmp(Name name, const char *str);
 extern int16 pg_strtoint16(const char *s);
 extern int32 pg_strtoint32(const char *s);
 extern int64 pg_strtoint64(const char *s);
+extern int64 pg_strtoint64_opt_error(const char *s, bool *error);
 extern int	pg_itoa(int16 i, char *a);
 extern int	pg_ultoa_n(uint32 l, char *a);
 extern int	pg_ulltoa_n(uint64 l, char *a);
diff --git a/src/include/utils/date.h b/src/include/utils/date.h
index 91ae24254df..9059f2b5ce7 100644
--- a/src/include/utils/date.h
+++ b/src/include/utils/date.h
@@ -87,4 +87,14 @@ extern bool time_overflows(int hour, int min, int sec, fsec_t fsec);
 extern bool float_time_overflows(int hour, int min, double sec);
 extern void AdjustTimeForTypmod(TimeADT *time, int32 typmod);
 
+extern DateADT timestamp_date_opt_error(Timestamp timestamp, bool *error);
+extern TimeADT timestamp_time_opt_error(Timestamp timestamp,
+										bool *isnull, bool *error);
+
+extern DateADT timestamptz_date_opt_error(Timestamp timestamp, bool *error);
+extern TimeADT timestamptz_time_opt_error(TimestampTz timestamp,
+										  bool *isnull, bool *error);
+extern TimeTzADT *timestamptz_timetz_opt_error(TimestampTz timestamp,
+											   bool *error);
+
 #endif							/* DATE_H */
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index bae466b5234..d5574faa79e 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -420,6 +420,8 @@ extern void JsonbHashScalarValueExtended(const JsonbValue *scalarVal,
 
 /* jsonb.c support functions */
 extern Datum jsonb_from_text(text *js, bool unique_keys);
+extern Datum jsonb_from_cstring(char *json, int len, bool unique_keys,
+								bool *error);
 extern char *JsonbToCString(StringInfo out, JsonbContainer *in,
 							int estimated_len);
 extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 3caa74dfe7a..ede68ab81b8 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -85,6 +85,8 @@ extern Numeric numeric_div_opt_error(Numeric num1, Numeric num2,
 									 bool *have_error);
 extern Numeric numeric_mod_opt_error(Numeric num1, Numeric num2,
 									 bool *have_error);
-extern int32 numeric_int4_opt_error(Numeric num, bool *error);
+extern int16 numeric_int2_opt_error(Numeric num, bool *have_error);
+extern int32 numeric_int4_opt_error(Numeric num, bool *have_error);
+extern int64 numeric_int8_opt_error(Numeric num, bool *have_error);
 
 #endif							/* _PG_NUMERIC_H_ */
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index edf3a973186..f9382d7193f 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -102,6 +102,8 @@ extern int	timestamp_cmp_internal(Timestamp dt1, Timestamp dt2);
 
 extern TimestampTz timestamp2timestamptz_opt_overflow(Timestamp timestamp,
 													  int *overflow);
+extern Timestamp timestamptz2timestamp_opt_error(TimestampTz timestamp,
+												 bool *error);
 extern int32 timestamp_cmp_timestamptz_internal(Timestamp timestampVal,
 												TimestampTz dt2);
 
diff --git a/src/test/regress/expected/jsonb_sqljson.out b/src/test/regress/expected/jsonb_sqljson.out
index ef496110af3..0d0705e6a8f 100644
--- a/src/test/regress/expected/jsonb_sqljson.out
+++ b/src/test/regress/expected/jsonb_sqljson.out
@@ -184,11 +184,11 @@ SELECT JSON_EXISTS(jsonb '1', 'strict $[1]' RETURNING text FALSE ON ERROR);
 (1 row)
 
 SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
-ERROR:  cannot cast type boolean to jsonb
+ERROR:  returning type jsonb is not supported in JSON_EXISTS()
 LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING jsonb);
                ^
 SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
-ERROR:  cannot cast type boolean to real
+ERROR:  returning type real is not supported in JSON_EXISTS()
 LINE 1: SELECT JSON_EXISTS(jsonb '1', '$[0]' RETURNING float4);
                ^
 -- JSON_VALUE
@@ -242,7 +242,9 @@ SELECT JSON_VALUE(jsonb '123', '$' RETURNING text);
 
 /* jsonb bytea ??? */
 SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ERROR);
-ERROR:  SQL/JSON item cannot be cast to target type
+ERROR:  returning type bytea is not supported in JSON_VALUE()
+LINE 2: SELECT JSON_VALUE(jsonb '123', '$' RETURNING bytea ERROR ON ...
+               ^
 SELECT JSON_VALUE(jsonb '1.23', '$');
  json_value 
 ------------
@@ -349,14 +351,213 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
  03-01-2017
 (1 row)
 
--- Test NULL checks execution in domain types
+-- Test for domain types
 CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_no...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb...
+               ^
+-- Test NULL checks execution in domain types
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_no...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_n...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_n...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_json_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no...
+               ^
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+ERROR:  returning type sqljsonb_jsonb_not_null is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_no...
+               ^
+-- Test returning of non-scalar items
 SELECT JSON_VALUE(jsonb '[]', '$');
  json_value 
 ------------
@@ -478,11 +679,9 @@ SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a);
 (1 row)
 
 SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )' AS a RETURNING point);
- json_value 
-------------
- (1,2)
-(1 row)
-
+ERROR:  returning type point is not supported in JSON_VALUE()
+LINE 1: SELECT JSON_VALUE(jsonb 'null', '$a' PASSING point ' (1, 2 )...
+               ^
 -- Test timestamptz passing and output
 SELECT JSON_VALUE(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
           json_value          
@@ -852,49 +1051,35 @@ FROM
 CREATE TYPE sqljsonb_rec AS (a int, t text, js json, jb jsonb, jsa json[]);
 CREATE TYPE sqljsonb_reca AS (reca sqljsonb_rec[]);
 SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "js": [1, "2", {}], "jb": {"x": [1, "2", {}]}},  {"a": 2}]', '$[0]' RETURNING sqljsonb_rec);
-                     json_query                      
------------------------------------------------------
- (1,aaa,"[1, ""2"", {}]","{""x"": [1, ""2"", {}]}",)
-(1 row)
-
+ERROR:  returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[{"a": 1, "b": "foo", "t": "aaa", "...
+               ^
 SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa":  [{"a": 1, "b": ["foo"]}, {"a": 2, "c": {}}, 123]}', '$' RETURNING sqljsonb_rec)).jsa);
-         unnest         
-------------------------
- {"a": 1, "b": ["foo"]}
- {"a": 2, "c": {}}
- 123
-(3 rows)
-
+ERROR:  returning type sqljsonb_rec is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"jsa":  [{"a": 1, "...
+                              ^
 SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]}', '$' RETURNING sqljsonb_reca)).reca);
- a |      t      | js |     jb     | jsa 
----+-------------+----+------------+-----
- 1 | ["foo", []] |    |            | 
- 2 |             |    | [{}, true] | 
-(2 rows)
-
+ERROR:  returning type sqljsonb_reca is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest((JSON_QUERY(jsonb '{"reca": [{"a": 1, "...
+                              ^
 -- Extension: array types returning
 SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING int[] WITH WRAPPER);
-  json_query  
---------------
- {1,2,NULL,3}
-(1 row)
-
+ERROR:  returning type integer[] is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '[1,2,null,"3"]', '$[*]' RETURNING i...
+               ^
 SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo", []]}, {"a": 2, "jb": [{}, true]}]', '$' RETURNING sqljsonb_rec[]));
- a |      t      | js |     jb     | jsa 
----+-------------+----+------------+-----
- 1 | ["foo", []] |    |            | 
- 2 |             |    | [{}, true] | 
-(2 rows)
-
+ERROR:  returning type sqljsonb_rec[] is not supported in JSON_QUERY()
+LINE 1: SELECT * FROM unnest(JSON_QUERY(jsonb '[{"a": 1, "t": ["foo"...
+                             ^
 -- Extension: domain types returning
 SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
- json_query 
-------------
-          1
-(1 row)
-
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb...
+               ^
 SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
-ERROR:  domain sqljsonb_int_not_null does not allow null values
+ERROR:  returning type sqljsonb_int_not_null is not supported in JSON_QUERY()
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb...
+               ^
 -- Test timestamptz passing and output
 SELECT JSON_QUERY(jsonb 'null', '$ts' PASSING timestamptz '2018-02-21 12:34:56 +10' AS ts);
          json_query          
@@ -1048,7 +1233,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
 (1 row)
 
 -- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
 SELECT *
 FROM
 	(VALUES
@@ -1069,7 +1253,6 @@ FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -1085,10 +1268,7 @@ FROM
 			exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR,
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
-			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$'
+			jsb2q jsonb PATH '$' OMIT QUOTES
 		)
 	) jt
 	ON true;
@@ -1123,7 +1303,6 @@ SELECT * FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -1139,9 +1318,6 @@ SELECT * FROM
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
 			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$',
 			NESTED PATH '$[1]' AS p1 COLUMNS (
 				a1 int,
 				NESTED PATH '$[*]' AS "p1 1" COLUMNS (
@@ -1168,7 +1344,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
     "json_table"."char(4)",
     "json_table".bool,
     "json_table"."numeric",
-    "json_table".domain,
     "json_table".js,
     "json_table".jb,
     "json_table".jst,
@@ -1184,9 +1359,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
     "json_table".js2,
     "json_table".jsb2w,
     "json_table".jsb2q,
-    "json_table".ia,
-    "json_table".ta,
-    "json_table".jba,
     "json_table".a1,
     "json_table".b1,
     "json_table".a11,
@@ -1205,7 +1377,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
                 "char(4)" character(4) PATH '$',
                 bool boolean PATH '$',
                 "numeric" numeric PATH '$',
-                domain jsonb_test_domain PATH '$',
                 js json PATH '$',
                 jb jsonb PATH '$',
                 jst text FORMAT JSON PATH '$',
@@ -1221,9 +1392,6 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
                 js2 json PATH '$',
                 jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER,
                 jsb2q jsonb PATH '$' OMIT QUOTES,
-                ia integer[] PATH '$',
-                ta text[] PATH '$',
-                jba jsonb[] PATH '$',
                 NESTED PATH '$[1]' AS p1
                 COLUMNS (
                     a1 integer PATH '$."a1"',
@@ -1248,15 +1416,14 @@ CREATE OR REPLACE VIEW public.jsonb_table_view AS
             PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22))))
         )
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
-                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Table Function Scan on "json_table"
-   Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain, "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
-   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, ia integer[] PATH '$', ta text[] PATH '$', jba jsonb[] PATH '$', NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
+   Output: "json_table".id, "json_table".id2, "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv, "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1, "json_table".exists1, "json_table".exists2, "json_table".exists3, "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".a1, "json_table".b1, "json_table".a11, "json_table".a21, "json_table".a22
+   Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_1 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, "int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', js json PATH '$', jb jsonb PATH '$', jst text FORMAT JSON PATH '$', jsc character(4) FORMAT JSON PATH '$', jsv character varying(4) FORMAT JSON PATH '$', jsb jsonb PATH '$', jsbq jsonb PATH '$' OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"', exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR, js2 json PATH '$', jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER, jsb2q jsonb PATH '$' OMIT QUOTES, NESTED PATH '$[1]' AS p1 COLUMNS (a1 integer PATH '$."a1"', b1 text PATH '$."b1"', NESTED PATH '$[*]' AS "p1 1" COLUMNS (a11 text PATH '$."a11"')), NESTED PATH '$[2]' AS p2 COLUMNS ( NESTED PATH '$[*]' AS "p2:1" COLUMNS (a21 text PATH '$."a21"'), NESTED PATH '$[*]' AS p22 COLUMNS (a22 text PATH '$."a22"'))) PLAN (json_table_path_1 OUTER ((p1 OUTER "p1 1") UNION (p2 OUTER ("p2:1" UNION p22)))))
 (3 rows)
 
 DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
 -- JSON_TABLE: ON EMPTY/ON ERROR behavior
 SELECT *
 FROM
@@ -1318,15 +1485,15 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a'));
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to smallint
+ERROR:  returning type smallint is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to bigint
+ERROR:  returning type bigint is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to real
+ERROR:  returning type real is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 E...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
@@ -1336,11 +1503,11 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'))
 (1 row)
 
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to json
+ERROR:  returning type json is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXI...
                                                              ^
 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
-ERROR:  cannot cast type boolean to jsonb
+ERROR:  returning type jsonb is not supported in JSON_EXISTS()
 LINE 1: ...ELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EX...
                                                              ^
 -- JSON_TABLE: nested paths and plans
@@ -2102,11 +2269,14 @@ set parallel_leader_participation = off;
 -- Should be non-parallel due to subtransactions
 EXPLAIN (COSTS OFF)
 SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
-                 QUERY PLAN                  
----------------------------------------------
- Aggregate
-   ->  Seq Scan on test_parallel_jsonb_value
-(2 rows)
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Finalize Aggregate
+   ->  Gather
+         Workers Planned: 4
+         ->  Partial Aggregate
+               ->  Parallel Seq Scan on test_parallel_jsonb_value
+(5 rows)
 
 SELECT sum(JSON_VALUE(js, '$' RETURNING numeric)) FROM test_parallel_jsonb_value;
     sum     
diff --git a/src/test/regress/sql/jsonb_sqljson.sql b/src/test/regress/sql/jsonb_sqljson.sql
index fff25374808..e74a4a5dbbe 100644
--- a/src/test/regress/sql/jsonb_sqljson.sql
+++ b/src/test/regress/sql/jsonb_sqljson.sql
@@ -82,12 +82,73 @@ SELECT JSON_VALUE(jsonb '"123"', '$' RETURNING int) + 234;
 
 SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
 
--- Test NULL checks execution in domain types
+-- Test for domain types
 CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
+CREATE DOMAIN sqljsonb_json_not_null AS json NOT NULL CHECK (VALUE::text <> '0');
+CREATE DOMAIN sqljsonb_jsonb_not_null AS jsonb NOT NULL CHECK (VALUE <> '0');
+
+-- Test casting to json[b] domains (items casted as is, strings are not unquoted)
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '"1"', '$' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '1', '$' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb 'true', '$' RETURNING sqljsonb_jsonb_not_null);
+
+-- Test NULL checks execution in domain types
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON EMPTY DEFAULT 2 ON ERROR);
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null NULL ON ERROR);
 SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
 
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON EMPTY DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb '1', '$.a' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON EMPTY);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_json_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON EMPTY);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null NULL ON ERROR);
+SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT NULL ON ERROR);
+
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null);
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_json_not_null DEFAULT '2' ON ERROR);
+
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null);
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null ERROR ON ERROR);
+SELECT JSON_VALUE(jsonb '0', '$' RETURNING sqljsonb_jsonb_not_null DEFAULT '2' ON ERROR);
+
+-- Test returning of non-scalar items
 SELECT JSON_VALUE(jsonb '[]', '$');
 SELECT JSON_VALUE(jsonb '[]', '$' ERROR ON ERROR);
 SELECT JSON_VALUE(jsonb '{}', '$');
@@ -338,8 +399,6 @@ SELECT * FROM JSON_TABLE(jsonb '123', '$'
 	COLUMNS (item int PATH '$', foo int)) bar;
 
 -- JSON_TABLE: basic functionality
-CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
-
 SELECT *
 FROM
 	(VALUES
@@ -360,7 +419,6 @@ FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -377,10 +435,7 @@ FROM
 
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
-			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$'
+			jsb2q jsonb PATH '$' OMIT QUOTES
 		)
 	) jt
 	ON true;
@@ -399,7 +454,6 @@ SELECT * FROM
 			"char(4)" char(4) PATH '$',
 			"bool" bool PATH '$',
 			"numeric" numeric PATH '$',
-			"domain" jsonb_test_domain PATH '$',
 			js json PATH '$',
 			jb jsonb PATH '$',
 			jst text    FORMAT JSON  PATH '$',
@@ -416,9 +470,6 @@ SELECT * FROM
 			js2 json PATH '$',
 			jsb2w jsonb PATH '$' WITH WRAPPER,
 			jsb2q jsonb PATH '$' OMIT QUOTES,
-			ia int[] PATH '$',
-			ta text[] PATH '$',
-			jba jsonb[] PATH '$',
 
 			NESTED PATH '$[1]' AS p1 COLUMNS (
 				a1 int,
@@ -443,7 +494,6 @@ SELECT * FROM
 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view;
 
 DROP VIEW jsonb_table_view;
-DROP DOMAIN jsonb_test_domain;
 
 -- JSON_TABLE: ON EMPTY/ON ERROR behavior
 SELECT *
-- 
2.17.1

view thread (95+ messages)  latest in thread

Message-ID: <3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru>
Permalink:  ../3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru/
Also on:    postgresql.org/message-id/3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru

copy link · copy postgr.es · copy git am

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: n.gluhov@postgrespro.ru, andrew@dunslane.net, jkatz@postgresql.org, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, andres@anarazel.de, pgsql-hackers@lists.postgresql.org, michael@paquier.xyz, john.naylor@enterprisedb.com, amitlangote09@gmail.com
  Subject: Re: SQL/JSON features for v15
  In-Reply-To: <3f8d4c2b-1e58-bf19-580e-64e9755897ef@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox