Cleanup around json_to_record/json_to_recordset

Set function parameter names and defaults. Add jsonb versions (which the
code already provided for so the actual new code is trivial). Add jsonb
regression tests and docs.

Bump catalog version (which I apparently forgot to do when jsonb was
committed).

8 files changed, 87 insertions, 13 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 662040261e9..42a4c00a1ed 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -833,6 +833,22 @@ CREATE OR REPLACE FUNCTION
   jsonb_populate_recordset(base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false)
   RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100  AS 'jsonb_populate_recordset';
 
+CREATE OR REPLACE FUNCTION
+  json_to_record(from_json json, nested_as_text boolean DEFAULT false)
+  RETURNS record LANGUAGE internal STABLE AS 'json_to_record';
+
+CREATE OR REPLACE FUNCTION
+  json_to_recordset(from_json json, nested_as_text boolean DEFAULT false)
+  RETURNS SETOF record LANGUAGE internal STABLE ROWS 100  AS 'json_to_recordset';
+
+CREATE OR REPLACE FUNCTION
+  jsonb_to_record(from_json jsonb, nested_as_text boolean DEFAULT false)
+  RETURNS record LANGUAGE internal STABLE AS 'jsonb_to_record';
+
+CREATE OR REPLACE FUNCTION
+  jsonb_to_recordset(from_json jsonb, nested_as_text boolean DEFAULT false)
+  RETURNS SETOF record LANGUAGE internal STABLE ROWS 100  AS 'jsonb_to_recordset';
+
 CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes(
     IN slotname name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}',
     OUT location pg_lsn, OUT xid xid, OUT data text)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 94595998029..bf164677142 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -2015,6 +2015,12 @@ jsonb_populate_record(PG_FUNCTION_ARGS)
 }
 
 Datum
+jsonb_to_record(PG_FUNCTION_ARGS)
+{
+	return populate_record_worker(fcinfo, false);
+}
+
+Datum
 json_populate_record(PG_FUNCTION_ARGS)
 {
 	return populate_record_worker(fcinfo, true);
@@ -2449,6 +2455,24 @@ jsonb_populate_recordset(PG_FUNCTION_ARGS)
 	return populate_recordset_worker(fcinfo, true);
 }
 
+Datum
+jsonb_to_recordset(PG_FUNCTION_ARGS)
+{
+	return populate_recordset_worker(fcinfo, false);
+}
+
+Datum
+json_populate_recordset(PG_FUNCTION_ARGS)
+{
+	return populate_recordset_worker(fcinfo, true);
+}
+
+Datum
+json_to_recordset(PG_FUNCTION_ARGS)
+{
+	return populate_recordset_worker(fcinfo, false);
+}
+
 static void
 make_row_from_rec_and_jsonb(Jsonb * element, PopulateRecordsetState *state)
 {
@@ -2571,18 +2595,6 @@ make_row_from_rec_and_jsonb(Jsonb * element, PopulateRecordsetState *state)
 	tuplestore_puttuple(state->tuple_store, rettuple);
 }
 
