On Thu, 09 Nov 2017 12:26:46 +0000
Aleksander Alekseev <a.aleks...@postgrespro.ru> wrote:
> The following review has been posted through the commitfest
> application: make installcheck-world: tested, failed
> Implements feature: tested, passed
> Spec compliant: tested, passed
> Documentation: tested, passed
>
> Hello Anthony,
>
> Great job!
>
> I decided to take a closer look on your patch. Here are some defects
> I discovered.
>
> > + Additional extensions are available that implement transforms
> > for
> > + the <type>jsonb</type> type for the language PL/Python. The
> > + extensions for PL/Perl are called
>
> 1. The part regarding PL/Perl is obviously from another patch.
>
> 2. jsonb_plpython2u and jsonb_plpythonu are marked as relocatable,
> while jsonb_plpython3u is not. Is it a mistake? Anyway if an
> extension is relocatable there should be a test that checks this.
>
> 3. Not all json types are test-covered. Tests for 'true' :: jsonb,
> '3.14' :: jsonb and 'null' :: jsonb are missing.
>
> 4. jsonb_plpython.c:133 - "Iterate trhrough Jsonb object." Typo, it
> should be "through" or probably even "over".
>
> 5. It looks like you've implemented transform in two directions
> Python <-> JSONB, however I see tests only for Python <- JSONB case.
>
> 6. Tests passed on Python 2.7.14 but failed on 3.6.2:
>
> > CREATE EXTENSION jsonb_plpython3u CASCADE;
> > + ERROR: could not access file "$libdir/jsonb_plpython3": No such
> > file or directory
>
> module_pathname in jsonb_plpython3u.control should be
> $libdir/jsonb_plpython3u, not $libdir/jsonb_plpython3.
>
> Tested on Arch Linux x64, GCC 7.2.0.
>
> The new status of this patch is: Waiting on Author
>
Hello, Aleksander.
Thank you for your time. The defects you have noticed were fixed.
Please, find in attachments new version of the patch (it is called
0001-jsonb_plpython-extension-v2.patch).
Most of changes were made to fix defects(list of the defects may be
found in citation in the beginning of this message), but the algorithm
of iterating through incoming jsonb was changed so that it looks tidier.
--
Anthony Bykov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/contrib/Makefile b/contrib/Makefile
index 8046ca4..d9d9817 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -81,9 +81,9 @@ ALWAYS_SUBDIRS += hstore_plperl
endif
ifeq ($(with_python),yes)
-SUBDIRS += hstore_plpython ltree_plpython
+SUBDIRS += hstore_plpython ltree_plpython jsonb_plpython
else
-ALWAYS_SUBDIRS += hstore_plpython ltree_plpython
+ALWAYS_SUBDIRS += hstore_plpython ltree_plpython jsonb_plpython
endif
# Missing:
diff --git a/contrib/jsonb_plpython/Makefile b/contrib/jsonb_plpython/Makefile
new file mode 100644
index 0000000..6371d11
--- /dev/null
+++ b/contrib/jsonb_plpython/Makefile
@@ -0,0 +1,39 @@
+# contrib/jsonb_plpython/Makefile
+
+MODULE_big = jsonb_plpython$(python_majorversion)u
+OBJS = jsonb_plpython.o $(WIN32RES)
+PGFILEDESC = "jsonb_plpython - transform between jsonb and plpythonu"
+
+PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plpython $(python_includespec) -DPLPYTHON_LIBNAME='"plpython$(python_majorversion)"'
+
+EXTENSION = jsonb_plpythonu jsonb_plpython2u jsonb_plpython3u
+DATA = jsonb_plpythonu--1.0.sql jsonb_plpython2u--1.0.sql jsonb_plpython3u--1.0.sql
+
+REGRESS = jsonb_plpython$(python_majorversion)
+REGRESS_PLPYTHON3_MANGLE := $(REGRESS)
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/jsonb_plpython
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
+
+# We must link libpython explicitly
+ifeq ($(PORTNAME), win32)
+# ... see silliness in plpython Makefile ...
+SHLIB_LINK += $(sort $(wildcard ../../src/pl/plpython/libpython*.a))
+else
+rpathdir = $(python_libdir)
+SHLIB_LINK += $(python_libspec) $(python_additional_libs)
+endif
+
+ifeq ($(python_majorversion),2)
+REGRESS_OPTS += --load-extension=plpython2u
+else
+REGRESS_OPTS += --load-extension=plpython3u
+endif
diff --git a/contrib/jsonb_plpython/expected/jsonb_plpython2.out b/contrib/jsonb_plpython/expected/jsonb_plpython2.out
new file mode 100644
index 0000000..8ad5338
--- /dev/null
+++ b/contrib/jsonb_plpython/expected/jsonb_plpython2.out
@@ -0,0 +1,478 @@
+CREATE EXTENSION jsonb_plpython2u CASCADE;
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+INFO: [('a', Decimal('1')), ('c', 'NULL')]
+ test1
+-------
+ 2
+(1 row)
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+ test1complex
+--------------
+ 1
+(1 row)
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+ test1arr
+----------
+ 1
+(1 row)
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+SELECT test2arr('[12,1]'::jsonb);
+ test2arr
+----------
+ 2
+(1 row)
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+ test3arr
+----------
+ 2
+(1 row)
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+SELECT test1int('1'::jsonb);
+ test1int
+----------
+ 1
+(1 row)
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+SELECT test1string('"a"'::jsonb);
+ test1string
+-------------
+ 1
+(1 row)
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+SELECT test1null('"a"'::jsonb);
+ERROR: AssertionError:
+CONTEXT: Traceback (most recent call last):
+ PL/Python function "test1null", line 2, in <module>
+ assert(val == None)
+PL/Python function "test1null"
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+SELECT back('null'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('1'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('true'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('"string"'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('{"1":null}'::jsonb);
+ back
+-------------
+ {"1": null}
+(1 row)
+
+SELECT back('{"1":1}'::jsonb);
+ back
+----------
+ {"1": 1}
+(1 row)
+
+SELECT back('{"1":true}'::jsonb);
+ back
+-------------
+ {"1": true}
+(1 row)
+
+SELECT back('{"1":"string"}'::jsonb);
+ back
+-----------------
+ {"1": "string"}
+(1 row)
+
+SELECT back('[null]'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('[1]'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('[true]'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('["string"]'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('[null,1]'::jsonb);
+ back
+-----------
+ [null, 1]
+(1 row)
+
+SELECT back('[1,true]'::jsonb);
+ back
+-----------
+ [1, true]
+(1 row)
+
+SELECT back('[true,"string"]'::jsonb);
+ back
+------------------
+ [true, "string"]
+(1 row)
+
+SELECT back('["string","string2"]'::jsonb);
+ back
+-----------------------
+ ["string", "string2"]
+(1 row)
+
+DROP EXTENSION plpython2u CASCADE;
+NOTICE: drop cascades to 10 other objects
+DETAIL: drop cascades to extension jsonb_plpython2u
+drop cascades to function test1(jsonb)
+drop cascades to function test1complex(jsonb)
+drop cascades to function test1arr(jsonb)
+drop cascades to function test2arr(jsonb)
+drop cascades to function test3arr(jsonb)
+drop cascades to function test1int(jsonb)
+drop cascades to function test1string(jsonb)
+drop cascades to function test1null(jsonb)
+drop cascades to function back(jsonb)
+-- Testing plpythonu extension.
+CREATE EXTENSION jsonb_plpythonu CASCADE;
+NOTICE: installing required extension "plpythonu"
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+INFO: [('a', Decimal('1')), ('c', 'NULL')]
+ test1
+-------
+ 2
+(1 row)
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+ test1complex
+--------------
+ 1
+(1 row)
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+ test1arr
+----------
+ 1
+(1 row)
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+SELECT test2arr('[12,1]'::jsonb);
+ test2arr
+----------
+ 2
+(1 row)
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+ test3arr
+----------
+ 2
+(1 row)
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+SELECT test1int('1'::jsonb);
+ test1int
+----------
+ 1
+(1 row)
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+SELECT test1string('"a"'::jsonb);
+ test1string
+-------------
+ 1
+(1 row)
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+SELECT test1null('"a"'::jsonb);
+ERROR: AssertionError:
+CONTEXT: Traceback (most recent call last):
+ PL/Python function "test1null", line 2, in <module>
+ assert(val == None)
+PL/Python function "test1null"
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+SELECT back('null'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('1'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('true'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('"string"'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('{"1":null}'::jsonb);
+ back
+-------------
+ {"1": null}
+(1 row)
+
+SELECT back('{"1":1}'::jsonb);
+ back
+----------
+ {"1": 1}
+(1 row)
+
+SELECT back('{"1":true}'::jsonb);
+ back
+-------------
+ {"1": true}
+(1 row)
+
+SELECT back('{"1":"string"}'::jsonb);
+ back
+-----------------
+ {"1": "string"}
+(1 row)
+
+SELECT back('[null]'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('[1]'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('[true]'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('["string"]'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('[null,1]'::jsonb);
+ back
+-----------
+ [null, 1]
+(1 row)
+
+SELECT back('[1,true]'::jsonb);
+ back
+-----------
+ [1, true]
+(1 row)
+
+SELECT back('[true,"string"]'::jsonb);
+ back
+------------------
+ [true, "string"]
+(1 row)
+
+SELECT back('["string","string2"]'::jsonb);
+ back
+-----------------------
+ ["string", "string2"]
+(1 row)
+
+DROP EXTENSION plpythonu CASCADE;
+NOTICE: drop cascades to 10 other objects
+DETAIL: drop cascades to extension jsonb_plpythonu
+drop cascades to function test1(jsonb)
+drop cascades to function test1complex(jsonb)
+drop cascades to function test1arr(jsonb)
+drop cascades to function test2arr(jsonb)
+drop cascades to function test3arr(jsonb)
+drop cascades to function test1int(jsonb)
+drop cascades to function test1string(jsonb)
+drop cascades to function test1null(jsonb)
+drop cascades to function back(jsonb)
diff --git a/contrib/jsonb_plpython/expected/jsonb_plpython3.out b/contrib/jsonb_plpython/expected/jsonb_plpython3.out
new file mode 100644
index 0000000..bcac5d3
--- /dev/null
+++ b/contrib/jsonb_plpython/expected/jsonb_plpython3.out
@@ -0,0 +1,238 @@
+CREATE EXTENSION jsonb_plpython3u CASCADE;
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+INFO: [('a', Decimal('1')), ('c', 'NULL')]
+ test1
+-------
+ 2
+(1 row)
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+ test1complex
+--------------
+ 1
+(1 row)
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+ test1arr
+----------
+ 1
+(1 row)
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+SELECT test2arr('[12,1]'::jsonb);
+ test2arr
+----------
+ 2
+(1 row)
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+ test3arr
+----------
+ 2
+(1 row)
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+SELECT test1int('1'::jsonb);
+ test1int
+----------
+ 1
+(1 row)
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+SELECT test1string('"a"'::jsonb);
+ test1string
+-------------
+ 1
+(1 row)
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+SELECT test1null('"a"'::jsonb);
+ERROR: AssertionError:
+CONTEXT: Traceback (most recent call last):
+ PL/Python function "test1null", line 2, in <module>
+ assert(val == None)
+PL/Python function "test1null"
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+SELECT back('null'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('1'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('true'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('"string"'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('{"1":null}'::jsonb);
+ back
+-------------
+ {"1": null}
+(1 row)
+
+SELECT back('{"1":1}'::jsonb);
+ back
+----------
+ {"1": 1}
+(1 row)
+
+SELECT back('{"1":true}'::jsonb);
+ back
+-------------
+ {"1": true}
+(1 row)
+
+SELECT back('{"1":"string"}'::jsonb);
+ back
+-----------------
+ {"1": "string"}
+(1 row)
+
+SELECT back('[null]'::jsonb);
+ back
+--------
+ [null]
+(1 row)
+
+SELECT back('[1]'::jsonb);
+ back
+------
+ [1]
+(1 row)
+
+SELECT back('[true]'::jsonb);
+ back
+--------
+ [true]
+(1 row)
+
+SELECT back('["string"]'::jsonb);
+ back
+------------
+ ["string"]
+(1 row)
+
+SELECT back('[null,1]'::jsonb);
+ back
+-----------
+ [null, 1]
+(1 row)
+
+SELECT back('[1,true]'::jsonb);
+ back
+-----------
+ [1, true]
+(1 row)
+
+SELECT back('[true,"string"]'::jsonb);
+ back
+------------------
+ [true, "string"]
+(1 row)
+
+SELECT back('["string","string2"]'::jsonb);
+ back
+-----------------------
+ ["string", "string2"]
+(1 row)
+
+DROP EXTENSION plpython3u CASCADE;
+NOTICE: drop cascades to 10 other objects
+DETAIL: drop cascades to extension jsonb_plpython3u
+drop cascades to function test1(jsonb)
+drop cascades to function test1complex(jsonb)
+drop cascades to function test1arr(jsonb)
+drop cascades to function test2arr(jsonb)
+drop cascades to function test3arr(jsonb)
+drop cascades to function test1int(jsonb)
+drop cascades to function test1string(jsonb)
+drop cascades to function test1null(jsonb)
+drop cascades to function back(jsonb)
diff --git a/contrib/jsonb_plpython/jsonb_plpython.c b/contrib/jsonb_plpython/jsonb_plpython.c
new file mode 100644
index 0000000..6722337
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython.c
@@ -0,0 +1,418 @@
+/* This document contains an implementation of transformations from python
+ * object to jsonb and vise versa.
+ * In this file you can find implementation of transformations:
+ * - JsonbValue transformation in PyObject_FromJsonbValue
+ * - JsonbContainer(jsonb) transformation in PyObject_FromJsonb
+ * - PyMapping object(dict) transformation in PyMapping_ToJsonbValue
+ * - PyString object transformation in PyString_ToJsonbValue
+ * - PySequence object(list) transformation in PySequence_ToJsonbValue
+ * - PyNumeric object transformation in PyNumeric_ToJsonbValue
+ * - PyMapping object transformation in PyObject_ToJsonbValue
+ * */
+#include "postgres.h"
+
+#include "plpython.h"
+#include "plpy_typeio.h"
+
+#include "utils/jsonb.h"
+#include "utils/fmgrprotos.h"
+
+PG_MODULE_MAGIC;
+
+extern void _PG_init(void);
+
+/* Linkage to functions in plpython module */
+typedef char *(*PLyObject_AsString_t) (PyObject *plrv);
+static PLyObject_AsString_t PLyObject_AsString_p;
+#if PY_MAJOR_VERSION >= 3
+typedef PyObject *(*PLyUnicode_FromStringAndSize_t) (const char *s, Py_ssize_t size);
+static PLyUnicode_FromStringAndSize_t PLyUnicode_FromStringAndSize_p;
+#endif
+
+/*
+ * Module initialize function: fetch function pointers for cross-module calls.
+ */
+void
+_PG_init(void)
+{
+ /* Asserts verify that typedefs above match original declarations */
+ AssertVariableIsOfType(&PLyObject_AsString, PLyObject_AsString_t);
+ PLyObject_AsString_p = (PLyObject_AsString_t)
+ load_external_function("$libdir/" PLPYTHON_LIBNAME, "PLyObject_AsString",
+ true, NULL);
+#if PY_MAJOR_VERSION >= 3
+ AssertVariableIsOfType(&PLyUnicode_FromStringAndSize, PLyUnicode_FromStringAndSize_t);
+ PLyUnicode_FromStringAndSize_p = (PLyUnicode_FromStringAndSize_t)
+ load_external_function("$libdir/" PLPYTHON_LIBNAME, "PLyUnicode_FromStringAndSize",
+ true, NULL);
+#endif
+}
+
+
+/* These defines must be after the module init function */
+#define PLyObject_AsString PLyObject_AsString_p
+#define PLyUnicode_FromStringAndSize PLyUnicode_FromStringAndSize_p
+
+/*
+ * decimal_constructor is a link to Python library
+ * for transforming strings into python decimal type
+ * */
+static PyObject *decimal_constructor;
+
+static PyObject *PyObject_FromJsonb(JsonbContainer *jsonb);
+static JsonbValue *PyObject_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state);
+
+/*
+ * PyObject_FromJsonbValue(JsonsValue *jsonbValue)
+ * Function for transforming JsonbValue type into Python Object
+ * The first argument defines the JsonbValue which will be transformed into PyObject
+ * Return value is the pointer to Jsonb structure containing the transformed object.
+ * */
+static PyObject *
+PyObject_FromJsonbValue(JsonbValue *jsonbValue)
+{
+ PyObject *result;
+ char *str;
+
+ switch (jsonbValue->type)
+ {
+ case jbvNull:
+ result = Py_None;
+ break;
+ case jbvBinary:
+ result = PyObject_FromJsonb(jsonbValue->val.binary.data);
+ break;
+ case jbvNumeric:
+
+ /*
+ * XXX There should be a better way. Right now Numeric is
+ * transformed into string and then this string is parsed into py
+ * numeric
+ */
+ str = DatumGetCString(
+ DirectFunctionCall1(numeric_out, NumericGetDatum(jsonbValue->val.numeric))
+ );
+ result = PyObject_CallFunction(decimal_constructor, "s", str);
+ break;
+ case jbvString:
+ result = PyString_FromStringAndSize(
+ jsonbValue->val.string.val,
+ jsonbValue->val.string.len
+ );
+ break;
+ case jbvBool:
+ result = jsonbValue->val.boolean ? Py_True : Py_False;
+ break;
+ case jbvArray:
+ case jbvObject:
+ result = PyObject_FromJsonb(jsonbValue->val.binary.data);
+ break;
+ }
+ return (result);
+}
+
+/*
+ * PyObject_FromJsonb(JsonbContainer *jsonb)
+ * Function for transforming JsonbContainer(jsonb) into PyObject
+ * The first argument should represent the data for transformation.
+ * Return value is the pointer to Python object.
+ * */
+
+static PyObject *
+PyObject_FromJsonb(JsonbContainer *jsonb)
+{
+ PyObject *object;
+ PyObject *key;
+ PyObject *value;
+
+ JsonbIterator *it;
+ JsonbIteratorToken r;
+ JsonbValue v;
+
+ it = JsonbIteratorInit(jsonb);
+
+ r = JsonbIteratorNext(&it, &v, true);
+
+ switch (r)
+ {
+ case (WJB_BEGIN_ARRAY):
+ /* array in v */
+ object = PyList_New(0);
+ while ((r = JsonbIteratorNext(&it, &v, true)) == WJB_ELEM)
+ PyList_Append(object, PyObject_FromJsonbValue(&v));
+ break;
+ case (WJB_BEGIN_OBJECT):
+ object = PyDict_New();
+ while ((r = JsonbIteratorNext(&it, &v, true)) == WJB_KEY)
+ {
+ key = PyString_FromStringAndSize(
+ v.val.string.val,
+ v.val.string.len
+ );
+ r = JsonbIteratorNext(&it, &v, true);
+ value = PyObject_FromJsonbValue(&v);
+ PyDict_SetItem(object, key, value);
+ }
+ break;
+ case (WJB_END_OBJECT):
+ break;
+ default:
+ object = PyObject_FromJsonbValue(&v);
+ break;
+ }
+ return (object);
+}
+
+
+/*
+ * jsonb_to_plpython(Jsonb *in)
+ * Function to transform jsonb object to corresponding python object.
+ * The first argument is the Jsonb object to be transformed.
+ * Return value is the pointer to Python object.
+ * */
+PG_FUNCTION_INFO_V1(jsonb_to_plpython);
+Datum
+jsonb_to_plpython(PG_FUNCTION_ARGS)
+{
+ Jsonb *in;
+ PyObject *dict;
+ PyObject *decimal_module;
+
+ in = PG_GETARG_JSONB_P(0);
+
+ /* Import python cdecimal library and if there is no cdecimal library, */
+ /* import decimal library */
+ if (!decimal_constructor)
+ {
+ decimal_module = PyImport_ImportModule("cdecimal");
+ if (!decimal_module)
+ {
+ PyErr_Clear();
+ decimal_module = PyImport_ImportModule("decimal");
+ }
+ decimal_constructor = PyObject_GetAttrString(decimal_module, "Decimal");
+ }
+
+ dict = PyObject_FromJsonb(&in->root);
+ return PointerGetDatum(dict);
+}
+
+
+/*
+ * PyMapping_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state)
+ * Function to transform Python lists to jsonbValue
+ * The first argument is the python object to be transformed.
+ * Return value is the pointer to JsonbValue structure containing the list.
+ * */
+static JsonbValue *
+PyMapping_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state)
+{
+ volatile PyObject *items_v = NULL;
+ int32 pcount;
+ JsonbValue *out = NULL;
+
+ pcount = PyMapping_Size(obj);
+ items_v = PyMapping_Items(obj);
+
+ PG_TRY();
+ {
+ int32 i;
+ PyObject *items;
+ JsonbValue *jbvValue;
+ JsonbValue jbvKey;
+
+ items = (PyObject *) items_v;
+ pushJsonbValue(&jsonb_state, WJB_BEGIN_OBJECT, NULL);
+
+ for (i = 0; i < pcount; i++)
+ {
+ PyObject *tuple;
+ PyObject *key;
+ PyObject *value;
+
+ tuple = PyList_GetItem(items, i);
+ key = PyTuple_GetItem(tuple, 0);
+ value = PyTuple_GetItem(tuple, 1);
+
+ if (key == Py_None)
+ {
+ jbvKey.type = jbvString;
+ jbvKey.val.string.len = 0;
+ jbvKey.val.string.val = "";
+ }
+ else
+ {
+ jbvKey.type = jbvString;
+ jbvKey.val.string.val = PLyObject_AsString(key);
+ jbvKey.val.string.len = strlen(jbvKey.val.string.val);
+ }
+ pushJsonbValue(&jsonb_state, WJB_KEY, &jbvKey);
+ jbvValue = PyObject_ToJsonbValue(value, jsonb_state);
+ if (IsAJsonbScalar(jbvValue))
+ pushJsonbValue(&jsonb_state, WJB_VALUE, jbvValue);
+ }
+ out = pushJsonbValue(&jsonb_state, WJB_END_OBJECT, NULL);
+ }
+ PG_CATCH();
+ {
+ Py_DECREF(items_v);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+ return (out);
+}
+
+/*
+ * PyString_ToJsonbValue(PyObject *obj)
+ * Function to transform python string object to jsonbValue object.
+ * The first argument is the Python String object to be transformed.
+ * Return value is the pointer to JsonbValue structure containing the String.
+ * */
+static JsonbValue *
+PyString_ToJsonbValue(PyObject *obj)
+{
+ JsonbValue *out = NULL;
+ JsonbValue *jbvElem;
+
+ jbvElem = palloc(sizeof(JsonbValue));
+ jbvElem->type = jbvString;
+ jbvElem->val.string.val = PLyObject_AsString(obj);
+ jbvElem->val.string.len = strlen(jbvElem->val.string.val);
+ out = jbvElem;
+
+ return (out);
+}
+
+/*
+ * PySequence_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state)
+ * Function to transform python lists to jsonbValue object.
+ * The first argument is the Python list to be transformed.
+ * The second one is conversion state.
+ * Return value is the pointer to JsonbValue structure containing array.
+ * */
+static JsonbValue *
+PySequence_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state)
+{
+ JsonbValue *jbvElem;
+ JsonbValue *out = NULL;
+ int32 pcount;
+ int32 i;
+
+ pcount = PySequence_Size(obj);
+
+
+ pushJsonbValue(&jsonb_state, WJB_BEGIN_ARRAY, NULL);
+
+ for (i = 0; i < pcount; i++)
+ {
+ PyObject *value;
+
+ value = PySequence_GetItem(obj, i);
+ jbvElem = PyObject_ToJsonbValue(value, jsonb_state);
+ if (IsAJsonbScalar(jbvElem))
+ pushJsonbValue(&jsonb_state, WJB_ELEM, jbvElem);
+ }
+ out = pushJsonbValue(&jsonb_state, WJB_END_ARRAY, NULL);
+ return (out);
+}
+
+/*
+ * PyNumeric_ToJsonbValue(PyObject *obj)
+ * Function to transform python numerics to jsonbValue object.
+ * The first argument is the Python numeric object to be transformed.
+ * Return value is the pointer to JsonbValue structure containing the String.
+ * */
+static JsonbValue *
+PyNumeric_ToJsonbValue(PyObject *obj)
+{
+ JsonbValue *out = NULL;
+ JsonbValue *jbvInt;
+
+ jbvInt = palloc(sizeof(JsonbValue));
+ jbvInt->type = jbvNumeric;
+ jbvInt->val.numeric = DatumGetNumeric(DirectFunctionCall3(
+ numeric_in,
+ CStringGetDatum(PLyObject_AsString(obj)),
+ 0,
+ -1
+ ));
+ out = jbvInt;
+ return (out);
+}
+
+/*
+ * PyObject_ToJsonbValue(PyObject *obj)
+ * Function to transform python objects to jsonbValue object.
+ * The first argument is the Python object to be transformed.
+ * Return value is the pointer to JsonbValue structure containing the transformed object.
+ * */
+static JsonbValue *
+PyObject_ToJsonbValue(PyObject *obj, JsonbParseState *jsonb_state)
+{
+ JsonbValue *out = NULL;
+ PyObject *type = PyObject_Type(obj);
+
+ if (type == PyObject_Type(PyDict_New()))
+ {
+ /* DICT */
+ out = PyMapping_ToJsonbValue(obj, jsonb_state);
+ }
+ else if (type == PyObject_Type(PyList_New(0)))
+ {
+ /* LIST or STRING */
+ /* but we have checked on STRING */
+ out = PySequence_ToJsonbValue(obj, jsonb_state);
+ }
+ else if (PyBool_Check(obj))
+ {
+ /* Boolean */
+ JsonbValue *jbvElem;
+
+ jbvElem = palloc(sizeof(JsonbValue));
+ jbvElem->type = jbvBool;
+ jbvElem->val.boolean = obj == Py_True;
+ out = jbvElem;
+
+ }
+ else if (obj == Py_None)
+ {
+ /* None */
+ JsonbValue *jbvElem;
+
+ jbvElem = palloc(sizeof(JsonbValue));
+ jbvElem->type = jbvNull;
+ out = jbvElem;
+
+ }
+ else if (PyNumber_Check(obj))
+ {
+ /* NUMERIC */
+ out = PyNumeric_ToJsonbValue(obj);
+ }
+ else
+ {
+ /* EVERYTHING ELSE */
+ /* Handle it as it's repr */
+ out = PyString_ToJsonbValue(obj);
+ }
+ return (out);
+}
+
+/*
+ * plpython_to_jsonb(PyObject *obj)
+ * Function to transform python objects to jsonb object.
+ * The first argument is the Python object to be transformed.
+ * Return value is the pointer to Jsonb structure containing the transformed object.
+ * */
+PG_FUNCTION_INFO_V1(plpython_to_jsonb);
+Datum
+plpython_to_jsonb(PG_FUNCTION_ARGS)
+{
+ PyObject *obj;
+ JsonbValue *out;
+ JsonbParseState *jsonb_state = NULL;
+
+ obj = (PyObject *) PG_GETARG_POINTER(0);
+ out = PyObject_ToJsonbValue(obj, jsonb_state);
+ PG_RETURN_POINTER(JsonbValueToJsonb(out));
+}
diff --git a/contrib/jsonb_plpython/jsonb_plpython2u--1.0.sql b/contrib/jsonb_plpython/jsonb_plpython2u--1.0.sql
new file mode 100644
index 0000000..1e38847
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython2u--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION jsonb_plpython" to load this file. \quit
+
+CREATE FUNCTION jsonb_to_plpython(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plpython_to_jsonb(val internal) RETURNS jsonb
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR jsonb LANGUAGE plpython2u (
+ FROM SQL WITH FUNCTION jsonb_to_plpython(internal),
+ TO SQL WITH FUNCTION plpython_to_jsonb(internal)
+);
+
+COMMENT ON TRANSFORM FOR jsonb LANGUAGE plpython2u IS 'transform between jsonb and Python dict';
diff --git a/contrib/jsonb_plpython/jsonb_plpython2u.control b/contrib/jsonb_plpython/jsonb_plpython2u.control
new file mode 100644
index 0000000..e7d7da3
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython2u.control
@@ -0,0 +1,6 @@
+# jsonb_plpython2u extension
+comment = 'transform between jsonb and plpythonu'
+default_version = '1.0'
+module_pathname = '$libdir/jsonb_plpython2u'
+relocatable = false
+requires = 'plpython2u'
diff --git a/contrib/jsonb_plpython/jsonb_plpython3u--1.0.sql b/contrib/jsonb_plpython/jsonb_plpython3u--1.0.sql
new file mode 100644
index 0000000..0958db7
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython3u--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION jsonb_plpython3" to load this file. \quit
+
+CREATE FUNCTION jsonb_to_plpython(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plpython_to_jsonb(val internal) RETURNS jsonb
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR jsonb LANGUAGE plpython3u (
+ FROM SQL WITH FUNCTION jsonb_to_plpython(internal),
+ TO SQL WITH FUNCTION plpython_to_jsonb(internal)
+);
+
+COMMENT ON TRANSFORM FOR jsonb LANGUAGE plpython3u IS 'transform between jsonb and Python dict';
diff --git a/contrib/jsonb_plpython/jsonb_plpython3u.control b/contrib/jsonb_plpython/jsonb_plpython3u.control
new file mode 100644
index 0000000..5e08544
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython3u.control
@@ -0,0 +1,6 @@
+# jsonb_plpython3u extension
+comment = 'transform between jsonb and plpython3u'
+default_version = '1.0'
+module_pathname = '$libdir/jsonb_plpython3u'
+relocatable = false
+requires = 'plpython3u'
diff --git a/contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql b/contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql
new file mode 100644
index 0000000..7c9460d
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql
@@ -0,0 +1,19 @@
+/* contrib/jsonb_plpython/jsonb_plpythonu--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION jsonb_plpython" to load this file. \quit
+
+CREATE FUNCTION jsonb_to_plpython(val internal) RETURNS internal
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE FUNCTION plpython_to_jsonb(val internal) RETURNS jsonb
+LANGUAGE C STRICT IMMUTABLE
+AS 'MODULE_PATHNAME';
+
+CREATE TRANSFORM FOR jsonb LANGUAGE plpythonu (
+ FROM SQL WITH FUNCTION jsonb_to_plpython(internal),
+ TO SQL WITH FUNCTION plpython_to_jsonb(internal)
+);
+
+COMMENT ON TRANSFORM FOR jsonb LANGUAGE plpythonu IS 'transform between jsonb and Python dict';
diff --git a/contrib/jsonb_plpython/jsonb_plpythonu.control b/contrib/jsonb_plpython/jsonb_plpythonu.control
new file mode 100644
index 0000000..9bbeb38
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpythonu.control
@@ -0,0 +1,6 @@
+# jsonb_plpython2u extension
+comment = 'transform between jsonb and plpythonu'
+default_version = '1.0'
+module_pathname = '$libdir/jsonb_plpython2u'
+relocatable = false
+requires = 'plpythonu'
diff --git a/contrib/jsonb_plpython/sql/jsonb_plpython2.sql b/contrib/jsonb_plpython/sql/jsonb_plpython2.sql
new file mode 100644
index 0000000..7a5445f
--- /dev/null
+++ b/contrib/jsonb_plpython/sql/jsonb_plpython2.sql
@@ -0,0 +1,261 @@
+CREATE EXTENSION jsonb_plpython2u CASCADE;
+
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+
+SELECT test2arr('[12,1]'::jsonb);
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+
+SELECT test1int('1'::jsonb);
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+
+SELECT test1string('"a"'::jsonb);
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+
+SELECT test1null('"a"'::jsonb);
+
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpython2u
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+
+SELECT back('null'::jsonb);
+SELECT back('1'::jsonb);
+SELECT back('true'::jsonb);
+SELECT back('"string"'::jsonb);
+
+SELECT back('{"1":null}'::jsonb);
+SELECT back('{"1":1}'::jsonb);
+SELECT back('{"1":true}'::jsonb);
+SELECT back('{"1":"string"}'::jsonb);
+
+SELECT back('[null]'::jsonb);
+SELECT back('[1]'::jsonb);
+SELECT back('[true]'::jsonb);
+SELECT back('["string"]'::jsonb);
+SELECT back('[null,1]'::jsonb);
+SELECT back('[1,true]'::jsonb);
+SELECT back('[true,"string"]'::jsonb);
+SELECT back('["string","string2"]'::jsonb);
+
+
+DROP EXTENSION plpython2u CASCADE;
+
+
+-- Testing plpythonu extension.
+CREATE EXTENSION jsonb_plpythonu CASCADE;
+
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+
+SELECT test2arr('[12,1]'::jsonb);
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+
+SELECT test1int('1'::jsonb);
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+
+SELECT test1string('"a"'::jsonb);
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+
+SELECT test1null('"a"'::jsonb);
+
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpythonu
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+
+SELECT back('null'::jsonb);
+SELECT back('1'::jsonb);
+SELECT back('true'::jsonb);
+SELECT back('"string"'::jsonb);
+
+SELECT back('{"1":null}'::jsonb);
+SELECT back('{"1":1}'::jsonb);
+SELECT back('{"1":true}'::jsonb);
+SELECT back('{"1":"string"}'::jsonb);
+
+SELECT back('[null]'::jsonb);
+SELECT back('[1]'::jsonb);
+SELECT back('[true]'::jsonb);
+SELECT back('["string"]'::jsonb);
+SELECT back('[null,1]'::jsonb);
+SELECT back('[1,true]'::jsonb);
+SELECT back('[true,"string"]'::jsonb);
+SELECT back('["string","string2"]'::jsonb);
+
+
+DROP EXTENSION plpythonu CASCADE;
diff --git a/contrib/jsonb_plpython/sql/jsonb_plpython3.sql b/contrib/jsonb_plpython/sql/jsonb_plpython3.sql
new file mode 100644
index 0000000..eaddf9a
--- /dev/null
+++ b/contrib/jsonb_plpython/sql/jsonb_plpython3.sql
@@ -0,0 +1,129 @@
+CREATE EXTENSION jsonb_plpython3u CASCADE;
+
+-- test jsonb -> python dict
+CREATE FUNCTION test1(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+plpy.info(sorted(val.items()))
+return len(val)
+$$;
+
+SELECT test1('{"a":1, "c":"NULL"}'::jsonb);
+
+-- test jsonb -> python dict
+-- complex dict with dicts as value
+CREATE FUNCTION test1complex(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d":{"d": 1}})
+return len(val)
+$$;
+
+SELECT test1complex('{"d":{"d": 1}}'::jsonb);
+
+
+-- test jsonb[] -> python dict
+-- dict with array as value
+CREATE FUNCTION test1arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, dict)
+assert(val == {"d": [12,1]})
+return len(val)
+$$;
+
+SELECT test1arr('{"d":[12,1]}'::jsonb);
+
+-- test jsonb[] -> python list
+-- simple list
+CREATE FUNCTION test2arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [12,1])
+return len(val)
+$$;
+
+SELECT test2arr('[12,1]'::jsonb);
+
+-- test jsonb[] -> python list
+-- array of dicts
+CREATE FUNCTION test3arr(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert isinstance(val, list)
+assert(val == [{"a":1,"b":2},{"c":3,"d":4}])
+return len(val)
+$$;
+
+SELECT test3arr('[{"a":1,"b":2},{"c":3,"d":4}]'::jsonb);
+
+-- test jsonb int -> python int
+CREATE FUNCTION test1int(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == [1])
+return len(val)
+$$;
+
+SELECT test1int('1'::jsonb);
+
+-- test jsonb string -> python string
+CREATE FUNCTION test1string(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == ["a"])
+return len(val)
+$$;
+
+SELECT test1string('"a"'::jsonb);
+
+-- test jsonb null -> python None
+CREATE FUNCTION test1null(val jsonb) RETURNS int
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+AS $$
+assert(val == None)
+return val==None
+$$;
+
+SELECT test1null('"a"'::jsonb);
+
+-- test python -> jsonb
+CREATE FUNCTION back(val jsonb) RETURNS jsonb
+LANGUAGE plpython3u
+TRANSFORM FOR TYPE jsonb
+as $$
+return val
+$$;
+
+SELECT back('null'::jsonb);
+SELECT back('1'::jsonb);
+SELECT back('true'::jsonb);
+SELECT back('"string"'::jsonb);
+
+SELECT back('{"1":null}'::jsonb);
+SELECT back('{"1":1}'::jsonb);
+SELECT back('{"1":true}'::jsonb);
+SELECT back('{"1":"string"}'::jsonb);
+
+SELECT back('[null]'::jsonb);
+SELECT back('[1]'::jsonb);
+SELECT back('[true]'::jsonb);
+SELECT back('["string"]'::jsonb);
+SELECT back('[null,1]'::jsonb);
+SELECT back('[1,true]'::jsonb);
+SELECT back('[true,"string"]'::jsonb);
+SELECT back('["string","string2"]'::jsonb);
+
+
+DROP EXTENSION plpython3u CASCADE;
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 05ecef2..4e8a7a0 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -569,4 +569,20 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
compared using the default database collation.
</para>
</sect2>
+ <sect2>
+ <title>Transforms</title>
+
+ <para>
+ Additional extensions are available that implement transforms for
+ the <type>jsonb</type> type for the language PL/Python. The
+ extensions for PL/Python are called
+ <literal>jsonb_plpythonu</literal>, <literal>jsonb_plpython2u</literal>,
+ and <literal>jsonb_plpython3u</literal>
+ (see <xref linkend="plpython-python23"> for the PL/Python naming
+ convention). If you use them, <type>jsonb</type> values are mapped to
+ Python dictionaries.
+ </para>
+ </sect2>
+
+
</sect1>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
