Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5a2bc37

Browse files
author
Nikita Glukhov
committed
Add tests for deparsing of SQL/JSON constructors
1 parentf2fed58 commit5a2bc37

File tree

2 files changed

+191
-0
lines changed

2 files changed

+191
-0
lines changed

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

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -620,3 +620,127 @@ ERROR: duplicate JSON object key value
620620
SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL WITH UNIQUE KEYS RETURNING jsonb)
621621
FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
622622
ERROR: duplicate JSON object key value
623+
-- Test JSON_OBJECT deparsing
624+
EXPLAIN (VERBOSE, COSTS OFF)
625+
SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
626+
QUERY PLAN
627+
------------------------------------------------------------------------------
628+
Result
629+
Output: JSON_OBJECT('foo' : '1'::json, 'bar' : 'baz'::text RETURNING json)
630+
(2 rows)
631+
632+
CREATE VIEW json_object_view AS
633+
SELECT JSON_OBJECT('foo' : '1' FORMAT JSON, 'bar' : 'baz' RETURNING json);
634+
\sv json_object_view
635+
CREATE OR REPLACE VIEW public.json_object_view AS
636+
SELECT JSON_OBJECT('foo' : '1'::text FORMAT JSON, 'bar' : 'baz'::text RETURNING json) AS "json_object"
637+
DROP VIEW json_object_view;
638+
-- Test JSON_ARRAY deparsing
639+
EXPLAIN (VERBOSE, COSTS OFF)
640+
SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
641+
QUERY PLAN
642+
---------------------------------------------------
643+
Result
644+
Output: JSON_ARRAY('1'::json, 2 RETURNING json)
645+
(2 rows)
646+
647+
CREATE VIEW json_array_view AS
648+
SELECT JSON_ARRAY('1' FORMAT JSON, 2 RETURNING json);
649+
\sv json_array_view
650+
CREATE OR REPLACE VIEW public.json_array_view AS
651+
SELECT JSON_ARRAY('1'::text FORMAT JSON, 2 RETURNING json) AS "json_array"
652+
DROP VIEW json_array_view;
653+
-- Test JSON_OBJECTAGG deparsing
654+
EXPLAIN (VERBOSE, COSTS OFF)
655+
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
656+
FROM generate_series(1,5) i;
657+
QUERY PLAN
658+
--------------------------------------------------------------------------------------------------------------------------------------
659+
Aggregate
660+
Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) FILTER (WHERE (i > 3))
661+
-> Function Scan on pg_catalog.generate_series i
662+
Output: i
663+
Function Call: generate_series(1, 5)
664+
(5 rows)
665+
666+
EXPLAIN (VERBOSE, COSTS OFF)
667+
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) OVER (PARTITION BY i % 2)
668+
FROM generate_series(1,5) i;
669+
QUERY PLAN
670+
-----------------------------------------------------------------------------------------------------------------------------------
671+
WindowAgg
672+
Output: JSON_OBJECTAGG(i : (('111'::text || (i)::text))::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) OVER (?), ((i % 2))
673+
-> Sort
674+
Output: ((i % 2)), i
675+
Sort Key: ((i.i % 2))
676+
-> Function Scan on pg_catalog.generate_series i
677+
Output: (i % 2), i
678+
Function Call: generate_series(1, 5)
679+
(8 rows)
680+
681+
CREATE VIEW json_objectagg_view AS
682+
SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING text) FILTER (WHERE i > 3)
683+
FROM generate_series(1,5) i;
684+
\sv json_objectagg_view
685+
CREATE OR REPLACE VIEW public.json_objectagg_view AS
686+
SELECT JSON_OBJECTAGG(i.i : ('111'::text || i.i)::bytea FORMAT JSON WITH UNIQUE KEYS RETURNING text) FILTER (WHERE i.i > 3) AS "json_objectagg"
687+
FROM generate_series(1, 5) i(i)
688+
DROP VIEW json_objectagg_view;
689+
-- Test JSON_ARRAYAGG deparsing
690+
EXPLAIN (VERBOSE, COSTS OFF)
691+
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
692+
FROM generate_series(1,5) i;
693+
QUERY PLAN
694+
-----------------------------------------------------------------------------------------------------------------------------
695+
Aggregate
696+
Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE (i > 3))
697+
-> Function Scan on pg_catalog.generate_series i
698+
Output: i
699+
Function Call: generate_series(1, 5)
700+
(5 rows)
701+
702+
EXPLAIN (VERBOSE, COSTS OFF)
703+
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (PARTITION BY i % 2)
704+
FROM generate_series(1,5) i;
705+
QUERY PLAN
706+
--------------------------------------------------------------------------------------------------------------------------
707+
WindowAgg
708+
Output: JSON_ARRAYAGG((('111'::text || (i)::text))::bytea FORMAT JSON NULL ON NULL RETURNING text) OVER (?), ((i % 2))
709+
-> Sort
710+
Output: ((i % 2)), i
711+
Sort Key: ((i.i % 2))
712+
-> Function Scan on pg_catalog.generate_series i
713+
Output: (i % 2), i
714+
Function Call: generate_series(1, 5)
715+
(8 rows)
716+
717+
CREATE VIEW json_arrayagg_view AS
718+
SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i > 3)
719+
FROM generate_series(1,5) i;
720+
\sv json_arrayagg_view
721+
CREATE OR REPLACE VIEW public.json_arrayagg_view AS
722+
SELECT JSON_ARRAYAGG(('111'::text || i.i)::bytea FORMAT JSON NULL ON NULL RETURNING text) FILTER (WHERE i.i > 3) AS "json_arrayagg"
723+
FROM generate_series(1, 5) i(i)
724+
DROP VIEW json_arrayagg_view;
725+
-- Test JSON_ARRAY(subquery) deparsing
726+
EXPLAIN (VERBOSE, COSTS OFF)
727+
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
728+
QUERY PLAN
729+
---------------------------------------------------------------------
730+
Result
731+
Output: $0
732+
InitPlan 1 (returns $0)
733+
-> Aggregate
734+
Output: JSON_ARRAYAGG("*VALUES*".column1 RETURNING jsonb)
735+
-> Values Scan on "*VALUES*"
736+
Output: "*VALUES*".column1
737+
(7 rows)
738+
739+
CREATE VIEW json_array_subquery_view AS
740+
SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
741+
\sv json_array_subquery_view
742+
CREATE OR REPLACE VIEW public.json_array_subquery_view AS
743+
SELECT ( SELECT JSON_ARRAYAGG(q.a RETURNING jsonb) AS "json_arrayagg"
744+
FROM ( SELECT foo.i
745+
FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
746+
DROP VIEW json_array_subquery_view;

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

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -213,3 +213,70 @@ FROM (VALUES (1, 1), (1, NULL), (2, 2)) foo(k, v);
213213

214214
SELECT JSON_OBJECTAGG(k: v ABSENTONNULL WITH UNIQUE KEYS RETURNING jsonb)
215215
FROM (VALUES (1,1), (1,NULL), (2,2)) foo(k, v);
216+
217+
-- Test JSON_OBJECT deparsing
218+
EXPLAIN (VERBOSE, COSTS OFF)
219+
SELECT JSON_OBJECT('foo' :'1' FORMAT JSON,'bar' :'baz' RETURNING json);
220+
221+
CREATEVIEWjson_object_viewAS
222+
SELECT JSON_OBJECT('foo' :'1' FORMAT JSON,'bar' :'baz' RETURNING json);
223+
224+
\sv json_object_view
225+
226+
DROPVIEW json_object_view;
227+
228+
-- Test JSON_ARRAY deparsing
229+
EXPLAIN (VERBOSE, COSTS OFF)
230+
SELECT JSON_ARRAY('1' FORMAT JSON,2 RETURNING json);
231+
232+
CREATEVIEWjson_array_viewAS
233+
SELECT JSON_ARRAY('1' FORMAT JSON,2 RETURNING json);
234+
235+
\sv json_array_view
236+
237+
DROPVIEW json_array_view;
238+
239+
-- Test JSON_OBJECTAGG deparsing
240+
EXPLAIN (VERBOSE, COSTS OFF)
241+
SELECT JSON_OBJECTAGG(i: ('111'|| i)::bytea FORMAT JSON WITH UNIQUE RETURNINGtext) FILTER (WHERE i>3)
242+
FROM generate_series(1,5) i;
243+
244+
EXPLAIN (VERBOSE, COSTS OFF)
245+
SELECT JSON_OBJECTAGG(i: ('111'|| i)::bytea FORMAT JSON WITH UNIQUE RETURNINGtext) OVER (PARTITION BY i %2)
246+
FROM generate_series(1,5) i;
247+
248+
CREATEVIEWjson_objectagg_viewAS
249+
SELECT JSON_OBJECTAGG(i: ('111'|| i)::bytea FORMAT JSON WITH UNIQUE RETURNINGtext) FILTER (WHERE i>3)
250+
FROM generate_series(1,5) i;
251+
252+
\sv json_objectagg_view
253+
254+
DROPVIEW json_objectagg_view;
255+
256+
-- Test JSON_ARRAYAGG deparsing
257+
EXPLAIN (VERBOSE, COSTS OFF)
258+
SELECT JSON_ARRAYAGG(('111'|| i)::bytea FORMAT JSONNULLONNULL RETURNINGtext) FILTER (WHERE i>3)
259+
FROM generate_series(1,5) i;
260+
261+
EXPLAIN (VERBOSE, COSTS OFF)
262+
SELECT JSON_ARRAYAGG(('111'|| i)::bytea FORMAT JSONNULLONNULL RETURNINGtext) OVER (PARTITION BY i %2)
263+
FROM generate_series(1,5) i;
264+
265+
CREATEVIEWjson_arrayagg_viewAS
266+
SELECT JSON_ARRAYAGG(('111'|| i)::bytea FORMAT JSONNULLONNULL RETURNINGtext) FILTER (WHERE i>3)
267+
FROM generate_series(1,5) i;
268+
269+
\sv json_arrayagg_view
270+
271+
DROPVIEW json_arrayagg_view;
272+
273+
-- Test JSON_ARRAY(subquery) deparsing
274+
EXPLAIN (VERBOSE, COSTS OFF)
275+
SELECT JSON_ARRAY(SELECT iFROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
276+
277+
CREATEVIEWjson_array_subquery_viewAS
278+
SELECT JSON_ARRAY(SELECT iFROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING jsonb);
279+
280+
\sv json_array_subquery_view
281+
282+
DROPVIEW json_array_subquery_view;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp