From: Anthony Bykov <a.bykov@postgrespro.ru>
To: pgsql-hackers@postgresql.org
Subject: Jsonb transform for pl/python
Date: Wed, 25 Oct 2017 14:51:00 +0300
Message-ID: <20171025145100.3a19933f@anthony-24-g082ur> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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 Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:0001-jsonb_plpython-extension.patch (text/x-patch, 33.3K) downloaddiff --git a/contrib/Makefile b/contrib/Makefileindex 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/Makefilenew file mode 100644index 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+endifdiff --git a/contrib/jsonb_plpython/expected/jsonb_plpython.out b/contrib/jsonb_plpython/expected/jsonb_plpython.outnew file mode 100644index 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.outnew file mode 100644index 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.outnew file mode 100644index 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.cnew file mode 100644index 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.sqlnew file mode 100644index 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.controlnew file mode 100644index 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.sqlnew file mode 100644index 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.controlnew file mode 100644index 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.sqlnew file mode 100644index 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.controlnew file mode 100644index 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.sqlnew file mode 100644index 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.sqlnew file mode 100644index 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.sqlnew file mode 100644index 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.sgmlindex 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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.bykov@postgrespro.ru
Subject: Re: Jsonb transform for pl/python
In-Reply-To: <20171025145100.3a19933f@anthony-24-g082ur>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox