Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eEBXq-000509-JZ for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Nov 2017 10:00:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eEBXp-0006VB-Qh for pgsql-hackers@arkaria.postgresql.org; Mon, 13 Nov 2017 10:00:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eEBXm-0006T5-Dm for pgsql-hackers@postgresql.org; Mon, 13 Nov 2017 10:00:42 +0000 Received: from mail.postgrespro.ru ([93.174.131.138]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1eEBXc-0005qa-Dy for pgsql-hackers@postgresql.org; Mon, 13 Nov 2017 10:00:41 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 9FA1C21C2583 for ; Mon, 13 Nov 2017 13:00:30 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Received: from anthony-24-g082ur (unknown [217.23.185.36]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id 435B621C20B0 for ; Mon, 13 Nov 2017 13:00:30 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1510567230; bh=U7XDdBZAoM18xaXh7Zb/b3BPMma1TcpLmfqud3I3N5w=; h=Date:From:To:Subject:In-Reply-To:References; b=HaI7EYF0Yn2BozJFZJ2SpW3Y+ApSHKZjRru1ofyMEfCDzuNbmyaNCisj071e5GBv3 KARaQqFJ8SFDhF/Pht+Kp5HlPhkMqbLu0FeCiioJdSnh+m3j7ixhehhjgHGwRRpU2v Nq/9Ur/DHfPtiniMJZC+XBLPNsamQ4UQIWb6LEg4= Date: Mon, 13 Nov 2017 13:00:28 +0300 From: Anthony Bykov To: pgsql-hackers@postgresql.org Subject: Re: Jsonb transform for pl/python Message-ID: <20171113130028.32405fee@anthony-24-g082ur> In-Reply-To: <20171109122646.1353.77959.pgcf@coridan.postgresql.org> References: <20171025145100.3a19933f@anthony-24-g082ur> <20171025145100.3a19933f@anthony-24-g082ur> <20171109122646.1353.77959.pgcf@coridan.postgresql.org> Organization: postgres pro X-Mailer: Claws Mail 3.13.2 (GTK+ 2.24.30; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="MP_/DR/s8unaZwot.uv+Sj43tip" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --MP_/DR/s8unaZwot.uv+Sj43tip Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline On Thu, 09 Nov 2017 12:26:46 +0000 Aleksander Alekseev 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 jsonb 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 --MP_/DR/s8unaZwot.uv+Sj43tip Content-Type: text/x-patch Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=0001-jsonb_plpython-extension-v2.patch 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 + 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 + 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 + 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. + + Transforms + + + Additional extensions are available that implement transforms for + the jsonb type for the language PL/Python. The + extensions for PL/Python are called + jsonb_plpythonu, jsonb_plpython2u, + and jsonb_plpython3u + (see for the PL/Python naming + convention). If you use them, jsonb values are mapped to + Python dictionaries. + + + + --MP_/DR/s8unaZwot.uv+Sj43tip Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --MP_/DR/s8unaZwot.uv+Sj43tip--