Hi.
I've implemented jsonb transform
(https://www.postgresql.org/docs/9.5/static/sql-createtransform.html)
for pl/python.
1. '{"1":1}'::jsonb is transformed into dict {"1"=>1}, while
'["1",2]'::jsonb is transformed into list(not tuple!) ["1", 2]
2. If there is a numeric value appear in jsonb, it will be transformed
to decimal through string (Numeric->String->Decimal). Not the best
solution, but as far as I understand this is usual practise in
postgresql to serialize Numerics and de-serialize them.
3. Decimal is transformed into jsonb through string
(Decimal->String->Numeric).
An example may also be helpful to understand extension. So, as an
example, function "test" transforms incoming jsonb into python,
transforms it back into jsonb and returns it.
create extension jsonb_plpython2u cascade;
create or replace function test(val jsonb)
returns jsonb
transform for type jsonb
language plpython2u
as $$
return (val);
$$;
select test('{"1":1,"example": null}'::jsonb);
--
Anthony Bykov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Companydiff --git a/contrib/Makefile b/contrib/Makefile
index e84eb67..d6b7170 100644
--- a/contrib/Makefile
+++ b/contrib/Makefile
@@ -82,9 +82,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..1e34d86
--- /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_plpython$(python_majorversion)u
+DATA = jsonb_plpython$(python_majorversion)u--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_plpython.out b/contrib/jsonb_plpython/expected/jsonb_plpython.out
new file mode 100644
index 0000000..be104af
--- /dev/null
+++ b/contrib/jsonb_plpython/expected/jsonb_plpython.out
@@ -0,0 +1,118 @@
+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);
+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)
+
+DROP EXTENSION jsonb_plpythonu CASCADE;
+NOTICE: drop cascades to 7 other objects
+DETAIL: 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)
diff --git a/contrib/jsonb_plpython/expected/jsonb_plpython2.out b/contrib/jsonb_plpython/expected/jsonb_plpython2.out
new file mode 100644
index 0000000..80afb2d
--- /dev/null
+++ b/contrib/jsonb_plpython/expected/jsonb_plpython2.out
@@ -0,0 +1,119 @@
+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)
+
+DROP EXTENSION plpython2u CASCADE;
+NOTICE: drop cascades to 8 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)
diff --git a/contrib/jsonb_plpython/expected/jsonb_plpython3.out b/contrib/jsonb_plpython/expected/jsonb_plpython3.out
new file mode 100644
index 0000000..80202b0
--- /dev/null
+++ b/contrib/jsonb_plpython/expected/jsonb_plpython3.out
@@ -0,0 +1,119 @@
+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)
+
+DROP EXTENSION plpython3u CASCADE;
+NOTICE: drop cascades to 8 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)
diff --git a/contrib/jsonb_plpython/jsonb_plpython.c b/contrib/jsonb_plpython/jsonb_plpython.c
new file mode 100644
index 0000000..97960ac
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython.c
@@ -0,0 +1,417 @@
+/* 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 = Py_None;
+ JsonbIterator *it;
+ JsonbIteratorToken r;
+ JsonbValue v;
+
+ object = PyDict_New();
+ it = JsonbIteratorInit(jsonb);
+
+ /*
+ * Iterate trhrough Jsonb object.
+ */
+ while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
+ {
+ PyObject *key = Py_None;
+ PyObject *value = Py_None;
+
+ switch (r)
+ {
+ case (WJB_KEY):
+ /* dict key in v */
+ 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_BEGIN_ARRAY):
+ /* array in v */
+ object = PyList_New(0);
+ while (
+ ((r = JsonbIteratorNext(&it, &v, true)) == WJB_ELEM)
+ && (r != WJB_DONE)
+ )
+ PyList_Append(object, PyObject_FromJsonbValue(&v));
+ return (object);
+ break;
+ case (WJB_END_OBJECT):
+ case (WJB_BEGIN_OBJECT):
+ /* no object are in v */
+ break;
+ default:
+ /* simple objects */
+ object = PyObject_FromJsonbValue(&v);
+ break;
+ }
+ Py_XDECREF(value);
+ Py_XDECREF(key);
+ }
+ 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(DirectFunctionCall1(
+ numeric_in,
+ CStringGetDatum(PLyObject_AsString(obj))
+ ));
+ 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;
+
+ if (PyMapping_Check(obj))
+ {
+ /* DICT */
+ out = PyMapping_ToJsonbValue(obj, jsonb_state);
+ }
+ else if (PyString_Check(obj))
+ {
+ /* STRING */
+ out = PyString_ToJsonbValue(obj);
+ }
+ else if (PySequence_Check(obj))
+ {
+ /* LIST or STRING */
+ /* but we have checked on STRING */
+ out = PySequence_ToJsonbValue(obj, jsonb_state);
+ }
+ else if (PyNumber_Check(obj))
+ {
+ /* NUMERIC */
+ out = PyNumeric_ToJsonbValue(obj);
+ }
+ else
+ {
+ /* EVERYTHING ELSE */
+ /* Handle it as it's repr */
+ 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);
+}
+
+/*
+ * 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..3514cbc
--- /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 = true
+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..bbf6ed7
--- /dev/null
+++ b/contrib/jsonb_plpython/jsonb_plpython3u.control
@@ -0,0 +1,6 @@
+# jsonb_plpython3u extension
+comment = 'transform between jsonb and plpythonu'
+default_version = '1.0'
+module_pathname = '$libdir/jsonb_plpython3'
+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..d696dfb
--- /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_plpython'
+relocatable = true
+requires = 'plpython2u'
diff --git a/contrib/jsonb_plpython/sql/jsonb_plpython.sql b/contrib/jsonb_plpython/sql/jsonb_plpython.sql
new file mode 100644
index 0000000..dda714e
--- /dev/null
+++ b/contrib/jsonb_plpython/sql/jsonb_plpython.sql
@@ -0,0 +1,91 @@
+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);
+
+
+DROP EXTENSION plpython cascade;
diff --git a/contrib/jsonb_plpython/sql/jsonb_plpython2.sql b/contrib/jsonb_plpython/sql/jsonb_plpython2.sql
new file mode 100644
index 0000000..96478a2
--- /dev/null
+++ b/contrib/jsonb_plpython/sql/jsonb_plpython2.sql
@@ -0,0 +1,91 @@
+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);
+
+
+DROP EXTENSION plpython2u 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..80e22a1
--- /dev/null
+++ b/contrib/jsonb_plpython/sql/jsonb_plpython3.sql
@@ -0,0 +1,91 @@
+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);
+
+
+DROP EXTENSION plpython3u CASCADE;
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 3cf78d6..66d307c 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/Perl 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
