Add jsonb type support for pg >- 90400

This change is backward-incompatible with existing applications that assume jsonb type to be interpreted in a string rather than a json object at the function boundary. The change is resonable as plv8 already does auto-coercion for the json type. Edits to Makefile and cosmetic issues by Hitoshi Harada.
plv8 · May 25, 2015 · 2e09144 · 2e09144 · obartunov · May 27, 2015
1 parent 79d862b
commit 2e09144
Show file tree

Hide file tree

Showing 5 changed files with 127 additions and 16 deletions.
diff --git a/Makefile b/Makefile
@@ -37,7 +37,7 @@ DATA += plcoffee.control plcoffee--$(PLV8_VERSION).sql \
 endif
 DATA_built = plv8.sql
 REGRESS = init-extension plv8 inline json startup_pre startup varparam json_conv \
-		  window
+		  jsonb_conv window
 ifndef DISABLE_DIALECT
 REGRESS += dialect
 endif
@@ -88,21 +88,7 @@ all: $(DATA)
 subclean:
 	rm -f plv8_config.h $(DATA) $(JSCS)
 
-ifeq ($(shell test $(PG_VERSION_NUM) -lt 90200 && echo yes), yes)
-REGRESS := $(filter-out json_conv, $(REGRESS))
-endif
-
-else # < 9.1
-
-ifeq ($(shell test $(PG_VERSION_NUM) -ge 90000 && echo yes), yes)
-REGRESS := init $(filter-out init-extension dialect json_conv, $(REGRESS))
-
-else # < 9.0
-
-REGRESS := init $(filter-out init-extension inline startup \
-					varparam dialect json_conv window, $(REGRESS))
-
-endif
+else
 
 DATA = uninstall_plv8.sql
 %.sql.in: plv8.sql.common
@@ -112,6 +98,26 @@ subclean:
 
 endif
 
+# < 9.4, drop jsonb_conv
+ifeq ($(shell test $(PG_VERSION_NUM) -lt 90400 && echo yes), yes)
+REGRESS := $(filter-out jsonb_conv, $(REGRESS))
+endif
+
+# < 9.2, drop json_conv
+ifeq ($(shell test $(PG_VERSION_NUM) -lt 90200 && echo yes), yes)
+REGRESS := $(filter-out json_conv, $(REGRESS))
+endif
+
+# < 9.1, drop init-extension and dialect, add init at the beginning
+ifeq ($(shell test $(PG_VERSION_NUM) -lt 90100 && echo yes), yes)
+REGRESS := init $(filter-out init-extension dialect, $(REGRESS))
+endif
+
+# < 9.0, drop inline, startup, varparam and window
+ifeq ($(shell test $(PG_VERSION_NUM) -lt 90000 && echo yes), yes)
+REGRESS := $(filter-out inline startup varparam window, $(REGRESS))
+endif
+
 clean: subclean
 
 # build will be created by Makefile.v8

diff --git a/doc/plv8.md b/doc/plv8.md
@@ -160,6 +160,7 @@ automatically.  If the desired database type is one of
 - timestamptz
 - bytea
 - json (>= 9.2)
+- jsonb (>= 9.4)
 
 and the JS value looks compatible, then the conversion succeeds.  Otherwise,
 PL/v8 tries to convert them via cstring representation.  An array type is

diff --git a/expected/jsonb_conv.out b/expected/jsonb_conv.out
@@ -0,0 +1,45 @@
+CREATE FUNCTION convb(o jsonb) RETURNS jsonb AS $$
+if (o instanceof Array) {
+	o[1] = 10;
+} else if (typeof(o) == 'object') {
+	o.i = 10;
+}
+return o;
+$$ LANGUAGE plv8;
+SELECT convb('{"i": 3, "b": 20}');
+       convb        
+--------------------
+ {"b": 20, "i": 10}
+(1 row)
+
+SELECT convb('[1, 2, 3]');
+   convb    
+------------
+ [1, 10, 3]
+(1 row)
+
+CREATE FUNCTION get_keyb(key text, json_raw jsonb) RETURNS jsonb
+LANGUAGE plv8 IMMUTABLE STRICT
+AS $$
+  var val = json_raw[key];
+  var ret = {};
+  ret[key] = val;
+  return ret;
+$$;
+CREATE TABLE jsonbonly (
+    data jsonb
+);
+COPY jsonbonly (data) FROM stdin;
+-- Call twice to test the function cache.
+SELECT get_keyb('ok', data) FROM jsonbonly;
+   get_keyb   
+--------------
+ {"ok": true}
+(1 row)
+
+SELECT get_keyb('ok', data) FROM jsonbonly;
+   get_keyb   
+--------------
+ {"ok": true}
+(1 row)
+
diff --git a/plv8_type.cc b/plv8_type.cc
@@ -22,6 +22,9 @@ extern "C" {
 #include "utils/date.h"
 #include "utils/datetime.h"
 #include "utils/builtins.h"
+#if PG_VERSION_NUM >= 90400
+#include "utils/jsonb.h"
+#endif
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 #include "utils/typcache.h"
@@ -300,6 +303,20 @@ ToScalarDatum(Handle<v8::Value> value, bool *isnull, plv8_type *type)
 				return PointerGetDatum(datum_p);
 			}
 		}
+#if PG_VERSION_NUM >= 90400
+	case JSONBOID:
+		if (value->IsObject() || value->IsArray())
+		{
+			JSONObject JSON;
+
+			Handle<v8::Value> result = JSON.Stringify(value);
+			CString str(result);
+
+			// lots of casting, but it ends up working - there is no CStringGetJsonb exposed
+			return (Datum) DatumGetJsonb(DirectFunctionCall1(jsonb_in, (Datum) (char *) str));
+		}
+		break;
+#endif
 #if PG_VERSION_NUM >= 90200
 	case JSONOID:
 		if (value->IsObject() || value->IsArray())
@@ -493,6 +510,16 @@ ToScalarValue(Datum datum, bool isnull, plv8_type *type)
 			pfree(p);	// free if detoasted
 		return result;
 	}
+#endif
+#if PG_VERSION_NUM >= 90400
+	case JSONBOID:
+	{
+		Local<v8::Value>	jsonString = ToString(datum, type);
+		JSONObject JSON;
+		Local<v8::Value> result = Local<v8::Value>::New(JSON.Parse(jsonString));
+
+		return result;
+	}
 #endif
 	default:
 		return ToString(datum, type);

diff --git a/sql/jsonb_conv.sql b/sql/jsonb_conv.sql
@@ -0,0 +1,32 @@
+CREATE FUNCTION convb(o jsonb) RETURNS jsonb AS $$
+if (o instanceof Array) {
+	o[1] = 10;
+} else if (typeof(o) == 'object') {
+	o.i = 10;
+}
+return o;
+$$ LANGUAGE plv8;
+
+SELECT convb('{"i": 3, "b": 20}');
+SELECT convb('[1, 2, 3]');
+
+CREATE FUNCTION get_keyb(key text, json_raw jsonb) RETURNS jsonb
+LANGUAGE plv8 IMMUTABLE STRICT
+AS $$
+  var val = json_raw[key];
+  var ret = {};
+  ret[key] = val;
+  return ret;
+$$;
+
+CREATE TABLE jsonbonly (
+    data jsonb
+);
+
+COPY jsonbonly (data) FROM stdin;
+{"ok": true}
+\.
+
+-- Call twice to test the function cache.
+SELECT get_keyb('ok', data) FROM jsonbonly;
+SELECT get_keyb('ok', data) FROM jsonbonly;