Skip to content

Commit 49082c2

Browse files
adunstanadunstan
committed
RETURNING clause for JSON() and JSON_SCALAR()
This patch is extracted from a larger patch that allowed setting the default returned value from these functions to json or jsonb. That had problems, but this piece of it is fine. For these functions only json or jsonb can be specified in the RETURNING clause. Extracted from an original patch from Nikita Glukhov Reviewers have included (in no particular order) Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup, Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson, Justin Pryzby. Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
1 parent ad43a41 commit 49082c2

File tree

9 files changed

+135
-16
lines changed

9 files changed

+135
-16
lines changed

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2354,6 +2354,7 @@ _copyJsonParseExpr(const JsonParseExpr *from)
23542354
JsonParseExpr *newnode = makeNode(JsonParseExpr);
23552355

23562356
COPY_NODE_FIELD(expr);
2357+
COPY_NODE_FIELD(output);
23572358
COPY_SCALAR_FIELD(unique_keys);
23582359
COPY_LOCATION_FIELD(location);
23592360

@@ -2369,6 +2370,7 @@ _copyJsonScalarExpr(const JsonScalarExpr *from)
23692370
JsonScalarExpr *newnode = makeNode(JsonScalarExpr);
23702371

23712372
COPY_NODE_FIELD(expr);
2373+
COPY_NODE_FIELD(output);
23722374
COPY_LOCATION_FIELD(location);
23732375

23742376
return newnode;

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -875,6 +875,7 @@ static bool
875875
_equalJsonParseExpr(const JsonParseExpr *a, const JsonParseExpr *b)
876876
{
877877
COMPARE_NODE_FIELD(expr);
878+
COMPARE_NODE_FIELD(output);
878879
COMPARE_SCALAR_FIELD(unique_keys);
879880
COMPARE_LOCATION_FIELD(location);
880881

@@ -885,6 +886,7 @@ static bool
885886
_equalJsonScalarExpr(const JsonScalarExpr *a, const JsonScalarExpr *b)
886887
{
887888
COMPARE_NODE_FIELD(expr);
889+
COMPARE_NODE_FIELD(output);
888890
COMPARE_LOCATION_FIELD(location);
889891

890892
return true;

‎src/backend/nodes/nodeFuncs.c

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4364,9 +4364,25 @@ raw_expression_tree_walker(Node *node,
43644364
}
43654365
break;
43664366
case T_JsonParseExpr:
4367-
return walker(((JsonParseExpr *) node)->expr, context);
4367+
{
4368+
JsonParseExpr *jpe = (JsonParseExpr *) node;
4369+
4370+
if (walker(jpe->expr, context))
4371+
return true;
4372+
if (walker(jpe->output, context))
4373+
return true;
4374+
}
4375+
break;
43684376
case T_JsonScalarExpr:
4369-
return walker(((JsonScalarExpr *) node)->expr, context);
4377+
{
4378+
JsonScalarExpr *jse = (JsonScalarExpr *) node;
4379+
4380+
if (walker(jse->expr, context))
4381+
return true;
4382+
if (walker(jse->output, context))
4383+
return true;
4384+
}
4385+
break;
43704386
case T_JsonSerializeExpr:
43714387
{
43724388
JsonSerializeExpr *jse = (JsonSerializeExpr *) node;

‎src/backend/parser/gram.y

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -15614,21 +15614,24 @@ json_func_expr:
1561415614
;
1561515615

1561615616
json_parse_expr:
15617-
JSON '(' json_value_expr json_key_uniqueness_constraint_opt ')'
15617+
JSON '(' json_value_expr json_key_uniqueness_constraint_opt
15618+
json_returning_clause_opt ')'
1561815619
{
1561915620
JsonParseExpr *n = makeNode(JsonParseExpr);
1562015621
n->expr = (JsonValueExpr *) $3;
1562115622
n->unique_keys = $4;
15623+
n->output = (JsonOutput *) $5;
1562215624
n->location = @1;
1562315625
$$ = (Node *) n;
1562415626
}
1562515627
;
1562615628

1562715629
json_scalar_expr:
15628-
JSON_SCALAR '(' a_expr ')'
15630+
JSON_SCALAR '(' a_expr json_returning_clause_opt ')'
1562915631
{
1563015632
JsonScalarExpr *n = makeNode(JsonScalarExpr);
1563115633
n->expr = (Expr *) $3;
15634+
n->output = (JsonOutput *) $4;
1563215635
n->location = @1;
1563315636
$$ = (Node *) n;
1563415637
}

‎src/backend/parser/parse_expr.c

Lines changed: 36 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -4450,19 +4450,48 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
44504450
return (Node *) jsexpr;
44514451
}
44524452

4453+
static JsonReturning *
4454+
transformJsonConstructorRet(ParseState *pstate, JsonOutput *output, const char *fname)
4455+
{
4456+
JsonReturning *returning;
4457+
4458+
if (output)
4459+
{
4460+
returning = transformJsonOutput(pstate, output, false);
4461+
4462+
Assert(OidIsValid(returning->typid));
4463+
4464+
if (returning->typid != JSONOID && returning->typid != JSONBOID)
4465+
ereport(ERROR,
4466+
(errcode(ERRCODE_DATATYPE_MISMATCH),
4467+
errmsg("cannot use RETURNING type %s in %s",
4468+
format_type_be(returning->typid), fname),
4469+
parser_errposition(pstate, output->typeName->location)));
4470+
}
4471+
else
4472+
{
4473+
Oid targettype = JSONOID;
4474+
JsonFormatType format = JS_FORMAT_JSON;
4475+
4476+
returning = makeNode(JsonReturning);
4477+
returning->format = makeJsonFormat(format, JS_ENC_DEFAULT, -1);
4478+
returning->typid = targettype;
4479+
returning->typmod = -1;
4480+
}
4481+
4482+
return returning;
4483+
}
4484+
44534485
/*
44544486
* Transform a JSON() expression.
44554487
*/
44564488
static Node *
44574489
transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr)
44584490
{
4459-
JsonReturning *returning = makeNode(JsonReturning);
4491+
JsonReturning *returning = transformJsonConstructorRet(pstate, jsexpr->output,
4492+
"JSON()");
44604493
Node *arg;
44614494

4462-
returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1);
4463-
returning->typid = JSONOID;
4464-
returning->typmod = -1;
4465-
44664495
if (jsexpr->unique_keys)
44674496
{
44684497
/*
@@ -4502,12 +4531,9 @@ transformJsonParseExpr(ParseState *pstate, JsonParseExpr *jsexpr)
45024531
static Node *
45034532
transformJsonScalarExpr(ParseState *pstate, JsonScalarExpr *jsexpr)
45044533
{
4505-
JsonReturning *returning = makeNode(JsonReturning);
45064534
Node *arg = transformExprRecurse(pstate, (Node *) jsexpr->expr);
4507-
4508-
returning->format = makeJsonFormat(JS_FORMAT_JSON, JS_ENC_DEFAULT, -1);
4509-
returning->typid = JSONOID;
4510-
returning->typmod = -1;
4535+
JsonReturning *returning = transformJsonConstructorRet(pstate, jsexpr->output,
4536+
"JSON_SCALAR()");
45114537

45124538
if (exprType(arg) == UNKNOWNOID)
45134539
arg = coerce_to_specific_type(pstate, arg, TEXTOID, "JSON_SCALAR");

‎src/backend/utils/adt/ruleutils.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -10092,8 +10092,9 @@ get_json_constructor_options(JsonConstructorExpr *ctor, StringInfo buf)
1009210092
if (ctor->unique)
1009310093
appendStringInfoString(buf, " WITH UNIQUE KEYS");
1009410094

10095-
if (ctor->type != JSCTOR_JSON_PARSE &&
10096-
ctor->type != JSCTOR_JSON_SCALAR)
10095+
if (!((ctor->type == JSCTOR_JSON_PARSE ||
10096+
ctor->type == JSCTOR_JSON_SCALAR) &&
10097+
ctor->returning->typid == JSONOID))
1009710098
get_json_returning(ctor->returning, buf, true);
1009810099
}
1009910100

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1684,6 +1684,7 @@ typedef struct JsonParseExpr
16841684
{
16851685
NodeTag type;
16861686
JsonValueExpr *expr; /* string expression */
1687+
JsonOutput *output; /* RETURNING clause, if specified */
16871688
bool unique_keys; /* WITH UNIQUE KEYS? */
16881689
int location; /* token location, or -1 if unknown */
16891690
} JsonParseExpr;
@@ -1696,6 +1697,7 @@ typedef struct JsonScalarExpr
16961697
{
16971698
NodeTag type;
16981699
Expr *expr; /* scalar expression */
1700+
JsonOutput *output; /* RETURNING clause, if specified */
16991701
int location; /* token location, or -1 if unknown */
17001702
} JsonScalarExpr;
17011703

‎src/test/regress/expected/sqljson.out

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,49 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
113113
Output: JSON('123'::json)
114114
(2 rows)
115115

116+
SELECT JSON('123' RETURNING text);
117+
ERROR: cannot use RETURNING type text in JSON()
118+
LINE 1: SELECT JSON('123' RETURNING text);
119+
^
120+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
121+
QUERY PLAN
122+
-----------------------------
123+
Result
124+
Output: JSON('123'::json)
125+
(2 rows)
126+
127+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json);
128+
QUERY PLAN
129+
-----------------------------
130+
Result
131+
Output: JSON('123'::json)
132+
(2 rows)
133+
134+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb);
135+
QUERY PLAN
136+
----------------------------------------------
137+
Result
138+
Output: JSON('123'::jsonb RETURNING jsonb)
139+
(2 rows)
140+
141+
SELECT pg_typeof(JSON('123'));
142+
pg_typeof
143+
-----------
144+
json
145+
(1 row)
146+
147+
SELECT pg_typeof(JSON('123' RETURNING json));
148+
pg_typeof
149+
-----------
150+
json
151+
(1 row)
152+
153+
SELECT pg_typeof(JSON('123' RETURNING jsonb));
154+
pg_typeof
155+
-----------
156+
jsonb
157+
(1 row)
158+
116159
-- JSON_SCALAR()
117160
SELECT JSON_SCALAR();
118161
ERROR: syntax error at or near ")"
@@ -204,6 +247,20 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
204247
Output: JSON_SCALAR('123'::text)
205248
(2 rows)
206249

