diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f4754588ae7594061969da7f4eb76302a33e040b..f8701e1771fea33bf17b568c86732580a7557d0f 100644 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** table2-mapping *** 10560,10567 **** ! json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]) ! jsonb_populate_record(base anyelement, from_json jsonb, [, use_json_as_text bool=false]) anyelement --- 10560,10567 ---- ! json_populate_record(base anyelement, from_json json) ! jsonb_populate_record(base anyelement, from_json jsonb) anyelement *************** table2-mapping *** 10579,10586 **** ! json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]) ! jsonb_populate_recordset(base anyelement, from_json jsonb, [, use_json_as_text bool=false]) setof anyelement --- 10579,10586 ---- ! json_populate_recordset(base anyelement, from_json json) ! jsonb_populate_recordset(base anyelement, from_json jsonb) setof anyelement *************** table2-mapping *** 10653,10670 **** number ! json_to_record(json [, nested_as_text bool=false]) ! jsonb_to_record(jsonb [, nested_as_text bool=false]) record Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS ! clause. If nested_as_text is true, the function ! coerces nested complex elements to text. ! select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}',true) as x(a int, b text, d text) a | b | d --- 10653,10669 ---- number ! json_to_record(json) ! jsonb_to_record(jsonb) record Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS ! clause. ! select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text) a | b | d *************** table2-mapping *** 10674,10691 **** ! json_to_recordset(json [, nested_as_text bool=false]) ! jsonb_to_recordset(jsonb [, nested_as_text bool=false]) setof record Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with ! an AS clause. nested_as_text works as ! with json_to_record. ! select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]',true) as x(a int, b text); a | b --- 10673,10689 ---- ! json_to_recordset(json) ! jsonb_to_recordset(jsonb) setof record Builds an arbitrary set of records from a JSON array of objects (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with ! an AS clause. ! select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text); a | b diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 793a92b76aafdeba75bc43dad88e82ededac251b..1bde175d4566ae9ac36f783c3d06c7ef73b52fcc 100644 *** a/src/backend/catalog/system_views.sql --- b/src/backend/catalog/system_views.sql *************** CREATE OR REPLACE FUNCTION *** 817,854 **** pg_start_backup(label text, fast boolean DEFAULT false) RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup'; CREATE OR REPLACE FUNCTION json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record'; CREATE OR REPLACE FUNCTION json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset'; - CREATE OR REPLACE FUNCTION - jsonb_populate_record(base anyelement, from_json jsonb, use_json_as_text boolean DEFAULT false) - RETURNS anyelement LANGUAGE internal STABLE AS 'jsonb_populate_record'; - - 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 slot_name name, IN upto_lsn pg_lsn, IN upto_nchanges int, VARIADIC options text[] DEFAULT '{}', OUT location pg_lsn, OUT xid xid, OUT data text) --- 817,832 ---- pg_start_backup(label text, fast boolean DEFAULT false) RETURNS pg_lsn STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup'; + -- legacy definition for compatibility with 9.3 CREATE OR REPLACE FUNCTION json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) RETURNS anyelement LANGUAGE internal STABLE AS 'json_populate_record'; + -- legacy definition for compatibility with 9.3 CREATE OR REPLACE FUNCTION json_populate_recordset(base anyelement, from_json json, use_json_as_text boolean DEFAULT false) RETURNS SETOF anyelement LANGUAGE internal STABLE ROWS 100 AS 'json_populate_recordset'; CREATE OR REPLACE FUNCTION pg_logical_slot_get_changes( IN slot_name 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 bd1241b620fa17a61a45a441da48f9d769585d31..6c16a953dd3fb9c0bcb903a4b17c1fb75bd2a807 100644 *** a/src/backend/utils/adt/jsonfuncs.c --- b/src/backend/utils/adt/jsonfuncs.c *************** static void elements_array_element_end(v *** 85,92 **** static void elements_scalar(void *state, char *token, JsonTokenType tokentype); /* turn a json object into a hash table */ ! static HTAB *get_json_object_as_hash(text *json, const char *funcname, ! bool use_json_as_text); /* common worker for populate_record and to_record */ static Datum populate_record_worker(FunctionCallInfo fcinfo, const char *funcname, --- 85,91 ---- static void elements_scalar(void *state, char *token, JsonTokenType tokentype); /* turn a json object into a hash table */ ! static HTAB *get_json_object_as_hash(text *json, const char *funcname); /* common worker for populate_record and to_record */ static Datum populate_record_worker(FunctionCallInfo fcinfo, const char *funcname, *************** typedef struct JhashState *** 198,204 **** HTAB *hash; char *saved_scalar; char *save_json_start; - bool use_json_as_text; } JHashState; /* hashtable element */ --- 197,202 ---- *************** typedef struct PopulateRecordsetState *** 235,241 **** HTAB *json_hash; char *saved_scalar; char *save_json_start; - bool use_json_as_text; Tuplestorestate *tuple_store; TupleDesc ret_tdesc; HeapTupleHeader rec; --- 233,238 ---- *************** populate_record_worker(FunctionCallInfo *** 1989,1995 **** Oid jtype = get_fn_expr_argtype(fcinfo->flinfo, json_arg_num); text *json; Jsonb *jb = NULL; - bool use_json_as_text; HTAB *json_hash = NULL; HeapTupleHeader rec = NULL; Oid tupType = InvalidOid; --- 1986,1991 ---- *************** populate_record_worker(FunctionCallInfo *** 2005,2013 **** Assert(jtype == JSONOID || jtype == JSONBOID); - use_json_as_text = PG_ARGISNULL(json_arg_num + 1) ? false : - PG_GETARG_BOOL(json_arg_num + 1); - if (have_record_arg) { Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0); --- 2001,2006 ---- *************** populate_record_worker(FunctionCallInfo *** 2065,2071 **** /* just get the text */ json = PG_GETARG_TEXT_P(json_arg_num); ! json_hash = get_json_object_as_hash(json, funcname, use_json_as_text); /* * if the input json is empty, we can only skip the rest if we were --- 2058,2064 ---- /* just get the text */ json = PG_GETARG_TEXT_P(json_arg_num); ! json_hash = get_json_object_as_hash(json, funcname); /* * if the input json is empty, we can only skip the rest if we were *************** populate_record_worker(FunctionCallInfo *** 2227,2236 **** else if (v->type == jbvNumeric) s = DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(v->val.numeric))); - else if (!use_json_as_text) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot populate with a nested object unless use_json_as_text is true"))); else if (v->type == jbvBinary) s = JsonbToCString(NULL, (JsonbContainer *) v->val.binary.data, v->val.binary.len); else --- 2220,2225 ---- *************** populate_record_worker(FunctionCallInfo *** 2258,2272 **** * get_json_object_as_hash * * decompose a json object into a hash table. - * - * Currently doesn't allow anything but a flat object. Should this - * change? - * - * funcname argument allows caller to pass in its name for use in - * error messages. */ static HTAB * ! get_json_object_as_hash(text *json, const char *funcname, bool use_json_as_text) { HASHCTL ctl; HTAB *tab; --- 2247,2255 ---- * get_json_object_as_hash * * decompose a json object into a hash table. */ static HTAB * ! get_json_object_as_hash(text *json, const char *funcname) { HASHCTL ctl; HTAB *tab; *************** get_json_object_as_hash(text *json, cons *** 2289,2295 **** state->function_name = funcname; state->hash = tab; state->lex = lex; - state->use_json_as_text = use_json_as_text; sem->semstate = (void *) state; sem->array_start = hash_array_start; --- 2272,2277 ---- *************** hash_object_field_start(void *state, cha *** 2313,2323 **** if (_state->lex->token_type == JSON_TOKEN_ARRAY_START || _state->lex->token_type == JSON_TOKEN_OBJECT_START) { ! if (!_state->use_json_as_text) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s on a nested object", ! _state->function_name))); _state->save_json_start = _state->lex->token_start; } else --- 2295,2301 ---- if (_state->lex->token_type == JSON_TOKEN_ARRAY_START || _state->lex->token_type == JSON_TOKEN_OBJECT_START) { ! /* remember start position of the whole text of the subobject */ _state->save_json_start = _state->lex->token_start; } else *************** make_row_from_rec_and_jsonb(Jsonb *eleme *** 2535,2544 **** else if (v->type == jbvNumeric) s = DatumGetCString(DirectFunctionCall1(numeric_out, PointerGetDatum(v->val.numeric))); - else if (!state->use_json_as_text) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot populate with a nested object unless use_json_as_text is true"))); else if (v->type == jbvBinary) s = JsonbToCString(NULL, (JsonbContainer *) v->val.binary.data, v->val.binary.len); else --- 2513,2518 ---- *************** populate_recordset_worker(FunctionCallIn *** 2565,2571 **** { int json_arg_num = have_record_arg ? 1 : 0; Oid jtype = get_fn_expr_argtype(fcinfo->flinfo, json_arg_num); - bool use_json_as_text; ReturnSetInfo *rsi; MemoryContext old_cxt; Oid tupType; --- 2539,2544 ---- *************** populate_recordset_worker(FunctionCallIn *** 2576,2583 **** int ncolumns; PopulateRecordsetState *state; - use_json_as_text = PG_ARGISNULL(json_arg_num + 1) ? false : PG_GETARG_BOOL(json_arg_num + 1); - if (have_record_arg) { Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0); --- 2549,2554 ---- *************** populate_recordset_worker(FunctionCallIn *** 2667,2673 **** state->function_name = funcname; state->my_extra = my_extra; state->rec = rec; - state->use_json_as_text = use_json_as_text; state->fn_mcxt = fcinfo->flinfo->fn_mcxt; if (jtype == JSONOID) --- 2638,2643 ---- *************** populate_recordset_object_start(void *st *** 2749,2764 **** errmsg("cannot call %s on an object", _state->function_name))); ! /* Nested objects, if allowed, require no special processing */ if (lex_level > 1) - { - if (!_state->use_json_as_text) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot call %s with nested objects", - _state->function_name))); return; - } /* Object at level 1: set up a new hash table for this object */ memset(&ctl, 0, sizeof(ctl)); --- 2719,2727 ---- errmsg("cannot call %s on an object", _state->function_name))); ! /* Nested objects require no special processing */ if (lex_level > 1) return; /* Object at level 1: set up a new hash table for this object */ memset(&ctl, 0, sizeof(ctl)); *************** populate_recordset_array_element_start(v *** 2903,2915 **** static void populate_recordset_array_start(void *state) { ! PopulateRecordsetState *_state = (PopulateRecordsetState *) state; ! ! if (_state->lex->lex_level != 0 && !_state->use_json_as_text) ! ereport(ERROR, ! (errcode(ERRCODE_INVALID_PARAMETER_VALUE), ! errmsg("cannot call %s with nested arrays", ! _state->function_name))); } static void --- 2866,2872 ---- static void populate_recordset_array_start(void *state) { ! /* nothing to do */ } static void *************** populate_recordset_object_field_start(vo *** 2938,2948 **** if (_state->lex->token_type == JSON_TOKEN_ARRAY_START || _state->lex->token_type == JSON_TOKEN_OBJECT_START) { - if (!_state->use_json_as_text) - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("cannot call %s on a nested object", - _state->function_name))); _state->save_json_start = _state->lex->token_start; } else --- 2895,2900 ---- diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 762ce6ca09c16c32f113130fd6a47b33621ffbff..43d11e26e9f47438266f792acb461799a2a6ed94 100644 *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 3960 ( json_populate_ *** 4250,4258 **** DESCR("get record fields from a json object"); DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16" _null_ _null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ )); DESCR("get set of records with fields from a json array of objects"); ! DATA(insert OID = 3204 ( json_to_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2249 "114 16" _null_ _null_ _null_ _null_ json_to_record _null_ _null_ _null_ )); DESCR("get record fields from a json object"); ! DATA(insert OID = 3205 ( json_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2249 "114 16" _null_ _null_ _null_ _null_ json_to_recordset _null_ _null_ _null_ )); DESCR("get set of records with fields from a json array of objects"); DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_ _null_ _null_ json_typeof _null_ _null_ _null_ )); DESCR("get the type of a json value"); --- 4250,4258 ---- DESCR("get record fields from a json object"); DATA(insert OID = 3961 ( json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 3 0 2283 "2283 114 16" _null_ _null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ )); DESCR("get set of records with fields from a json array of objects"); ! DATA(insert OID = 3204 ( json_to_record PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 2249 "114" _null_ _null_ _null_ _null_ json_to_record _null_ _null_ _null_ )); DESCR("get record fields from a json object"); ! DATA(insert OID = 3205 ( json_to_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 1 0 2249 "114" _null_ _null_ _null_ _null_ json_to_recordset _null_ _null_ _null_ )); DESCR("get set of records with fields from a json array of objects"); DATA(insert OID = 3968 ( json_typeof PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 25 "114" _null_ _null_ _null_ _null_ json_typeof _null_ _null_ _null_ )); DESCR("get the type of a json value"); *************** DATA(insert OID = 3208 ( jsonb_each *** 4609,4621 **** DESCR("key value pairs of a jsonb object"); DATA(insert OID = 3932 ( jsonb_each_text PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 2249 "3802" "{3802,25,25}" "{i,o,o}" "{from_json,key,value}" _null_ jsonb_each_text _null_ _null_ _null_ )); DESCR("key value pairs of a jsonb object"); ! DATA(insert OID = 3209 ( jsonb_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 3 0 2283 "2283 3802 16" _null_ _null_ _null_ _null_ jsonb_populate_record _null_ _null_ _null_ )); 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"); --- 4609,4621 ---- DESCR("key value pairs of a jsonb object"); DATA(insert OID = 3932 ( jsonb_each_text PGNSP PGUID 12 1 100 0 0 f f f f t t i 1 0 2249 "3802" "{3802,25,25}" "{i,o,o}" "{from_json,key,value}" _null_ jsonb_each_text _null_ _null_ _null_ )); DESCR("key value pairs of a jsonb object"); ! DATA(insert OID = 3209 ( jsonb_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2283 "2283 3802" _null_ _null_ _null_ _null_ jsonb_populate_record _null_ _null_ _null_ )); 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 2 0 2283 "2283 3802" _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 t f s 1 0 2249 "3802" _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 1 0 2249 "3802" _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"); diff --git a/src/test/regress/expected/json.out b/src/test/regress/expected/json.out index d1e32a19a52891dee8e2f5aaf500b40006fc11b2..99036a23ca865d20539aeb9db88636a17a00d645 100644 *** a/src/test/regress/expected/json.out --- b/src/test/regress/expected/json.out *************** select * from json_populate_record(row(' *** 943,1020 **** blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+--- blurfl | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -----------------+---+--- [100,200,false] | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -----------------+---+-------------------------- [100,200,false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q; ERROR: invalid input syntax for type timestamp: "[100,200,false]" -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c ---------------+----+-------------------------- [100,200,300] | 99 | {"z":true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ERROR: invalid input syntax for type timestamp: "[100,200,300]" create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q; a | b | c | d ---+---------+---+--- 2 | {"z":4} | 3 | 6 (1 row) - -- using the default use_json_as_text argument select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- --- 943,1019 ---- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+--- blurfl | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -----------------+---+--- [100,200,false] | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -----------------+---+-------------------------- [100,200,false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q; ERROR: invalid input syntax for type timestamp: "[100,200,false]" -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c ---------------+----+-------------------------- [100,200,300] | 99 | {"z":true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ERROR: invalid input syntax for type timestamp: "[100,200,300]" create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q; a | b | c | d ---+---------+---+--- 2 | {"z":4} | 3 | 6 (1 row) select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- *************** select * from json_populate_recordset(ro *** 1030,1038 **** (2 rows) select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot call json_populate_recordset on a nested object ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot call json_populate_recordset on a nested object -- handling of unicode surrogate pairs select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8; correct_in_utf8 --- 1029,1040 ---- (2 rows) select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! a | b | c ! ---------------+----+-------------------------- ! [100,200,300] | 99 | ! {"z":true} | 3 | Fri Jan 20 10:42:53 2012 ! (2 rows) ! -- handling of unicode surrogate pairs select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8; correct_in_utf8 *************** ERROR: null value not allowed for objec *** 1215,1228 **** select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); ERROR: empty value not allowed for object key -- json_to_record and json_to_recordset ! select * from json_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 json_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 ---+-----+--- --- 1217,1230 ---- select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); ERROR: empty value not allowed for object key -- json_to_record and json_to_recordset ! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') as x(a int, b text, d text); a | b | d ---+-----+--- 1 | foo | (1 row) ! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') as x(a int, b text, c boolean); a | b | c ---+-----+--- *************** select * from json_to_recordset('[{"a":1 *** 1230,1236 **** 2 | bar | t (2 rows) ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true) as x(a int, b json, c boolean); a | b | c ---+-------------+--- --- 1232,1238 ---- 2 | bar | t (2 rows) ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') as x(a int, b json, c boolean); a | b | c ---+-------------+--- diff --git a/src/test/regress/expected/json_1.out b/src/test/regress/expected/json_1.out index 93cb693b2fbdec2bab654fbfdaf0e26d50cde51e..e74aabec8a1d3016d7fd483d9bbed30f7ed5deb9 100644 *** a/src/test/regress/expected/json_1.out --- b/src/test/regress/expected/json_1.out *************** select * from json_populate_record(row(' *** 943,1020 **** blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+--- blurfl | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -----------------+---+--- [100,200,false] | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -----------------+---+-------------------------- [100,200,false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q; ERROR: invalid input syntax for type timestamp: "[100,200,false]" -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c ---------------+----+-------------------------- [100,200,300] | 99 | {"z":true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ERROR: invalid input syntax for type timestamp: "[100,200,300]" create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q; a | b | c | d ---+---------+---+--- 2 | {"z":4} | 3 | 6 (1 row) - -- using the default use_json_as_text argument select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- --- 943,1019 ---- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+--- blurfl | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -----------------+---+--- [100,200,false] | | (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -----------------+---+-------------------------- [100,200,false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q; ERROR: invalid input syntax for type timestamp: "[100,200,false]" -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c ---------------+----+-------------------------- [100,200,300] | 99 | {"z":true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ERROR: invalid input syntax for type timestamp: "[100,200,300]" create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q; a | b | c | d ---+---------+---+--- 2 | {"z":4} | 3 | 6 (1 row) select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+---+-------------------------- *************** select * from json_populate_recordset(ro *** 1030,1038 **** (2 rows) select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot call json_populate_recordset on a nested object ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot call json_populate_recordset on a nested object -- handling of unicode surrogate pairs select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8; ERROR: invalid input syntax for type json --- 1029,1040 ---- (2 rows) select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! a | b | c ! ---------------+----+-------------------------- ! [100,200,300] | 99 | ! {"z":true} | 3 | Fri Jan 20 10:42:53 2012 ! (2 rows) ! -- handling of unicode surrogate pairs select json '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a' as correct_in_utf8; ERROR: invalid input syntax for type json *************** ERROR: null value not allowed for objec *** 1211,1224 **** select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); ERROR: empty value not allowed for object key -- json_to_record and json_to_recordset ! select * from json_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 json_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 ---+-----+--- --- 1213,1226 ---- select json_object('{a,b,"","d e f"}','{1,2,3,"a b c"}'); ERROR: empty value not allowed for object key -- json_to_record and json_to_recordset ! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') as x(a int, b text, d text); a | b | d ---+-----+--- 1 | foo | (1 row) ! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') as x(a int, b text, c boolean); a | b | c ---+-----+--- *************** select * from json_to_recordset('[{"a":1 *** 1226,1232 **** 2 | bar | t (2 rows) ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true) as x(a int, b json, c boolean); a | b | c ---+-------------+--- --- 1228,1234 ---- 2 | bar | t (2 rows) ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') as x(a int, b json, c boolean); a | b | c ---+-------------+--- diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out index 6bc789de293df8c187f6be5ac4bf8c3b7f5b06c4..c1cc1a9dbec4c2f28a4794f953c293be2b056d66 100644 *** a/src/test/regress/expected/jsonb.out --- b/src/test/regress/expected/jsonb.out *************** SELECT * FROM jsonb_populate_record(row( *** 1297,1367 **** blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+--- blurfl | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -------------------+---+--- [100, 200, false] | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -------------------+---+-------------------------- [100, 200, false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true) q; ERROR: invalid input syntax for type timestamp: "[100, 200, false]" -- populate_recordset ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c -----------------+----+-------------------------- [100, 200, 300] | 99 | {"z": true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ERROR: invalid input syntax for type timestamp: "[100, 200, 300]" - -- using the default use_json_as_text argument 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 --------+---+-------------------------- --- 1297,1366 ---- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+--- blurfl | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -------------------+---+--- [100, 200, false] | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -------------------+---+-------------------------- [100, 200, false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! 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]" -- 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 --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! 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 --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c -----------------+----+-------------------------- [100, 200, 300] | 99 | {"z": true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ERROR: invalid input syntax for type timestamp: "[100, 200, 300]" 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 --------+---+-------------------------- *************** SELECT * FROM jsonb_populate_recordset(r *** 1377,1385 **** (2 rows) SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot populate with a nested object unless use_json_as_text is true ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot populate with a nested object unless use_json_as_text is true -- handling of unicode surrogate pairs SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; correct_in_utf8 --- 1376,1387 ---- (2 rows) SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! a | b | c ! -----------------+----+-------------------------- ! [100, 200, 300] | 99 | ! {"z": true} | 3 | Fri Jan 20 10:42:53 2012 ! (2 rows) ! -- handling of unicode surrogate pairs SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; correct_in_utf8 *************** SELECT jsonb '{ "a": "null \u0000 escap *** 1431,1444 **** (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 ---+-----+--- --- 1433,1446 ---- (1 row) -- jsonb_to_record and jsonb_to_recordset ! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') 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}]') as x(a int, b text, c boolean); a | b | c ---+-----+--- diff --git a/src/test/regress/expected/jsonb_1.out b/src/test/regress/expected/jsonb_1.out index 0c861d3b2940db051cf0c9040c7f86fe735fa92d..249f5758442d6277322bc9809d5dcc5d2ce06c5f 100644 *** a/src/test/regress/expected/jsonb_1.out --- b/src/test/regress/expected/jsonb_1.out *************** SELECT * FROM jsonb_populate_record(row( *** 1297,1367 **** blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+--- blurfl | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -------------------+---+--- [100, 200, false] | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; a | b | c -------------------+---+-------------------------- [100, 200, false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true) q; ERROR: invalid input syntax for type timestamp: "[100, 200, false]" -- populate_recordset ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; a | b | c -----------------+----+-------------------------- [100, 200, 300] | 99 | {"z": true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ERROR: invalid input syntax for type timestamp: "[100, 200, 300]" - -- using the default use_json_as_text argument 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 --------+---+-------------------------- --- 1297,1366 ---- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+--- blurfl | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; a | b | c --------+---+-------------------------- blurfl | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -------------------+---+--- [100, 200, false] | | (1 row) ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q; a | b | c -------------------+---+-------------------------- [100, 200, false] | 3 | Mon Dec 31 15:30:56 2012 (1 row) ! 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]" -- 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 --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! 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 --------+---+-------------------------- blurfl | | | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c --------+----+-------------------------- blurfl | 99 | def | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; a | b | c -----------------+----+-------------------------- [100, 200, 300] | 99 | {"z": true} | 3 | Fri Jan 20 10:42:53 2012 (2 rows) ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ERROR: invalid input syntax for type timestamp: "[100, 200, 300]" 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 --------+---+-------------------------- *************** SELECT * FROM jsonb_populate_recordset(r *** 1377,1385 **** (2 rows) SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot populate with a nested object unless use_json_as_text is true ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! ERROR: cannot populate with a nested object unless use_json_as_text is true -- handling of unicode surrogate pairs SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; ERROR: invalid input syntax for type json --- 1376,1387 ---- (2 rows) SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! a | b | c ! -----------------+----+-------------------------- ! [100, 200, 300] | 99 | ! {"z": true} | 3 | Fri Jan 20 10:42:53 2012 ! (2 rows) ! -- handling of unicode surrogate pairs SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; ERROR: invalid input syntax for type json *************** SELECT jsonb '{ "a": "null \u0000 escap *** 1431,1444 **** (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 ---+-----+--- --- 1433,1446 ---- (1 row) -- jsonb_to_record and jsonb_to_recordset ! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') 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}]') as x(a int, b text, c boolean); a | b | c ---+-----+--- diff --git a/src/test/regress/sql/json.sql b/src/test/regress/sql/json.sql index bc8bb62978155f672b5ab4fafd69d7cbd74869e1..3215b61a5a81100546ed415f15ddebfb8020b3db 100644 *** a/src/test/regress/sql/json.sql --- b/src/test/regress/sql/json.sql *************** create type jpop as (a text, b int, c ti *** 309,339 **** select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}', true) q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}', true) q; ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}', true) q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}', true) q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}', true) q; -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]',true) q; ! ! -- using the default use_json_as_text argument select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; - select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; -- handling of unicode surrogate pairs --- 309,336 ---- select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; ! select * from json_populate_record(null::jpop,'{"a":"blurfl","x":43.2}') q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":"blurfl","x":43.2}') q; ! select * from json_populate_record(null::jpop,'{"a":[100,200,false],"x":43.2}') q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"a":[100,200,false],"x":43.2}') q; ! select * from json_populate_record(row('x',3,'2012-12-31 15:30:56')::jpop,'{"c":[100,200,false],"x":43.2}') q; -- populate_recordset ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; ! select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; ! select * from json_populate_recordset(row('def',99,null)::jpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; create type jpop2 as (a int, b json, c int, d int); ! select * from json_populate_recordset(null::jpop2, '[{"a":2,"c":3,"b":{"z":4},"d":6}]') q; select * from json_populate_recordset(null::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; select * from json_populate_recordset(row('def',99,null)::jpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; -- handling of unicode surrogate pairs *************** select json_object('{a,b,"","d e f"}','{ *** 445,455 **** -- json_to_record and json_to_recordset ! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}',true) as x(a int, b text, d text); ! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]',false) as x(a int, b text, c boolean); ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]', true) as x(a int, b json, c boolean); --- 442,452 ---- -- json_to_record and json_to_recordset ! select * from json_to_record('{"a":1,"b":"foo","c":"bar"}') as x(a int, b text, d text); ! select * from json_to_recordset('[{"a":1,"b":"foo","d":false},{"a":2,"b":"bar","c":true}]') as x(a int, b text, c boolean); ! select * from json_to_recordset('[{"a":1,"b":{"d":"foo"},"c":true},{"a":2,"c":false,"b":{"d":"bar"}}]') as x(a int, b json, c boolean); diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql index 7527925b2cb1ea98a73065b0fa004b5fd78c1033..187a8e8ccc99473303dffddb624046d921d6187d 100644 *** a/src/test/regress/sql/jsonb.sql --- b/src/test/regress/sql/jsonb.sql *************** CREATE TYPE jbpop AS (a text, b int, c t *** 285,313 **** SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}', true) q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}', true) q; ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}', true) q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}', true) q; -- populate_recordset ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',false) q; ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]',true) q; ! ! -- using the default use_json_as_text argument SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; -- handling of unicode surrogate pairs SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order --- 285,311 ---- SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":"blurfl","x":43.2}') q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":"blurfl","x":43.2}') q; ! SELECT * FROM jsonb_populate_record(NULL::jbpop,'{"a":[100,200,false],"x":43.2}') q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"a":[100,200,false],"x":43.2}') q; ! SELECT * FROM jsonb_populate_record(row('x',3,'2012-12-31 15:30:56')::jbpop,'{"c":[100,200,false],"x":43.2}') q; -- populate_recordset ! SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; ! SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"c":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; + SELECT * FROM jsonb_populate_recordset(NULL::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; + SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":"blurfl","x":43.2},{"b":3,"c":"2012-01-20 10:42:53"}]') q; + SELECT * FROM jsonb_populate_recordset(row('def',99,NULL)::jbpop,'[{"a":[100,200,300],"x":43.2},{"a":{"z":true},"b":3,"c":"2012-01-20 10:42:53"}]') q; -- handling of unicode surrogate pairs + SELECT octet_length((jsonb '{ "a": "\ud83d\ude04\ud83d\udc36" }' -> 'a')::text) AS correct_in_utf8; SELECT jsonb '{ "a": "\ud83d\ud83d" }' -> 'a'; -- 2 high surrogates in a row SELECT jsonb '{ "a": "\ude04\ud83d" }' -> 'a'; -- surrogates in wrong order *************** SELECT jsonb '{ "a": "null \u0000 escap *** 321,330 **** -- 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 --- 319,328 ---- -- jsonb_to_record and jsonb_to_recordset ! select * from jsonb_to_record('{"a":1,"b":"foo","c":"bar"}') 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}]') as x(a int, b text, c boolean); -- indexing