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).

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 4e2fff7cd7481ef2a717957a4308fa3fccd1b254..6e2fbda6c23fa882091644ae65071f037594a3b4 100644 (file)
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10443,9 +10443,15 @@ table2-mapping
   <indexterm>
    <primary>json_to_record</primary>
   </indexterm>
+  <indexterm>
+   <primary>jsonb_to_record</primary>
+  </indexterm>
   <indexterm>
    <primary>json_to_recordset</primary>
   </indexterm>
+  <indexterm>
+   <primary>jsonb_to_recordset</primary>
+  </indexterm>
 
   <table id="functions-json-processing-table">
     <title>JSON Processing Functions</title>
@@ -10649,9 +10655,9 @@ table2-mapping
        <entry><literal>number</literal></entry>
       </row>
       <row>
-       <entry>
-         <literal>json_to_record(json, nested_as_text bool)</literal>
-       </entry>
+       <entry><para><literal>json_to_record(json [, nested_as_text bool=false])</literal>
+          </para><para><literal>jsonb_to_record(jsonb [, nested_as_text bool=false])</literal>
+       </para></entry>
        <entry><type>record</type></entry>
        <entry>
          Returns an arbitrary record from a JSON object.  As with all functions 
@@ -10670,9 +10676,9 @@ table2-mapping
        </entry>
       </row>
       <row>
-       <entry>
-         <literal>json_to_recordset(json, nested_as_text bool)</literal>
-       </entry>
+       <entry><para><literal>json_to_recordset(json [, nested_as_text bool=false])</literal>
+         </para><para><literal>jsonb_to_recordset(jsonb [, nested_as_text bool=false])</literal>
+       </para></entry>
        <entry><type>setof record</type></entry>
        <entry>
          Returns an arbitrary set of records from a JSON object.  As with 

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 662040261e968dbc1846c12f7a9439cc792879cd..42a4c00a1ed8ad69881ad56110f15225e359a8c1 100644 (file)
--- 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 94595998029667ff6629882f46e1403748c99717..bf1646771423fe8faaa12898e53fdd3fbbd92fef 100644 (file)
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -2014,6 +2014,12 @@ jsonb_populate_record(PG_FUNCTION_ARGS)
    return populate_record_worker(fcinfo, true);
 }
 
+Datum
+jsonb_to_record(PG_FUNCTION_ARGS)
+{
+   return populate_record_worker(fcinfo, false);
+}
+
 Datum
 json_populate_record(PG_FUNCTION_ARGS)
 {
@@ -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 60f78d24a968f299e1d840137f85e6196a9c2dff..3c74fa0a5150bf9cc2c1bada1d696f8dad1ef344 100644 (file)
--- 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 56f0f11ebe27d1826b0db6c518e789c37fa85ac9..334e6b8d15578e24f5a8a56f696394f031aafeb6 100644 (file)
--- 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 b5e947bd7af39622cb1879c8af7a0d3bc3ea66e8..82cc48b7113e1b0bb30da53f5e96fedb85d18299 100644 (file)
--- 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 f368530a193fed70d877d9a8b1913a0fe0726326..8bd0131100d3db18be89611d7ec432dc67107785 100644 (file)
--- 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 856c55af7176af2f91b41fb92e12891a356dc08c..35524fb9a7e17c34b49bfaa6903ad58ac45f1e39 100644 (file)
--- 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 e460b1bb2a704f8a75db7f91cc007c0e82d62b5f..3ee43e93470f371f37a06715635441090d148db4 100644 (file)
--- 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"}';