250+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json);
251+
QUERY PLAN
252+
----------------------------
253+
Result
254+
Output: JSON_SCALAR(123)
255+
(2 rows)
256+
257+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb);
258+
QUERY PLAN
259+
--------------------------------------------
260+
Result
261+
Output: JSON_SCALAR(123 RETURNING jsonb)
262+
(2 rows)
263+
207264
-- JSON_SERIALIZE()
208265
SELECT JSON_SERIALIZE();
209266
ERROR: syntax error at or near ")"

‎src/test/regress/sql/sqljson.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,14 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123'::bytea FORMAT JSON ENCODING UTF8)
2323
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITH UNIQUE KEYS);
2424
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' WITHOUT UNIQUE KEYS);
2525

26+
SELECT JSON('123' RETURNING text);
27+
28+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123');
29+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING json);
30+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON('123' RETURNING jsonb);
31+
SELECT pg_typeof(JSON('123'));
32+
SELECT pg_typeof(JSON('123' RETURNING json));
33+
SELECT pg_typeof(JSON('123' RETURNING jsonb));
2634

2735
-- JSON_SCALAR()
2836
SELECT JSON_SCALAR();
@@ -41,6 +49,8 @@ SELECT JSON_SCALAR('{}'::jsonb);
4149

4250
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123);
4351
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR('123');
52+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING json);
53+
EXPLAIN (VERBOSE, COSTS OFF) SELECT JSON_SCALAR(123 RETURNING jsonb);
4454

4555
-- JSON_SERIALIZE()
4656
SELECT JSON_SERIALIZE();

0 commit comments

Comments
 (0)