Fix missing FORMAT when deparsing JSON_ARRAY(query) - Mailing list pgsql-hackers

From Chao Li
Subject Fix missing FORMAT when deparsing JSON_ARRAY(query)
Date
Msg-id 4C89B193-7D54-4705-9CF9-F0D484B9E099@gmail.com
Whole thread
List pgsql-hackers
Hi,

While testing "[8d829f5a0] Fix JSON_ARRAY(query) empty set handling and view deparsing”, I found that the departing may
omitthe FORMAT JSON clause. 

Here is a simple repro:
```
evantest=# create view v as
evantest-#   select json_array(select '{"a": 1}'::text format json) as j;
CREATE VIEW
evantest=# select * from v;
     j
------------
 [{"a": 1}]
(1 row)

evantest=# select pg_get_viewdef('v'::regclass, true);
                              pg_get_viewdef
---------------------------------------------------------------------------
  SELECT JSON_ARRAY( SELECT '{"a": 1}'::text AS text RETURNING json) AS j;
(1 row)

evantest=# select pg_get_viewdef('v'::regclass, false);
                              pg_get_viewdef
---------------------------------------------------------------------------
  SELECT JSON_ARRAY( SELECT '{"a": 1}'::text AS text RETURNING json) AS j;
(1 row)

evantest=# SELECT JSON_ARRAY( SELECT '{"a": 1}'::text AS text RETURNING json) AS j;
       j
----------------
 ["{\"a\": 1}"]
(1 row)
```

As shown above, I defined the view with FORMAT JSON, but the deparsed SQL has lost that clause. Running the deparsed
SELECTproduces a different result from selecting from the view because FORMAT JSON is missing. 

Currently, JsonConstructorExpr does not store the JsonFormat information. To fix this problem, we need to add a
JsonFormatfield to JsonConstructorExpr. Please see the attached patch for details. 

With the fix:
```
evantest=# select pg_get_viewdef('v'::regclass, true);
                                    pg_get_viewdef
---------------------------------------------------------------------------------------
  SELECT JSON_ARRAY( SELECT '{"a": 1}'::text AS text FORMAT JSON RETURNING json) AS j;
(1 row)

evantest=# SELECT JSON_ARRAY( SELECT '{"a": 1}'::text AS text FORMAT JSON RETURNING json) AS j;
     j
------------
 [{"a": 1}]
(1 row)
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/





Attachment

pgsql-hackers by date:

Previous
From: Zsolt Parragi
Date:
Subject: Re: [PATCH] Add pg_get_event_trigger_ddl() function
Next
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: sequencesync worker race with REFRESH SEQUENCES