From 5845f2788bd9d395da7977ceb3dcc2ef4d37e91b Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Wed, 5 Jun 2024 09:13:15 +0900 Subject: [PATCH v2] SQL/JSON: Use implicit casts for RETURNING type with typmod This fixes things so that the various SQL/JSON functions that support specifying the type of the returned value using the RETURNING clause perform an implicit cast to the target type at least in the cases where the target type also specifies a length limit. That is to ensure that the output values (including those specified in the DEFAULT .. ON ERROR) that exceed the length produce an error instead of being silently truncated to the specified length, which is a behavior that agrees with what the SQL standard. Reported-by: Alvaro Herrera Discussion: https://postgr.es/m/202405271326.5a5rprki64aw%40alvherre.pgsql --- src/backend/parser/parse_expr.c | 60 +++++++++++++++++-- src/test/regress/expected/sqljson.out | 17 ++++++ .../regress/expected/sqljson_jsontable.out | 26 ++++---- .../regress/expected/sqljson_queryfuncs.out | 44 ++++++++------ src/test/regress/sql/sqljson.sql | 12 ++++ src/test/regress/sql/sqljson_jsontable.sql | 2 +- src/test/regress/sql/sqljson_queryfuncs.sql | 18 ++++-- 7 files changed, 136 insertions(+), 43 deletions(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 3d79d17125..18d0adb64d 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -3583,6 +3583,7 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr, Node *res; int location; Oid exprtype = exprType(expr); + int32 baseTypmod = returning->typmod; /* if output type is not specified or equals to function type, return */ if (!OidIsValid(returning->typid) || returning->typid == exprtype) @@ -3611,10 +3612,19 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr, return (Node *) fexpr; } + /* + * For domains, consider the base type's typmod to decide whether to setup + * an implicit or explicit cast. + */ + if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) + (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); + /* try to coerce expression to the output type */ res = coerce_to_target_type(pstate, expr, exprtype, - returning->typid, returning->typmod, + returning->typid, baseTypmod, + baseTypmod > 0 ? COERCION_IMPLICIT : COERCION_EXPLICIT, + baseTypmod > 0 ? COERCE_IMPLICIT_CAST : COERCE_EXPLICIT_CAST, location); @@ -3640,6 +3650,7 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type, JsonConstructorExpr *jsctor = makeNode(JsonConstructorExpr); Node *placeholder; Node *coercion; + int32 baseTypmod = returning->typmod; jsctor->args = args; jsctor->func = fexpr; @@ -3677,6 +3688,17 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type, placeholder = (Node *) cte; } + /* + * Convert the source expression to text, because coerceJsonFuncExpr() will + * create an implicit cast to the RETURNING types with typmod and there are + * no implicit casts from json(b) to such types. For domains, the base + * type's typmod will be considered, so do so here too. + */ + if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) + (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); + if (baseTypmod > 0) + placeholder = coerce_to_specific_type(pstate, placeholder, TEXTOID, + "JSON_CONSTRUCTOR()"); coercion = coerceJsonFuncExpr(pstate, placeholder, returning, true); if (coercion != placeholder) @@ -4593,10 +4615,9 @@ coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr) if (jsexpr->op == JSON_VALUE_OP) { /* - * Use cast expressions for types with typmod and domain types. + * Use cast expression for domain types; we need CoerceToDomain here. */ - if (returning->typmod == -1 && - get_typtype(returning->typid) != TYPTYPE_DOMAIN) + if (get_typtype(returning->typid) != TYPTYPE_DOMAIN) { jsexpr->use_io_coercion = true; return; @@ -4628,6 +4649,20 @@ coerceJsonExprOutput(ParseState *pstate, JsonExpr *jsexpr) default: break; } + + /* + * coerceJsonFuncExpr() creates implicit casts for types with typmod, + * which (if present) don't handle errors softly, so use runtime + * coercion. + */ + if (returning->typmod > 0) + { + if (jsexpr->omit_quotes) + jsexpr->use_io_coercion = true; + else + jsexpr->use_json_coercion = true; + return; + } } /* Look up a cast expression. */ @@ -4776,11 +4811,24 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior, btype == default_behavior)) coerce_at_runtime = true; else + { + int32 baseTypmod = returning->typmod; + + if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) + (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); + + if (baseTypmod > 0) + expr = coerce_to_specific_type(pstate, expr, TEXTOID, + "JSON_FUNCTION()"); coerced_expr = coerce_to_target_type(pstate, expr, exprType(expr), - returning->typid, returning->typmod, - COERCION_EXPLICIT, COERCE_EXPLICIT_CAST, + returning->typid, baseTypmod, + baseTypmod > 0 ? COERCION_IMPLICIT : + COERCION_EXPLICIT, + baseTypmod > 0 ? COERCE_IMPLICIT_CAST : + COERCE_EXPLICIT_CAST, exprLocation((Node *) behavior)); + } if (coerced_expr == NULL) ereport(ERROR, diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index cbf8542d8d..4f91e2117e 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1278,3 +1278,20 @@ CREATE OR REPLACE VIEW public.is_json_view AS '{}'::text IS JSON OBJECT WITH UNIQUE KEYS AS object FROM generate_series(1, 3) i(i) DROP VIEW is_json_view; +-- Test implicit coercion to a fixed-length type specified in RETURNING +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2)); +ERROR: value too long for type character varying(2) +SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2))); +ERROR: value too long for type character varying(2) +SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2))); +ERROR: value too long for type character varying(2) +SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i; +ERROR: value too long for type character varying(2) +SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i; +ERROR: value too long for type character varying(2) +-- Now try domain over fixed-length type +CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2); +ERROR: value too long for type character(2) +SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2); +ERROR: value for domain sqljson_char2 violates check constraint "sqljson_char2_check" diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index eace29efbf..327eb10e02 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -103,14 +103,14 @@ FROM json_table_test vals [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2" - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | aaaaaaa | aaaa | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | aaaaaaa | | | | aaaaaaa | "aaaaaaa" | "aaaaaaa" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | | foo | foo | | | | "foo" | "foo" [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | | | | | | | null | null [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | | f | f | f | | f | false | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | | t | t | t | | t | true | true [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | | | | | | | {"aaa": 123} | {"aaa": 123} - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | [1,2] | [1,2 | | | [1,2] | "[1,2]" | "[1,2]" - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | "str" | "str | | | "str" | "\"str\"" | "\"str\"" + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | [1,2] | | | | [1,2] | "[1,2]" | "[1,2]" + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | "str" | | | | "str" | "\"str\"" | "\"str\"" (14 rows) -- "formatted" columns @@ -137,14 +137,14 @@ FROM json_table_test vals [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | 1 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | "2" | "2" | "2" | "2" | 2 - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | "aaaaaaa" | "aaa | "aaa | "aaaaaaa" | - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | "foo" | "foo | "foo | "foo" | + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | "aaaaaaa" | | | "aaaaaaa" | + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | "foo" | | | "foo" | [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | null | null | null | null | null - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | false | fals | fals | false | false + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | false | | | false | false [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | true | true | true | true | true - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | {"aaa": 123} | {"aa | {"aa | {"aaa": 123} | {"aaa": 123} - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]" | "[1, | "[1, | "[1,2]" | [1, 2] - [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\"" | "\"s | "\"s | "\"str\"" | "str" + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | {"aaa": 123} | | | {"aaa": 123} | {"aaa": 123} + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]" | | | "[1,2]" | [1, 2] + [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\"" | | | "\"str\"" | "str" (14 rows) -- EXISTS columns @@ -566,10 +566,10 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); ERROR: cannot cast type boolean to real 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')); - a ------ - fal +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a')); + a +------- + false (1 row) SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index 0c4b62b0bb..35ce994ef3 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -234,10 +234,18 @@ SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5)); aaa (1 row) +SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2) ERROR ON ERROR); +ERROR: value too long for type character(2) SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2)); json_value ------------ - aa + +(1 row) + +SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR); + json_value +------------ + aaa (1 row) SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json); @@ -636,30 +644,28 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERR (1 row) -- Behavior when a RETURNING type has typmod != -1 -SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2)); +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR); +ERROR: value too long for type character(3) +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3)); json_query ------------ - "a + (1 row) -SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2) OMIT QUOTES); +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) OMIT QUOTES ERROR ON ERROR); json_query ------------ - aa + aaa (1 row) -SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bbb' ON EMPTY); +SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb' ON EMPTY); json_query ------------ bb (1 row) -SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bbb"'::jsonb ON EMPTY); - json_query ------------- - "b -(1 row) - +SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY); +ERROR: value too long for type character(2) -- OMIT QUOTES behavior should not be specified when WITH WRAPPER used: -- Should fail SELECT JSON_QUERY(jsonb '[1]', '$' WITH WRAPPER OMIT QUOTES); @@ -828,12 +834,6 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10)); [1, 2] (1 row) -SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3)); - json_query ------------- - [1, -(1 row) - SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON); json_query ------------ @@ -1369,3 +1369,11 @@ ERROR: invalid ON ERROR behavior LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); ^ DETAIL: Only ERROR, NULL, EMPTY [ ARRAY | OBJECT }, or DEFAULT is allowed in ON ERROR for JSON_QUERY(). +-- Test implicit coercion domain over fixed-legth type specified in RETURNING +CREATE DOMAIN queryfuncs_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); +ERROR: value too long for type character(2) +SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); +ERROR: value too long for type character(2) +SELECT JSON_VALUE(jsonb '12', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); +ERROR: value for domain queryfuncs_char2 violates check constraint "queryfuncs_char2_check" diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index e6e20175b0..bb2487e864 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -463,3 +463,15 @@ SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT \sv is_json_view DROP VIEW is_json_view; + +-- Test implicit coercion to a fixed-length type specified in RETURNING +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2)); +SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2))); +SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2))); +SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i; +SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i; + +-- Now try domain over fixed-length type +CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2); +SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2); diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 1f81464c5f..990e5bcac0 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -266,7 +266,7 @@ SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a')); -SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a')); +SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a')); SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a')); diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index 4586fdb8a4..adb34bf85a 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -53,7 +53,9 @@ SELECT JSON_VALUE(jsonb '"1.23"', '$' RETURNING int ERROR ON ERROR); SELECT JSON_VALUE(jsonb '"aaa"', '$'); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING text); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(5)); +SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2) ERROR ON ERROR); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(2)); +SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING jsonb); SELECT JSON_VALUE(jsonb '"aaa"', '$' RETURNING json ERROR ON ERROR); @@ -188,10 +190,11 @@ SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING json OMIT QUOTES ERROR ON ERROR); SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING bytea FORMAT JSON OMIT QUOTES ERROR ON ERROR); -- Behavior when a RETURNING type has typmod != -1 -SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2)); -SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(2) OMIT QUOTES); -SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bbb' ON EMPTY); -SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bbb"'::jsonb ON EMPTY); +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) ERROR ON ERROR); +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3)); +SELECT JSON_QUERY(jsonb '"aaa"', '$' RETURNING char(3) OMIT QUOTES ERROR ON ERROR); +SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT 'bb' ON EMPTY); +SELECT JSON_QUERY(jsonb '"aaa"', '$.a' RETURNING char(2) OMIT QUOTES DEFAULT '"bb"'::jsonb ON EMPTY); -- OMIT QUOTES behavior should not be specified when WITH WRAPPER used: -- Should fail @@ -235,7 +238,6 @@ SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING jsonb FORMAT JSON); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(10)); -SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING char(3)); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING text FORMAT JSON); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea); SELECT JSON_QUERY(jsonb '[1,2]', '$' RETURNING bytea FORMAT JSON); @@ -464,3 +466,9 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz); SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); + +-- Test implicit coercion domain over fixed-legth type specified in RETURNING +CREATE DOMAIN queryfuncs_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_QUERY(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '123', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); +SELECT JSON_VALUE(jsonb '12', '$' RETURNING queryfuncs_char2 ERROR ON ERROR); -- 2.43.0