Skip to content

Commit d037cc2

Browse files
deanrasheeddeanrasheed
committed
Fix JsonExpr deparsing to quote variable names in the PASSING clause.
When deparsing a JsonExpr, variable names in the PASSING clause were not quoted. However, since they are parsed as ColLabel tokens, some variable names require double quotes to ensure that they are properly interpreted. Fix by using quote_identifier() in the deparsing code. This oversight was limited to the SQL/JSON query functions JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE(). Back-patch to v17, where these functions were added. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/CAEZATCXTpAS%3DncfLNTZ7YS6O5puHeLg_SUYAit%2Bcs7wsrd9Msg%40mail.gmail.com
1 parent 61b1213 commit d037cc2

File tree

3 files changed

+22
-5
lines changed

3 files changed

+22
-5
lines changed

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

+1-1
Original file line numberDiff line numberDiff line change
@@ -10250,7 +10250,7 @@ get_rule_expr(Node *node, deparse_context *context,
1025010250

1025110251
get_rule_expr((Node *) lfirst(lc2), context, showimplicit);
1025210252
appendStringInfo(buf, " AS %s",
10253-
((String *) lfirst_node(String, lc1))->sval);
10253+
quote_identifier(lfirst_node(String, lc1)->sval));
1025410254
}
1025510255
}
1025610256

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

+17-3
Original file line numberDiff line numberDiff line change
@@ -1084,7 +1084,7 @@ CREATE TABLE test_jsonb_constraints (
10841084
CONSTRAINT test_jsonb_constraint1
10851085
CHECK (js IS JSON)
10861086
CONSTRAINT test_jsonb_constraint2
1087-
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
1087+
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS "TXT", array[1,2,3] as arr))
10881088
CONSTRAINT test_jsonb_constraint3
10891089
CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
10901090
CONSTRAINT test_jsonb_constraint4
@@ -1101,7 +1101,7 @@ CREATE TABLE test_jsonb_constraints (
11011101
x | jsonb | | | JSON_QUERY('[1, 2]'::jsonb, '$[*]' RETURNING jsonb WITH UNCONDITIONAL WRAPPER KEEP QUOTES)
11021102
Check constraints:
11031103
"test_jsonb_constraint1" CHECK (js IS JSON)
1104-
"test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS int, i::text AS txt, ARRAY[1, 2, 3] AS arr))
1104+
"test_jsonb_constraint2" CHECK (JSON_EXISTS(js::jsonb, '$."a"' PASSING i + 5 AS "int", i::text AS "TXT", ARRAY[1, 2, 3] AS arr))
11051105
"test_jsonb_constraint3" CHECK (JSON_VALUE(js::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
11061106
"test_jsonb_constraint4" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) = '[10]'::jsonb)
11071107
"test_jsonb_constraint5" CHECK (JSON_QUERY(js::jsonb, '$."a"' RETURNING character(5) WITHOUT WRAPPER OMIT QUOTES EMPTY ARRAY ON EMPTY) > ('a'::bpchar COLLATE "C"))
@@ -1116,7 +1116,7 @@ ORDER BY 1;
11161116
(JSON_QUERY((js)::jsonb, '$."a"' RETURNING jsonb WITH CONDITIONAL WRAPPER KEEP QUOTES EMPTY OBJECT ON ERROR) = '[10]'::jsonb)
11171117
(JSON_VALUE((js)::jsonb, '$."a"' RETURNING integer DEFAULT 12 ON EMPTY ERROR ON ERROR) > i)
11181118
(js IS JSON)
1119-
JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS int, (i)::text AS txt, ARRAY[1, 2, 3] AS arr)
1119+
JSON_EXISTS((js)::jsonb, '$."a"' PASSING (i + 5) AS "int", (i)::text AS "TXT", ARRAY[1, 2, 3] AS arr)
11201120
(5 rows)
11211121

11221122
SELECT pg_get_expr(adbin, adrelid)
@@ -1366,6 +1366,20 @@ SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
13661366
1
13671367
(1 row)
13681368

1369+
SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS Xyz);
1370+
ERROR: could not find jsonpath variable "Xyz"
1371+
SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS "Xyz");
1372+
json_query
1373+
------------
1374+
1
1375+
(1 row)
1376+
1377+
SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz");
1378+
json_query
1379+
------------
1380+
1
1381+
(1 row)
1382+
13691383
-- Test ON ERROR / EMPTY value validity for the function; all fail.
13701384
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);
13711385
ERROR: invalid ON ERROR behavior

‎src/test/regress/sql/sqljson_queryfuncs.sql

+4-1
Original file line numberDiff line numberDiff line change
@@ -327,7 +327,7 @@ CREATE TABLE test_jsonb_constraints (
327327
CONSTRAINT test_jsonb_constraint1
328328
CHECK (js IS JSON)
329329
CONSTRAINT test_jsonb_constraint2
330-
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS txt, array[1,2,3] as arr))
330+
CHECK (JSON_EXISTS(js::jsonb, '$.a' PASSING i + 5 AS int, i::text AS "TXT", array[1,2,3] as arr))
331331
CONSTRAINT test_jsonb_constraint3
332332
CHECK (JSON_VALUE(js::jsonb, '$.a' RETURNING int DEFAULT '12' ON EMPTY ERROR ON ERROR) > i)
333333
CONSTRAINT test_jsonb_constraint4
@@ -465,6 +465,9 @@ SELECT json_value('"aaa"', path RETURNING json) FROM jsonpaths;
465465
SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xy);
466466
SELECT JSON_QUERY(jsonb 'null', '$xy' PASSING 1 AS xyz);
467467
SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xyz);
468+
SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS Xyz);
469+
SELECT JSON_QUERY(jsonb 'null', '$Xyz' PASSING 1 AS "Xyz");
470+
SELECT JSON_QUERY(jsonb 'null', '$"Xyz"' PASSING 1 AS "Xyz");
468471

469472
-- Test ON ERROR / EMPTY value validity for the function; all fail.
470473
SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR);

0 commit comments

Comments
 (0)