-Datum
-json_populate_recordset(PG_FUNCTION_ARGS)
-{
-	return populate_recordset_worker(fcinfo, true);
-}
-
-Datum
-json_to_recordset(PG_FUNCTION_ARGS)
-{
-	return populate_recordset_worker(fcinfo, false);
-}
-
 /*
  * common worker for json_populate_recordset() and json_to_recordset()
  */
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 60f78d24a96..3c74fa0a515 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	201403121
+#define CATALOG_VERSION_NO	201403261
 
 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 56f0f11ebe2..334e6b8d155 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4546,6 +4546,10 @@ DATA(insert OID = 3209 (  jsonb_populate_record    PGNSP PGUID 12 1 0 0 0 f f f
 DESCR("get record fields from a jsonb object");
 DATA(insert OID = 3475 (  jsonb_populate_recordset	PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 3802 16" _null_ _null_ _null_ _null_ jsonb_populate_recordset _null_ _null_ _null_ ));
 DESCR("get set of records with fields from a jsonb array of objects");
+DATA(insert OID = 3490 (  jsonb_to_record	   PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2249 "3802 16" _null_ _null_ _null_ _null_ jsonb_to_record _null_ _null_ _null_ ));
+DESCR("get record fields from a json object");
+DATA(insert OID = 3491 (  jsonb_to_recordset  PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2249 "3802 16" _null_ _null_ _null_ _null_ jsonb_to_recordset _null_ _null_ _null_ ));
+DESCR("get set of records with fields from a json array of objects");
 DATA(insert OID = 3210 (  jsonb_typeof				PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "3802" _null_ _null_ _null_ _null_ jsonb_typeof _null_ _null_ _null_ ));
 DESCR("get the type of a jsonb value");
 DATA(insert OID = 4038 (  jsonb_ne		   PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "3802 3802" _null_ _null_ _null_ _null_ jsonb_ne _null_ _null_ _null_ ));
diff --git a/src/include/utils/json.h b/src/include/utils/json.h
index b5e947bd7af..82cc48b7113 100644
--- a/src/include/utils/json.h
+++ b/src/include/utils/json.h
@@ -78,5 +78,7 @@ extern Datum jsonb_array_elements_text(PG_FUNCTION_ARGS);
 extern Datum jsonb_array_elements(PG_FUNCTION_ARGS);
 extern Datum jsonb_populate_record(PG_FUNCTION_ARGS);
 extern Datum jsonb_populate_recordset(PG_FUNCTION_ARGS);
+extern Datum jsonb_to_record(PG_FUNCTION_ARGS);
+extern Datum jsonb_to_recordset(PG_FUNCTION_ARGS);
 
 #endif   /* JSON_H */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index f368530a193..8bd0131100d 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -1430,6 +1430,22 @@ SELECT jsonb '{ "a":  "null \u0000 escape" }' ->> 'a' AS not_unescaped;
  null \u0000 escape
 (1 row)
 
+-- jsonb_to_record and jsonb_to_recordset
+select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
+    as x(a int, b text, d text);
+ a |  b  | d 
+---+-----+---
+ 1 | foo | 
+(1 row)
+
+select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
+    as x(a int, b text, c boolean);
+ a |  b  | c 
+---+-----+---
+ 1 | foo | 
+ 2 | bar | t
+(2 rows)
+
 -- indexing
 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
  count 
diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out
index 856c55af717..35524fb9a7e 100644
--- a/src/test/regress/expected/jsonb_1.out
+++ b/src/test/regress/expected/jsonb_1.out
@@ -1430,6 +1430,22 @@ SELECT jsonb '{ "a":  "null \u0000 escape" }' ->> 'a' AS not_unescaped;
  null \u0000 escape
 (1 row)
 
+-- jsonb_to_record and jsonb_to_recordset
+select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
+    as x(a int, b text, d text);
+ a |  b  | d 
+---+-----+---
+ 1 | foo | 
+(1 row)
+
+select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
+    as x(a int, b text, c boolean);
+ a |  b  | c 
+---+-----+---
+ 1 | foo | 
+ 2 | bar | t
+(2 rows)
+
 -- indexing
 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
  count 
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index e460b1bb2a7..3ee43e93470 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -319,6 +319,14 @@ SELECT jsonb '{ "a":  "the Copyright \u00a9 sign" }' ->> 'a' AS correct_in_utf8;
 SELECT jsonb '{ "a":  "dollar \u0024 character" }' ->> 'a' AS correct_everyWHERE;
 SELECT jsonb '{ "a":  "null \u0000 escape" }' ->> 'a' AS not_unescaped;
 
+-- jsonb_to_record and jsonb_to_recordset
+
+select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}',true)
+    as x(a int, b text, d text);
+
+select * from jsonb_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false)
+    as x(a int, b text, c boolean);
+
 -- indexing
 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":null}';
 SELECT count(*) FROM testjsonb WHERE j @> '{"wait":"CC"}';