Commits

Andrew Dunstan  committed f7681a1

add use_text_as_json option for populate_recordset

  • Participants
  • Parent commits e62fa09

Comments (0)

Files changed (4)

File src/backend/catalog/system_views.sql

 CREATE OR REPLACE FUNCTION
   pg_start_backup(label text, fast boolean DEFAULT false)
   RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
+
+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';

File src/backend/utils/adt/jsonfuncs.c

 	JsonLexContext *lex;
 	HTAB	   *json_hash;
 	char	   *saved_scalar;
+	char	   *save_json_start;
+	bool		use_json_as_text;
 	Tuplestorestate *tuple_store;
 	TupleDesc	ret_tdesc;
 	HeapTupleHeader rec;
  * set fields in a set of records from the argument json,
  * which must be an array of objects.
  *
- * similar to jsonb_populate_record, but the tuple-building code
+ * similar to json_populate_record, but the tuple-building code
  * is pushed down into the semantic action handlers so it's done
  * per object in the array.
  */
 {
 	Oid			argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
 	text	   *json = PG_GETARG_TEXT_P(1);
+	bool		use_json_as_text = PG_GETARG_BOOL(2);
 	char	   *jsonstr;
 	ReturnSetInfo *rsi;
 	MemoryContext old_cxt;
 
 	state->my_extra = my_extra;
 	state->rec = rec;
+	state->use_json_as_text = use_json_as_text;
 	state->fn_mcxt = fcinfo->flinfo->fn_mcxt;
 
 	pg_parse_json(lex, sem);
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("cannot call populate_recordset on an object")));
-	else if (lex_level > 1)
+	else if (lex_level > 1 && !_state->use_json_as_text)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-			  errmsg("cannot call populate_recordset with nested objects")));
+				 errmsg("cannot call populate_recordset with nested objects")));
 
 	/* set up a new hash for this entry */
 	memset(&ctl, 0, sizeof(ctl));
 	HeapTupleHeader rec = _state->rec;
 	HeapTuple	rettuple;
 
+	if (_state->lex->lex_level > 1)
+		return;
+
 	values = (Datum *) palloc(ncolumns * sizeof(Datum));
 	nulls = (bool *) palloc(ncolumns * sizeof(bool));
 
 {
 	PopulateRecordsetState _state = (PopulateRecordsetState) state;
 
-	if (_state->lex->lex_level != 0)
+	if (_state->lex->lex_level != 0 && ! _state->use_json_as_text)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-			   errmsg("cannot call populate_recordset with nested arrays")));
+				 errmsg("cannot call populate_recordset with nested arrays")));
 }
 
 static void
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 				 errmsg("cannot call populate_recordset on a scalar")));
 
-	_state->saved_scalar = token;
+	if (_state->lex->lex_level == 2)
+		_state->saved_scalar = token;
 }
 
 static void
 {
 	PopulateRecordsetState _state = (PopulateRecordsetState) state;
 
+	if (_state->lex->lex_level > 2)
+		return;
+
 	if (_state->lex->token_type == JSON_TOKEN_ARRAY_START ||
 		_state->lex->token_type == JSON_TOKEN_OBJECT_START)
 	{
-		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
-			   errmsg("cannot call populate_recordset on a nested object")));
+		if (!_state->use_json_as_text)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("cannot call populate_recordset on a nested object")));
+		_state->save_json_start = _state->lex->token_start;
+	}
+	else
+	{
+		_state->save_json_start = NULL;
 	}
 }
 
 	bool		found;
 	char		name[NAMEDATALEN];
 
-	/* ignore field names >= NAMEDATALEN - they can't match a record field */
-	if (strlen(fname) >= NAMEDATALEN)
+	/* 
+	 * ignore field names >= NAMEDATALEN - they can't match a record field 
+	 * ignore nested fields.
+	 */
+	if (_state->lex->lex_level > 2 || strlen(fname) >= NAMEDATALEN)
 		return;
 
 	memset(name, 0, NAMEDATALEN);
 				 errmsg("duplicate object field name: \"%s\"", fname)));
 
 	hashentry->isnull = isnull;
-	hashentry->val = _state->saved_scalar;
+	if (_state->save_json_start != NULL)
+	{
+		int len = _state->lex->prev_token_terminator - _state->save_json_start;
+		char *val = palloc((len+1) * sizeof(char));
+		memcpy(val, _state->save_json_start,len);
+		val[len] = '\0';
+		hashentry->val = val;
+	}
+	else
+	{
+		/* must have had a scalar instead */
+		hashentry->val = _state->saved_scalar;
+	}
 }

File src/include/catalog/pg_proc.h

 DESCR("key value pairs of a json object");
 DATA(insert OID = 5012 (  json_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 2 0 2283 "2283 114" _null_ _null_ _null_ _null_ json_populate_record _null_ _null_ _null_ ));
 DESCR("get record fields from a json object");
-DATA(insert OID = 5013 (  json_populate_recordset PGNSP PGUID 12 1 100 0 0 f f f f f t s 2 0 2283 "2283 114" _null_ _null_ _null_ _null_ json_populate_recordset _null_ _null_ _null_ ));
-DESCR("get record fields from a json object");
+DATA(insert OID = 5013 (  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");
 
 /* uuid */
 DATA(insert OID = 2952 (  uuid_in		   PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2950 "2275" _null_ _null_ _null_ _null_ uuid_in _null_ _null_ _null_ ));

File src/test/regress/sql/json.sql

 
 -- 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;
+
+
+-- 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;