Make json_populate_record and friends operate recursively

With this change array fields are populated from json(b) arrays, and
composite fields are populated from json(b) objects.

Along the way, some significant code refactoring is done to remove
redundancy in the way to populate_record[_set] and to_record[_set]
functions operate, and some significant efficiency gains are made by
caching tuple descriptors.

Nikita Glukhov, edited some by me.

Reviewed by Aleksander Alekseev and Tom Lane.

1 files changed, 473 insertions, 5 deletions

diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index e93a15f98a..f199eb4f70 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1897,6 +1897,31 @@ SELECT * FROM jsonb_array_elements_text('[1,true,[1,[2,3]],null,{"f1":1,"f2":[7,
 
 -- populate_record
 CREATE TYPE jbpop AS (a text, b int, c timestamp);
+CREATE DOMAIN jsb_int_not_null  AS int     NOT NULL;
+CREATE DOMAIN jsb_int_array_1d  AS int[]   CHECK(array_length(VALUE, 1) = 3);
+CREATE DOMAIN jsb_int_array_2d  AS int[][] CHECK(array_length(VALUE, 2) = 3);
+CREATE TYPE jsbrec AS (
+	i	int,
+	ia	_int4,
+	ia1	int[],
+	ia2	int[][],
+	ia3	int[][][],
+	ia1d	jsb_int_array_1d,
+	ia2d	jsb_int_array_2d,
+	t	text,
+	ta	text[],
+	c	char(10),
+	ca	char(10)[],
+	ts	timestamp,
+	js	json,
+	jsb	jsonb,
+	jsa	json[],
+	rec	jbpop,
+	reca	jbpop[]
+);
+CREATE TYPE jsbrec_i_not_null AS (
+	i	jsb_int_not_null
+);
 SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q;
    a    | b | c 
 --------+---+---
@@ -1935,6 +1960,382 @@ SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a
 
 SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q;
 ERROR:  invalid input syntax for type timestamp: "[100, 200, false]"
+SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop, '{}') q;
+ a | b |            c             
+---+---+--------------------------
+ x | 3 | Mon Dec 31 15:30:56 2012
+(1 row)
+
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"x": 43.2}') q;
+ERROR:  domain jsb_int_not_null does not allow null values
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": null}') q;
+ERROR:  domain jsb_int_not_null does not allow null values
+SELECT i FROM jsonb_populate_record(NULL::jsbrec_i_not_null, '{"i": 12345}') q;
+   i   
+-------
+ 12345
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": null}') q;
+ ia 
+----
+ 
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "ia"
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [1, "2", null, 4]}') q;
+      ia      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1, 2], [3, 4]]}') q;
+      ia       
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], 2]}') q;
+ERROR:  expected json array
+HINT:  see the array element [1] of key "ia"
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": [[1], [2, 3]]}') q;
+ERROR:  malformed json array
+DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia FROM jsonb_populate_record(NULL::jsbrec, '{"ia": "{1,2,3}"}') q;
+   ia    
+---------
+ {1,2,3}
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": null}') q;
+ ia1 
+-----
+ 
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "ia1"
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [1, "2", null, 4]}') q;
+     ia1      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia1 FROM jsonb_populate_record(NULL::jsbrec, '{"ia1": [[1, 2, 3]]}') q;
+    ia1    
+-----------
+ {{1,2,3}}
+(1 row)
+
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": null}') q;
+ ia1d 
+------
+ 
+(1 row)
+
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "ia1d"
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null, 4]}') q;
+ERROR:  value for domain jsb_int_array_1d violates check constraint "jsb_int_array_1d_check"
+SELECT ia1d FROM jsonb_populate_record(NULL::jsbrec, '{"ia1d": [1, "2", null]}') q;
+    ia1d    
+------------
+ {1,2,NULL}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [1, "2", null, 4]}') q;
+     ia2      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [null, 4]]}') q;
+       ia2        
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[], []]}') q;
+ ia2 
+-----
+ {}
+(1 row)
+
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], [3]]}') q;
+ERROR:  malformed json array
+DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ia2 FROM jsonb_populate_record(NULL::jsbrec, '{"ia2": [[1, 2], 3, 4]}') q;
+ERROR:  expected json array
+HINT:  see the array element [1] of key "ia2"
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2"], [null, 4]]}') q;
+ERROR:  value for domain jsb_int_array_2d violates check constraint "jsb_int_array_2d_check"
+SELECT ia2d FROM jsonb_populate_record(NULL::jsbrec, '{"ia2d": [[1, "2", 3], [null, 5, 6]]}') q;
+         ia2d         
+----------------------
+ {{1,2,3},{NULL,5,6}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [1, "2", null, 4]}') q;
+     ia3      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [[1, 2], [null, 4]]}') q;
+       ia3        
+------------------
+ {{1,2},{NULL,4}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[], []], [[], []], [[], []] ]}') q;
+ ia3 
+-----
+ {}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2]], [[3, 4]] ]}') q;
+        ia3        
+-------------------
+ {{{1,2}},{{3,4}}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8]] ]}') q;
+              ia3              
+-------------------------------
+ {{{1,2},{3,4}},{{5,6},{7,8}}}
+(1 row)
+
+SELECT ia3 FROM jsonb_populate_record(NULL::jsbrec, '{"ia3": [ [[1, 2], [3, 4]], [[5, 6], [7, 8], [9, 10]] ]}') q;
+ERROR:  malformed json array
+DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": null}') q;
+ ta 
+----
+ 
+(1 row)
+
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "ta"
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [1, "2", null, 4]}') q;
+      ta      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+SELECT ta FROM jsonb_populate_record(NULL::jsbrec, '{"ta": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR:  expected json array
+HINT:  see the array element [1] of key "ta"
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": null}') q;
+ c 
+---
+ 
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaa"}') q;
+     c      
+------------
+ aaa       
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaa"}') q;
+     c      
+------------
+ aaaaaaaaaa
+(1 row)
+
+SELECT c FROM jsonb_populate_record(NULL::jsbrec, '{"c": "aaaaaaaaaaaaa"}') q;
+ERROR:  value too long for type character(10)
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": null}') q;
+ ca 
+----
+ 
+(1 row)
+
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "ca"
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [1, "2", null, 4]}') q;
+                      ca                       
+-----------------------------------------------
+ {"1         ","2         ",NULL,"4         "}
+(1 row)
+
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": ["aaaaaaaaaaaaaaaa"]}') q;
+ERROR:  value too long for type character(10)
+SELECT ca FROM jsonb_populate_record(NULL::jsbrec, '{"ca": [[1, 2, 3], {"k": "v"}]}') q;
+ERROR:  expected json array
+HINT:  see the array element [1] of key "ca"
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": null}') q;
+ js 
+----
+ 
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": true}') q;
+  js  
+------
+ true
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": 123.45}') q;
+   js   
+--------
+ 123.45
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "123.45"}') q;
+    js    
+----------
+ "123.45"
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": "abc"}') q;
+  js   
+-------
+ "abc"
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": [123, "123", null, {"key": "value"}]}') q;
+                  js                  
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT js FROM jsonb_populate_record(NULL::jsbrec, '{"js": {"a": "bbb", "b": null, "c": 123.45}}') q;
+                  js                  
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": null}') q;
+ jsb 
+-----
+ 
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": true}') q;
+ jsb  
+------
+ true
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": 123.45}') q;
+  jsb   
+--------
+ 123.45
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "123.45"}') q;
+   jsb    
+----------
+ "123.45"
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": "abc"}') q;
+  jsb  
+-------
+ "abc"
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": [123, "123", null, {"key": "value"}]}') q;
+                 jsb                  
+--------------------------------------
+ [123, "123", null, {"key": "value"}]
+(1 row)
+
+SELECT jsb FROM jsonb_populate_record(NULL::jsbrec, '{"jsb": {"a": "bbb", "b": null, "c": 123.45}}') q;
+                 jsb                  
+--------------------------------------
+ {"a": "bbb", "b": null, "c": 123.45}
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": null}') q;
+ jsa 
+-----
+ 
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "jsa"
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": [1, "2", null, 4]}') q;
+        jsa         
+--------------------
+ {1,"\"2\"",NULL,4}
+(1 row)
+
+SELECT jsa FROM jsonb_populate_record(NULL::jsbrec, '{"jsa": ["aaa", null, [1, 2, "3", {}], { "k" : "v" }]}') q;
+                          jsa                          
+-------------------------------------------------------
+ {"\"aaa\"",NULL,"[1, 2, \"3\", {}]","{\"k\": \"v\"}"}
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": 123}') q;
+ rec  
+------
+ (,,)
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": [1, 2]}') q;
+ rec  
+------
+ (,,)
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}') q;
+                rec                
+-----------------------------------
+ (abc,,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(NULL::jsbrec, '{"rec": "(abc,42,01.02.2003)"}') q;
+                 rec                 
+-------------------------------------
+ (abc,42,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": 123}') q;
+ERROR:  expected json array
+HINT:  see the value of key "reca"
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [1, 2]}') q;
+      reca       
+-----------------
+ {"(,,)","(,,)"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]}') q;
+                          reca                          
+--------------------------------------------------------
+ {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": ["(abc,42,01.02.2003)"]}') q;
+                   reca                    
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT reca FROM jsonb_populate_record(NULL::jsbrec, '{"reca": "{\"(abc,42,01.02.2003)\"}"}') q;
+                   reca                    
+-------------------------------------------
+ {"(abc,42,\"Thu Jan 02 00:00:00 2003\")"}
+(1 row)
+
+SELECT rec FROM jsonb_populate_record(
+	row(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
+		row('x',3,'2012-12-31 15:30:56')::jbpop,NULL)::jsbrec,
+	'{"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2}}'
+) q;
+                rec                 
+------------------------------------
+ (abc,3,"Thu Jan 02 00:00:00 2003")
+(1 row)
+
 -- populate_recordset
 SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q;
    a    | b |            c             
@@ -2011,11 +2412,11 @@ select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar",
 (2 rows)
 
 select *, c is null as c_is_null
-from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8}'::jsonb)
-    as t(a int, b jsonb, c text, x int);
- a |         b         | c | x | c_is_null 
----+-------------------+---+---+-----------
- 1 | {"c": 16, "d": 2} |   | 8 | t
+from jsonb_to_record('{"a":1, "b":{"c":16, "d":2}, "x":8, "ca": ["1 2", 3], "ia": [[1,2],[3,4]], "r": {"a": "aaa", "b": 123}}'::jsonb)
+    as t(a int, b jsonb, c text, x int, ca char(5)[], ia int[][], r jbpop);
+ a |         b         | c | x |        ca         |      ia       |     r      | c_is_null 
+---+-------------------+---+---+-------------------+---------------+------------+-----------
+ 1 | {"c": 16, "d": 2} |   | 8 | {"1 2  ","3    "} | {{1,2},{3,4}} | (aaa,123,) | t
 (1 row)
 
 select *, c is null as c_is_null
@@ -2026,6 +2427,73 @@ from jsonb_to_recordset('[{"a":1, "b":{"c":16, "d":2}, "x":8}]'::jsonb)
  1 | {"c": 16, "d": 2} |   | 8 | t
 (1 row)
 
+select * from jsonb_to_record('{"ia": null}') as x(ia _int4);
+ ia 
+----
+ 
+(1 row)
+
+select * from jsonb_to_record('{"ia": 123}') as x(ia _int4);
+ERROR:  expected json array
+HINT:  see the value of key "ia"
+select * from jsonb_to_record('{"ia": [1, "2", null, 4]}') as x(ia _int4);
+      ia      
+--------------
+ {1,2,NULL,4}
+(1 row)
+
+select * from jsonb_to_record('{"ia": [[1, 2], [3, 4]]}') as x(ia _int4);
+      ia       
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from jsonb_to_record('{"ia": [[1], 2]}') as x(ia _int4);
+ERROR:  expected json array
+HINT:  see the array element [1] of key "ia"
+select * from jsonb_to_record('{"ia": [[1], [2, 3]]}') as x(ia _int4);
+ERROR:  malformed json array
+DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
+select * from jsonb_to_record('{"ia2": [1, 2, 3]}') as x(ia2 int[][]);
+   ia2   
+---------
+ {1,2,3}
+(1 row)
+
+select * from jsonb_to_record('{"ia2": [[1, 2], [3, 4]]}') as x(ia2 int4[][]);
+      ia2      
+---------------
+ {{1,2},{3,4}}
+(1 row)
+
+select * from jsonb_to_record('{"ia2": [[[1], [2], [3]]]}') as x(ia2 int4[][]);
+       ia2       
+-----------------
+ {{{1},{2},{3}}}
+(1 row)
+
+-- test type info caching in jsonb_populate_record()
+CREATE TEMP TABLE jsbpoptest (js jsonb);
+INSERT INTO jsbpoptest
+SELECT '{
+	"jsa": [1, "2", null, 4],
+	"rec": {"a": "abc", "c": "01.02.2003", "x": 43.2},
+	"reca": [{"a": "abc", "b": 456}, null, {"c": "01.02.2003", "x": 43.2}]
+}'::jsonb
+FROM generate_series(1, 3);
+SELECT (jsonb_populate_record(NULL::jsbrec, js)).* FROM jsbpoptest;
+ i | ia | ia1 | ia2 | ia3 | ia1d | ia2d | t | ta | c | ca | ts | js | jsb |        jsa         |                rec                |                          reca                          
+---+----+-----+-----+-----+------+------+---+----+---+----+----+----+-----+--------------------+-----------------------------------+--------------------------------------------------------
+   |    |     |     |     |      |      |   |    |   |    |    |    |     | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+   |    |     |     |     |      |      |   |    |   |    |    |    |     | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+   |    |     |     |     |      |      |   |    |   |    |    |    |     | {1,"\"2\"",NULL,4} | (abc,,"Thu Jan 02 00:00:00 2003") | {"(abc,456,)",NULL,"(,,\"Thu Jan 02 00:00:00 2003\")"}
+(3 rows)
+
+DROP TYPE jsbrec;
+DROP TYPE jsbrec_i_not_null;
+DROP DOMAIN jsb_int_not_null;
+DROP DOMAIN jsb_int_array_1d;
+DROP DOMAIN jsb_int_array_2d;
 -- indexing
 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
  count