Commits

Andrew Dunstan committed f880334

use_json_as_text for populate_record

  • Participants
  • Parent commits 5437792

Comments (0)

Files changed (5)

src/backend/catalog/system_views.sql

   RETURNS text 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';

src/backend/utils/adt/jsonfuncs.c

 static void unnest_scalar(void *state, char *token, JsonTokenType tokentype);
 
 /* turn a json object into a hash table */
-static HTAB *get_json_object_as_hash(char *jsonstr, char *funcname);
+static HTAB *get_json_object_as_hash(char *jsonstr, char *funcname, bool use_json_as_text);
 
 /* semantic action functions for get_json_object_as_hash */
 static void hash_object_field_start(void *state, char *fname, bool isnull);
 	JsonLexContext *lex;
 	HTAB	   *hash;
 	char	   *saved_scalar;
+	char	   *save_json_start;
+	bool		use_json_as_text;
 	char	   *function_name;
 }	jhashState, *JHashState;
 
 {
 	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 = text_to_cstring(json);
 	HTAB	   *json_hash;
 	HeapTupleHeader rec;
 		tupTypmod = HeapTupleHeaderGetTypMod(rec);
 	}
 
-	json_hash = get_json_object_as_hash(jsonstr, "json_populate_record");
+	json_hash = get_json_object_as_hash(jsonstr, "json_populate_record", use_json_as_text);
 
 	/*
 	 * if the input json is empty, we can only skip the rest if we were passed
  * error messages.
  */
 static HTAB *
-get_json_object_as_hash(char *jsonstr, char *funcname)
+get_json_object_as_hash(char *jsonstr, char *funcname, bool use_json_as_text)
 {
 	HASHCTL		ctl;
 	HTAB	   *tab;
 	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;
 {
 	JHashState	_state = (JHashState) state;
 
+	if (_state->lex->lex_level > 1)
+		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 %s on a nested object", _state->function_name)));
+		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
+	{
+		/* must be a scalar */
+		_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;
+	}
 }
 
 static void
 				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 			   errmsg("cannot call %s on a scalar", _state->function_name)));
 
-	_state->saved_scalar = token;
+	if (_state->lex->lex_level == 1)
+		_state->saved_scalar = token;
 }
 
 

src/include/catalog/pg_proc.h

 DESCR("get value from json as text with path elements");
 DATA(insert OID = 5011 (  json_each_as_text PGNSP PGUID 12 1 100 0 0 f f f f t t s 1 0 2249 "114" "{114,25,25}" "{i,o,o}" "{from_json,key,value}" _null_ json_each_as_text _null_ _null_ _null_ ));
 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_ ));
+DATA(insert OID = 5012 (  json_populate_record PGNSP PGUID 12 1 0 0 0 f f f f f f s 3 0 2283 "2283 114 16" _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 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");

src/test/regress/expected/json.out

  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             

src/test/regress/sql/json.sql

 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":[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;