From: Anthony Bykov <a.bykov@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Transform for pl/perl
Date: Thu, 7 Dec 2017 12:56:02 +0300
Message-ID: <20171207125602.5326f103@anthony-24-g082ur> (raw)
In-Reply-To: <20171207125455.39067aa7@anthony-24-g082ur>
References: <20171127132439.23dc26ef@anthony-24-g082ur>
<20171201184921.27he6vbp62yegpvm@alvherre.pgsql>
<20171207125455.39067aa7@anthony-24-g082ur>
On Thu, 7 Dec 2017 12:54:55 +0300
Anthony Bykov <a.bykov@postgrespro.ru> wrote:
> On Fri, 1 Dec 2017 15:49:21 -0300
> Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> > A few very minor comments while quickly paging through:
> >
> > 1. non-ASCII tests are going to cause problems in one platform or
> > another. Please don't include that one.
> >
> > 2. error messages
> > a) please use ereport() not elog()
> > b) conform to style guidelines: errmsg() start with lowercase,
> > others are complete phrases (start with uppercase, end with period)
> > c) replace
> > "The type you was trying to transform can't be represented in
> > JSONB" maybe with
> > errmsg("could not transform to type \"%s\"", "jsonb"),
> > errdetail("The type you are trying to transform can't be
> > represented in JSON") d) same errmsg() for the other error; figure
> > out suitable errdetail.
> >
> > 3. whitespace: don't add newlines to while, DirectFunctionCallN,
> > pnstrdup.
> >
> > 4. the "relocatability" test seems pointless to me.
> >
> > 5. "#undef _" looks bogus. Don't do it.
> >
>
> Hello,
> thank you for your time.
>
> 1. I really think that it might be a good practice to test non ASCII
> symbols on platforms where it is possible. Maybe locale-dependent
> Makefile? I've already spent pretty much time trying to find
> possible solutions and I have no results. So, I've deleted this
> tests. Maybe there is a better solution I don't know about?
>
> 2. Thank you for this one. Writing those errors were really pain for
> me. I've changed those things in new patch.
>
> 3. I've fixed all the whitespaces you was talking about in new version
> of the patch.
>
> 4. The relocatibility test is needed in order to check if patch is
> still relocatable. With this test I've tried to prove the code
> "relocatable=true" in *.control files. So, I've decided to leave
> them in next version of the patch.
>
> 5. If I delete #undef _, I will get the warning:
> /usr/lib/x86_64-linux-gnu/perl/5.22/CORE/config.h:3094:0:
> warning: "_" redefined #define _(args) args
>
> In file included from ../../src/include/postgres.h:47:0,
> from jsonb_plperl.c:12:
> ../../src/include/c.h:971:0: note: this is the location of the
> previous definition #define _(x) gettext(x)
> This #undef was meant to fix the warning. I hope a new comment next
> to #undef contains all the explanations needed.
>
> Please, find a new version of the patch in attachments to this
> message.
>
>
> --
> Anthony Bykov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
Forgot the patch.
--
Anthony Bykov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:0001-jsonb_plperl-extension-v5.patch (text/x-patch, 29.7K) downloaddiff --git a/contrib/Makefile b/contrib/Makefileindex 8046ca4..53d44fe 100644--- a/contrib/Makefile+++ b/contrib/Makefile@@ -75,9 +75,9 @@ ALWAYS_SUBDIRS += sepgsql
endif
ifeq ($(with_perl),yes)
-SUBDIRS += hstore_plperl+SUBDIRS += hstore_plperl jsonb_plperl
else
-ALWAYS_SUBDIRS += hstore_plperl+ALWAYS_SUBDIRS += hstore_plperl jsonb_plperl
endif
ifeq ($(with_python),yes)
diff --git a/contrib/jsonb_plperl/Makefile b/contrib/jsonb_plperl/Makefilenew file mode 100644index 0000000..cd86553--- /dev/null+++ b/contrib/jsonb_plperl/Makefile@@ -0,0 +1,40 @@+# contrib/jsonb_plperl/Makefile++MODULE_big = jsonb_plperl+OBJS = jsonb_plperl.o $(WIN32RES)+PGFILEDESC = "jsonb_plperl - jsonb transform for plperl"++PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl++EXTENSION = jsonb_plperlu jsonb_plperl+DATA = jsonb_plperlu--1.0.sql jsonb_plperl--1.0.sql++REGRESS = jsonb_plperl jsonb_plperl_relocatability jsonb_plperlu jsonb_plperlu_relocatability++ifdef USE_PGXS+PG_CONFIG = pg_config+PGXS := $(shell $(PG_CONFIG) --pgxs)+include $(PGXS)+else+subdir = contrib/jsonb_plperl+top_builddir = ../..+include $(top_builddir)/src/Makefile.global+include $(top_srcdir)/contrib/contrib-global.mk+endif++# We must link libperl explicitly+ifeq ($(PORTNAME), win32)+# these settings are the same as for plperl+override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment+# ... see silliness in plperl Makefile ...+SHLIB_LINK += $(sort $(wildcard ../../src/pl/plperl/libperl*.a))+else+rpathdir = $(perl_archlibexp)/CORE+SHLIB_LINK += $(perl_embed_ldflags)+endif++# As with plperl we need to make sure that the CORE directory is included+# last, probably because it sometimes contains some header files with names+# that clash with some of ours, or with some that we include, notably on+# Windows.+override CPPFLAGS := $(CPPFLAGS) $(perl_embed_ccflags) -I$(perl_archlibexp)/COREdiff --git a/contrib/jsonb_plperl/expected/jsonb_plperl.out b/contrib/jsonb_plperl/expected/jsonb_plperl.outnew file mode 100644index 0000000..9032f7e--- /dev/null+++ b/contrib/jsonb_plperl/expected/jsonb_plperl.out@@ -0,0 +1,235 @@+CREATE EXTENSION jsonb_plperl CASCADE;+NOTICE: installing required extension "plperl"+-- test hash -> jsonb+CREATE FUNCTION testHVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = {a => 1, b => 'boo', c => undef};+return $val;+$$;+SELECT testHVToJsonb();+ testhvtojsonb +---------------------------------+ {"a": 1, "b": "boo", "c": null}+(1 row)++-- test array -> jsonb+CREATE FUNCTION testAVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];+return $val;+$$;+SELECT testAVToJsonb();+ testavtojsonb +---------------------------------------------+ [{"a": 1, "b": "boo", "c": null}, {"d": 2}]+(1 row)++-- test scalar -> jsonb+CREATE FUNCTION testSVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = 1;+return $val;+$$;+SELECT testSVToJsonb();+ testsvtojsonb +---------------+ 1+(1 row)++-- test blessed scalar -> jsonb+CREATE FUNCTION testBlessedToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+my $class = shift;+my $tmp = { a=>"a", 1=>"1" };+bless $tmp, $class;+return $tmp;+$$;+SELECT testBlessedToJsonb();+ testblessedtojsonb +----------------------+ {"1": "1", "a": "a"}+(1 row)++-- test blessed scalar -> jsonb+CREATE FUNCTION testRegexpToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+return ('1' =~ m(0\t2));+$$;+SELECT testRegexpToJsonb();+ERROR: could not transform to type "jsonb"+DETAIL: The type you are trying to transform can't be transformed to jsonb+CONTEXT: PL/Perl function "testregexptojsonb"+-- test jsonb -> scalar -> jsonb+CREATE FUNCTION testSVToJsonb2(val jsonb) RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+return $_[0];+$$;+SELECT testSVToJsonb2('null');+ testsvtojsonb2 +----------------+ null+(1 row)++SELECT testSVToJsonb2('1');+ testsvtojsonb2 +----------------+ 1+(1 row)++SELECT testSVToJsonb2('1E+131071');+ERROR: could not transform to type "jsonb"+DETAIL: The type you are trying to transform can't be transformed to jsonb+CONTEXT: PL/Perl function "testsvtojsonb2"+SELECT testSVToJsonb2('-1');+ testsvtojsonb2 +----------------+ -1+(1 row)++SELECT testSVToJsonb2('1.2');+ testsvtojsonb2 +----------------+ 1.2+(1 row)++SELECT testSVToJsonb2('-1.2');+ testsvtojsonb2 +----------------+ -1.2+(1 row)++SELECT testSVToJsonb2('"string"');+ testsvtojsonb2 +----------------+ "string"+(1 row)++SELECT testSVToJsonb2('"NaN"');+ testsvtojsonb2 +----------------+ "NaN"+(1 row)++SELECT testSVToJsonb2('true');+ testsvtojsonb2 +----------------+ 1+(1 row)++SELECT testSVToJsonb2('false');+ testsvtojsonb2 +----------------+ 0+(1 row)++SELECT testSVToJsonb2('[]');+ testsvtojsonb2 +----------------+ []+(1 row)++SELECT testSVToJsonb2('[null,null]');+ testsvtojsonb2 +----------------+ [null, null]+(1 row)++SELECT testSVToJsonb2('[1,2,3]');+ testsvtojsonb2 +----------------+ [1, 2, 3]+(1 row)++SELECT testSVToJsonb2('[-1,2,-3]');+ testsvtojsonb2 +----------------+ [-1, 2, -3]+(1 row)++SELECT testSVToJsonb2('[1.2,2.3,3.4]');+ testsvtojsonb2 +-----------------+ [1.2, 2.3, 3.4]+(1 row)++SELECT testSVToJsonb2('[-1.2,2.3,-3.4]');+ testsvtojsonb2 +-------------------+ [-1.2, 2.3, -3.4]+(1 row)++SELECT testSVToJsonb2('["string1","string2"]');+ testsvtojsonb2 +------------------------+ ["string1", "string2"]+(1 row)++SELECT testSVToJsonb2('{}');+ testsvtojsonb2 +----------------+ {}+(1 row)++SELECT testSVToJsonb2('{"1":null}');+ testsvtojsonb2 +----------------+ {"1": null}+(1 row)++SELECT testSVToJsonb2('{"1":1}');+ testsvtojsonb2 +----------------+ {"1": 1}+(1 row)++SELECT testSVToJsonb2('{"1":-1}');+ testsvtojsonb2 +----------------+ {"1": -1}+(1 row)++SELECT testSVToJsonb2('{"1":1.1}');+ testsvtojsonb2 +----------------+ {"1": 1.1}+(1 row)++SELECT testSVToJsonb2('{"1":-1.1}');+ testsvtojsonb2 +----------------+ {"1": -1.1}+(1 row)++SELECT testSVToJsonb2('{"1":"string1"}');+ testsvtojsonb2 +------------------+ {"1": "string1"}+(1 row)++SELECT testSVToJsonb2('{"1":{"2":[3,4,5]},"2":3}');+ testsvtojsonb2 +---------------------------------+ {"1": {"2": [3, 4, 5]}, "2": 3}+(1 row)++DROP EXTENSION plperl CASCADE;+NOTICE: drop cascades to 7 other objects+DETAIL: drop cascades to extension jsonb_plperl+drop cascades to function testhvtojsonb()+drop cascades to function testavtojsonb()+drop cascades to function testsvtojsonb()+drop cascades to function testblessedtojsonb()+drop cascades to function testregexptojsonb()+drop cascades to function testsvtojsonb2(jsonb)diff --git a/contrib/jsonb_plperl/expected/jsonb_plperl_relocatability.out b/contrib/jsonb_plperl/expected/jsonb_plperl_relocatability.outnew file mode 100644index 0000000..b334d0c--- /dev/null+++ b/contrib/jsonb_plperl/expected/jsonb_plperl_relocatability.out@@ -0,0 +1,21 @@+CREATE EXTENSION jsonb_plperl CASCADE;+NOTICE: installing required extension "plperl"+CREATE SCHEMA test;+alter extension jsonb_plperl set schema test;+create function test.test(val jsonb) returns jsonb+language plperl+transform for type jsonb+as $$+return val+$$;+select test.test('1'::jsonb);+ test +-------+ "val"+(1 row)++drop extension plperl cascade;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to extension jsonb_plperl+drop cascades to function test.test(jsonb)+drop schema test cascade;diff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu.out b/contrib/jsonb_plperl/expected/jsonb_plperlu.outnew file mode 100644index 0000000..e0309ba--- /dev/null+++ b/contrib/jsonb_plperl/expected/jsonb_plperlu.out@@ -0,0 +1,235 @@+CREATE EXTENSION jsonb_plperlu CASCADE;+NOTICE: installing required extension "plperlu"+-- test hash -> jsonb+CREATE FUNCTION testHVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = {a => 1, b => 'boo', c => undef};+return $val;+$$;+SELECT testHVToJsonb();+ testhvtojsonb +---------------------------------+ {"a": 1, "b": "boo", "c": null}+(1 row)++-- test array -> jsonb+CREATE FUNCTION testAVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];+return $val;+$$;+SELECT testAVToJsonb();+ testavtojsonb +---------------------------------------------+ [{"a": 1, "b": "boo", "c": null}, {"d": 2}]+(1 row)++-- test scalar -> jsonb+CREATE FUNCTION testSVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = 1;+return $val;+$$;+SELECT testSVToJsonb();+ testsvtojsonb +---------------+ 1+(1 row)++-- test blessed scalar -> jsonb+CREATE FUNCTION testBlessedToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+my $class = shift;+my $tmp = { a=>"a", 1=>"1" };+bless $tmp, $class;+return $tmp;+$$;+SELECT testBlessedToJsonb();+ testblessedtojsonb +----------------------+ {"1": "1", "a": "a"}+(1 row)++-- test blessed scalar -> jsonb+CREATE FUNCTION testRegexpToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+return ('1' =~ m(0\t2));+$$;+SELECT testRegexpToJsonb();+ERROR: could not transform to type "jsonb"+DETAIL: The type you are trying to transform can't be transformed to jsonb+CONTEXT: PL/Perl function "testregexptojsonb"+-- test jsonb -> scalar -> jsonb+CREATE FUNCTION testSVToJsonb2(val jsonb) RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+return $_[0];+$$;+SELECT testSVToJsonb2('null');+ testsvtojsonb2 +----------------+ null+(1 row)++SELECT testSVToJsonb2('1');+ testsvtojsonb2 +----------------+ 1+(1 row)++SELECT testSVToJsonb2('1E+131071');+ERROR: could not transform to type "jsonb"+DETAIL: The type you are trying to transform can't be transformed to jsonb+CONTEXT: PL/Perl function "testsvtojsonb2"+SELECT testSVToJsonb2('-1');+ testsvtojsonb2 +----------------+ -1+(1 row)++SELECT testSVToJsonb2('1.2');+ testsvtojsonb2 +----------------+ 1.2+(1 row)++SELECT testSVToJsonb2('-1.2');+ testsvtojsonb2 +----------------+ -1.2+(1 row)++SELECT testSVToJsonb2('"string"');+ testsvtojsonb2 +----------------+ "string"+(1 row)++SELECT testSVToJsonb2('"NaN"');+ testsvtojsonb2 +----------------+ "NaN"+(1 row)++SELECT testSVToJsonb2('true');+ testsvtojsonb2 +----------------+ 1+(1 row)++SELECT testSVToJsonb2('false');+ testsvtojsonb2 +----------------+ 0+(1 row)++SELECT testSVToJsonb2('[]');+ testsvtojsonb2 +----------------+ []+(1 row)++SELECT testSVToJsonb2('[null,null]');+ testsvtojsonb2 +----------------+ [null, null]+(1 row)++SELECT testSVToJsonb2('[1,2,3]');+ testsvtojsonb2 +----------------+ [1, 2, 3]+(1 row)++SELECT testSVToJsonb2('[-1,2,-3]');+ testsvtojsonb2 +----------------+ [-1, 2, -3]+(1 row)++SELECT testSVToJsonb2('[1.2,2.3,3.4]');+ testsvtojsonb2 +-----------------+ [1.2, 2.3, 3.4]+(1 row)++SELECT testSVToJsonb2('[-1.2,2.3,-3.4]');+ testsvtojsonb2 +-------------------+ [-1.2, 2.3, -3.4]+(1 row)++SELECT testSVToJsonb2('["string1","string2"]');+ testsvtojsonb2 +------------------------+ ["string1", "string2"]+(1 row)++SELECT testSVToJsonb2('{}');+ testsvtojsonb2 +----------------+ {}+(1 row)++SELECT testSVToJsonb2('{"1":null}');+ testsvtojsonb2 +----------------+ {"1": null}+(1 row)++SELECT testSVToJsonb2('{"1":1}');+ testsvtojsonb2 +----------------+ {"1": 1}+(1 row)++SELECT testSVToJsonb2('{"1":-1}');+ testsvtojsonb2 +----------------+ {"1": -1}+(1 row)++SELECT testSVToJsonb2('{"1":1.1}');+ testsvtojsonb2 +----------------+ {"1": 1.1}+(1 row)++SELECT testSVToJsonb2('{"1":-1.1}');+ testsvtojsonb2 +----------------+ {"1": -1.1}+(1 row)++SELECT testSVToJsonb2('{"1":"string1"}');+ testsvtojsonb2 +------------------+ {"1": "string1"}+(1 row)++SELECT testSVToJsonb2('{"1":{"2":[3,4,5]},"2":3}');+ testsvtojsonb2 +---------------------------------+ {"1": {"2": [3, 4, 5]}, "2": 3}+(1 row)++DROP EXTENSION plperlu CASCADE;+NOTICE: drop cascades to 7 other objects+DETAIL: drop cascades to extension jsonb_plperlu+drop cascades to function testhvtojsonb()+drop cascades to function testavtojsonb()+drop cascades to function testsvtojsonb()+drop cascades to function testblessedtojsonb()+drop cascades to function testregexptojsonb()+drop cascades to function testsvtojsonb2(jsonb)diff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu_relocatability.out b/contrib/jsonb_plperl/expected/jsonb_plperlu_relocatability.outnew file mode 100644index 0000000..a640da7--- /dev/null+++ b/contrib/jsonb_plperl/expected/jsonb_plperlu_relocatability.out@@ -0,0 +1,21 @@+CREATE EXTENSION jsonb_plperlu CASCADE;+NOTICE: installing required extension "plperlu"+CREATE SCHEMA test;+alter extension jsonb_plperlu set schema test;+create function test.test(val jsonb) returns jsonb+language plperlu+transform for type jsonb+as $$+return val+$$;+select test.test('1'::jsonb);+ test +-------+ "val"+(1 row)++drop extension plperlu cascade;+NOTICE: drop cascades to 2 other objects+DETAIL: drop cascades to extension jsonb_plperlu+drop cascades to function test.test(jsonb)+drop schema test cascade;diff --git a/contrib/jsonb_plperl/jsonb_plperl--1.0.sql b/contrib/jsonb_plperl/jsonb_plperl--1.0.sqlnew file mode 100644index 0000000..25dedbe--- /dev/null+++ b/contrib/jsonb_plperl/jsonb_plperl--1.0.sql@@ -0,0 +1,17 @@+/* contrib/jsonb_plperl/jsonb_plperl--1.0.sql */++-- complain if script is sourced in psql, rather than via CREATE EXTENSION+\echo Use "CREATE EXTENSION jsonb_plperl" to load this file. \quit++CREATE FUNCTION jsonb_to_plperl(val internal) RETURNS internal+LANGUAGE C STRICT IMMUTABLE+AS 'MODULE_PATHNAME';++CREATE FUNCTION plperl_to_jsonb(val internal) RETURNS jsonb+LANGUAGE C STRICT IMMUTABLE+AS 'MODULE_PATHNAME';++CREATE TRANSFORM FOR jsonb LANGUAGE plperl (+ FROM SQL WITH FUNCTION jsonb_to_plperl(internal),+ TO SQL WITH FUNCTION plperl_to_jsonb(internal)+);diff --git a/contrib/jsonb_plperl/jsonb_plperl.c b/contrib/jsonb_plperl/jsonb_plperl.cnew file mode 100644index 0000000..247d45a--- /dev/null+++ b/contrib/jsonb_plperl/jsonb_plperl.c@@ -0,0 +1,322 @@+/* This document contains an implementation of transformations from perl+ * object to jsonb and vise versa.+ * In this file you can find implementation of transformations:+ * - jsonb_to_plperl(PG_FUNCTION_ARGS)+ * - plperl_to_jsonb(PG_FUNCTION_ARGS)+ */+#include "postgres.h"++/* #undef _ is needed because "_" was already defined in include/c.h:971:0 */+#undef _++#include "fmgr.h"+#include "plperl.h"+#include "plperl_helpers.h"++#include "utils/jsonb.h"+#include "utils/fmgrprotos.h"++PG_MODULE_MAGIC;++static SV *SV_FromJsonb(JsonbContainer *jsonb);++static JsonbValue *HV_ToJsonbValue(HV *obj, JsonbParseState *jsonb_state);++static JsonbValue *SV_ToJsonbValue(SV *obj, JsonbParseState *jsonb_state);++/*+ * SV_FromJsonbValue+ *+ * Transform JsonbValue into SV+ */+static SV *+SV_FromJsonbValue(JsonbValue *jsonbValue)+{+ dTHX;+ SV *result;+ char *str;++ switch (jsonbValue->type)+ {+ case jbvBinary:+ result = (SV *) newRV((SV *) SV_FromJsonb(jsonbValue->val.binary.data));+ break;+ case jbvNumeric:++ /*+ * Transform incoming value into string and generate SV from+ * string+ */+ str = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(jsonbValue->val.numeric)));+ result = newSVnv(SvNV(cstr2sv(pstrdup(str))));+ break;+ case jbvString:+ result = cstr2sv(pnstrdup(jsonbValue->val.string.val, jsonbValue->val.string.len));+ break;+ case jbvBool:+ result = newSVnv(SvNV(jsonbValue->val.boolean ? &PL_sv_yes : &PL_sv_no));+ break;+ case jbvArray:+ result = SV_FromJsonbValue(jsonbValue->val.array.elems);+ break;+ case jbvObject:+ result = SV_FromJsonbValue(&(jsonbValue->val.object.pairs->value));+ break;+ case jbvNull:+ result = newSV(0);+ break;+ }+ return result;+}++/*+ * SV_FromJsonb+ *+ * Transform JsonbContainer into SV+ */+static SV *+SV_FromJsonb(JsonbContainer *jsonb)+{+ dTHX;+ SV *result;+ SV *value;+ JsonbIterator *it;+ JsonbValue v;++ it = JsonbIteratorInit(jsonb);++ switch (JsonbIteratorNext(&it, &v, true))+ {+ case WJB_BEGIN_ARRAY:+ {+ AV *av;+ bool raw_scalar;++ /* array in v */+ av = newAV();+ raw_scalar = (v.val.array.rawScalar);+ value = newSV(0);+ while (JsonbIteratorNext(&it, &v, true) == WJB_ELEM)+ {+ value = SV_FromJsonbValue(&v);+ av_push(av, value);+ }+ if (raw_scalar)+ result = newRV(value);+ else+ result = (SV *) av;+ break;+ }+ case WJB_BEGIN_OBJECT:+ {+ HV *object;+ const char *key;+ int keyLength;++ /* hash in v */+ object = newHV();+ while (JsonbIteratorNext(&it, &v, true) == WJB_KEY)+ {+ /* json key in v */+ key = pstrdup(v.val.string.val);+ keyLength = v.val.string.len;+ JsonbIteratorNext(&it, &v, true);+ value = SV_FromJsonbValue(&v);+ (void) hv_store(object, key, keyLength, value, 0);+ }+ result = (SV *) object;+ break;+ }+ case WJB_ELEM:+ case WJB_VALUE:+ case WJB_KEY:+ /* simple objects */+ result = SV_FromJsonbValue(&v);+ break;+ case WJB_DONE:+ case WJB_END_OBJECT:+ case WJB_END_ARRAY:+ pg_unreachable();+ break;+ }+ return result;+}++/*+ * jsonb_to_plperl+ *+ * Transform Jsonb into SV+ */+PG_FUNCTION_INFO_V1(jsonb_to_plperl);+Datum+jsonb_to_plperl(PG_FUNCTION_ARGS)+{+ dTHX;+ Jsonb *in = PG_GETARG_JSONB_P(0);+ SV *sv;++ sv = SV_FromJsonb(&in->root);++ return PointerGetDatum(newRV(sv));+}++/*+ * AV_ToJsonbValue+ *+ * Transform AV into JsonbValue+ * jsonb_state defines conversion state+ */+static JsonbValue *+AV_ToJsonbValue(AV *in, JsonbParseState *jsonb_state)+{+ dTHX;++ JsonbValue *jbvElem;+ JsonbValue *out = NULL;+ ssize_t pcount;+ ssize_t i;++ pcount = av_len(in) + 1;+ pushJsonbValue(&jsonb_state, WJB_BEGIN_ARRAY, NULL);++ for (i = 0; i < pcount; i++)+ {+ SV **value;++ value = av_fetch(in, i, false);+ jbvElem = SV_ToJsonbValue(*value, jsonb_state);++ /*+ * If "value" was a complex structure, it was already pushed to jsonb+ * and there is no need to push it again+ */+ if (IsAJsonbScalar(jbvElem))+ pushJsonbValue(&jsonb_state, WJB_ELEM, jbvElem);+ }+ out = pushJsonbValue(&jsonb_state, WJB_END_ARRAY, NULL);+ return out;+}++/*+ * SV_ToJsonbValue+ *+ * Transform Jsonb into SV+ */+static JsonbValue *+SV_ToJsonbValue(SV *in, JsonbParseState *jsonb_state)+{+ dTHX;+ svtype type; /* type of incoming object */+ JsonbValue *out; /* result */++ type = SvTYPE(in);+ switch (type)+ {+ case SVt_PVAV:+ out = AV_ToJsonbValue((AV *) in, jsonb_state);+ break;+ case SVt_PVHV:+ out = HV_ToJsonbValue((HV *) in, jsonb_state);+ break;+ case SVt_NV:+ case SVt_IV:+ {+ if (SvROK(in))+ /* if "in" is a pointer */+ out = SV_ToJsonbValue((SV *) SvRV(in), jsonb_state);+ else+ {+ /* if "in" is a numeric */+ char *str;++ out = palloc(sizeof(JsonbValue));+ str = sv2cstr(in);+ if (strcmp(str, "Inf") == 0)+ /* in case when "in" is "inf" */+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ (errmsg("could not transform to type \"%s\"", "jsonb"),+ errdetail("The type you are trying to transform can't be transformed to jsonb"))));+ else+ {+ Datum tmp;++ tmp = DirectFunctionCall3(numeric_in, CStringGetDatum(str), 0, -1);+ out->val.numeric = DatumGetNumeric(tmp);+ out->type = jbvNumeric;+ }+ }+ break;+ }+ case SVt_NULL:+ out = palloc(sizeof(JsonbValue));+ out->type = jbvNull;+ break;+ case SVt_PV:++ /*+ * String+ */+ out = palloc(sizeof(JsonbValue));+ out->val.string.val = sv2cstr(in);+ out->val.string.len = strlen(out->val.string.val);+ out->type = jbvString;+ break;+ default:+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ (errmsg("could not transform to type \"%s\"", "jsonb"),+ errdetail("The type you are trying to transform can't be transformed to jsonb"))));+ break;+ }+ return out;+}++/*+ * HV_ToJsonbValue+ *+ * Transform Jsonb into SV+ */+static JsonbValue *+HV_ToJsonbValue(HV *obj, JsonbParseState *jsonb_state)+{+ dTHX;+ JsonbValue *out;+ HE *he;++ pushJsonbValue(&jsonb_state, WJB_BEGIN_OBJECT, NULL);+ while ((he = hv_iternext(obj)) != NULL)+ {+ JsonbValue *key;+ JsonbValue *val;++ key = SV_ToJsonbValue(HeSVKEY_force(he), jsonb_state);+ pushJsonbValue(&jsonb_state, WJB_KEY, key);+ val = SV_ToJsonbValue(HeVAL(he), jsonb_state);+ if ((val == NULL) || (IsAJsonbScalar(val)))+ pushJsonbValue(&jsonb_state, WJB_VALUE, val);+ }+ out = pushJsonbValue(&jsonb_state, WJB_END_OBJECT, NULL);+ return out;+}++/*+ * plperl_to_jsonb(SV *in)+ *+ * Transform Jsonb into SV+ */+PG_FUNCTION_INFO_V1(plperl_to_jsonb);+Datum+plperl_to_jsonb(PG_FUNCTION_ARGS)+{+ dTHX;+ JsonbValue *out = NULL;+ Jsonb *result;+ JsonbParseState *jsonb_state = NULL;+ SV *in;++ in = (SV *) PG_GETARG_POINTER(0);+ out = SV_ToJsonbValue(in, jsonb_state);+ result = JsonbValueToJsonb(out);+ PG_RETURN_POINTER(result);+}diff --git a/contrib/jsonb_plperl/jsonb_plperl.control b/contrib/jsonb_plperl/jsonb_plperl.controlnew file mode 100644index 0000000..26c86a7--- /dev/null+++ b/contrib/jsonb_plperl/jsonb_plperl.control@@ -0,0 +1,6 @@+# jsonb_plperl extension+comment = 'transform between jsonb and plperl'+default_version = '1.0'+module_pathname = '$libdir/jsonb_plperl'+relocatable = true+requires = 'plperl'diff --git a/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql b/contrib/jsonb_plperl/jsonb_plperlu--1.0.sqlnew file mode 100644index 0000000..65404f6--- /dev/null+++ b/contrib/jsonb_plperl/jsonb_plperlu--1.0.sql@@ -0,0 +1,17 @@+/* contrib/json_plperl/jsonb_plperl--1.0.sql */++-- complain if script is sourced in psql, rather than via CREATE EXTENSION+\echo Use "CREATE EXTENSION jsonb_plperlu" to load this file. \quit++CREATE FUNCTION jsonb_to_plperl(val internal) RETURNS internal+LANGUAGE C STRICT IMMUTABLE+AS 'MODULE_PATHNAME';++CREATE FUNCTION plperl_to_jsonb(val internal) RETURNS jsonb+LANGUAGE C STRICT IMMUTABLE+AS 'MODULE_PATHNAME';++CREATE TRANSFORM FOR jsonb LANGUAGE plperlu (+ FROM SQL WITH FUNCTION jsonb_to_plperl(internal),+ TO SQL WITH FUNCTION plperl_to_jsonb(internal)+);diff --git a/contrib/jsonb_plperl/jsonb_plperlu.control b/contrib/jsonb_plperl/jsonb_plperlu.controlnew file mode 100644index 0000000..946fc51--- /dev/null+++ b/contrib/jsonb_plperl/jsonb_plperlu.control@@ -0,0 +1,6 @@+# jsonb_plperl extension+comment = 'transform between jsonb and plperlu'+default_version = '1.0'+module_pathname = '$libdir/jsonb_plperl'+relocatable = true+requires = 'plperlu'diff --git a/contrib/jsonb_plperl/sql/jsonb_plperl.sql b/contrib/jsonb_plperl/sql/jsonb_plperl.sqlnew file mode 100644index 0000000..9c5d64e--- /dev/null+++ b/contrib/jsonb_plperl/sql/jsonb_plperl.sql@@ -0,0 +1,99 @@+CREATE EXTENSION jsonb_plperl CASCADE;++-- test hash -> jsonb+CREATE FUNCTION testHVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = {a => 1, b => 'boo', c => undef};+return $val;+$$;++SELECT testHVToJsonb();++-- test array -> jsonb+CREATE FUNCTION testAVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];+return $val;+$$;++SELECT testAVToJsonb();++-- test scalar -> jsonb+CREATE FUNCTION testSVToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+$val = 1;+return $val;+$$;++SELECT testSVToJsonb();++-- test blessed scalar -> jsonb+CREATE FUNCTION testBlessedToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+my $class = shift;+my $tmp = { a=>"a", 1=>"1" };+bless $tmp, $class;+return $tmp;+$$;++SELECT testBlessedToJsonb();++-- test blessed scalar -> jsonb+CREATE FUNCTION testRegexpToJsonb() RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+return ('1' =~ m(0\t2));+$$;++SELECT testRegexpToJsonb();+++-- test jsonb -> scalar -> jsonb+CREATE FUNCTION testSVToJsonb2(val jsonb) RETURNS jsonb+LANGUAGE plperl+TRANSFORM FOR TYPE jsonb+AS $$+return $_[0];+$$;+++SELECT testSVToJsonb2('null');+SELECT testSVToJsonb2('1');+SELECT testSVToJsonb2('1E+131071');+SELECT testSVToJsonb2('-1');+SELECT testSVToJsonb2('1.2');+SELECT testSVToJsonb2('-1.2');+SELECT testSVToJsonb2('"string"');+SELECT testSVToJsonb2('"NaN"');++SELECT testSVToJsonb2('true');+SELECT testSVToJsonb2('false');++SELECT testSVToJsonb2('[]');+SELECT testSVToJsonb2('[null,null]');+SELECT testSVToJsonb2('[1,2,3]');+SELECT testSVToJsonb2('[-1,2,-3]');+SELECT testSVToJsonb2('[1.2,2.3,3.4]');+SELECT testSVToJsonb2('[-1.2,2.3,-3.4]');+SELECT testSVToJsonb2('["string1","string2"]');++SELECT testSVToJsonb2('{}');+SELECT testSVToJsonb2('{"1":null}');+SELECT testSVToJsonb2('{"1":1}');+SELECT testSVToJsonb2('{"1":-1}');+SELECT testSVToJsonb2('{"1":1.1}');+SELECT testSVToJsonb2('{"1":-1.1}');+SELECT testSVToJsonb2('{"1":"string1"}');++SELECT testSVToJsonb2('{"1":{"2":[3,4,5]},"2":3}');++DROP EXTENSION plperl CASCADE;diff --git a/contrib/jsonb_plperl/sql/jsonb_plperl_relocatability.sql b/contrib/jsonb_plperl/sql/jsonb_plperl_relocatability.sqlnew file mode 100644index 0000000..4745443--- /dev/null+++ b/contrib/jsonb_plperl/sql/jsonb_plperl_relocatability.sql@@ -0,0 +1,14 @@+CREATE EXTENSION jsonb_plperl CASCADE;+CREATE SCHEMA test;+alter extension jsonb_plperl set schema test;+create function test.test(val jsonb) returns jsonb+language plperl+transform for type jsonb+as $$+return val+$$;++select test.test('1'::jsonb);++drop extension plperl cascade;+drop schema test cascade;diff --git a/contrib/jsonb_plperl/sql/jsonb_plperlu.sql b/contrib/jsonb_plperl/sql/jsonb_plperlu.sqlnew file mode 100644index 0000000..a09052c--- /dev/null+++ b/contrib/jsonb_plperl/sql/jsonb_plperlu.sql@@ -0,0 +1,99 @@+CREATE EXTENSION jsonb_plperlu CASCADE;++-- test hash -> jsonb+CREATE FUNCTION testHVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = {a => 1, b => 'boo', c => undef};+return $val;+$$;++SELECT testHVToJsonb();++-- test array -> jsonb+CREATE FUNCTION testAVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];+return $val;+$$;++SELECT testAVToJsonb();++-- test scalar -> jsonb+CREATE FUNCTION testSVToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+$val = 1;+return $val;+$$;++SELECT testSVToJsonb();++-- test blessed scalar -> jsonb+CREATE FUNCTION testBlessedToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+my $class = shift;+my $tmp = { a=>"a", 1=>"1" };+bless $tmp, $class;+return $tmp;+$$;++SELECT testBlessedToJsonb();++-- test blessed scalar -> jsonb+CREATE FUNCTION testRegexpToJsonb() RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+return ('1' =~ m(0\t2));+$$;++SELECT testRegexpToJsonb();+++-- test jsonb -> scalar -> jsonb+CREATE FUNCTION testSVToJsonb2(val jsonb) RETURNS jsonb+LANGUAGE plperlu+TRANSFORM FOR TYPE jsonb+AS $$+return $_[0];+$$;+++SELECT testSVToJsonb2('null');+SELECT testSVToJsonb2('1');+SELECT testSVToJsonb2('1E+131071');+SELECT testSVToJsonb2('-1');+SELECT testSVToJsonb2('1.2');+SELECT testSVToJsonb2('-1.2');+SELECT testSVToJsonb2('"string"');+SELECT testSVToJsonb2('"NaN"');++SELECT testSVToJsonb2('true');+SELECT testSVToJsonb2('false');++SELECT testSVToJsonb2('[]');+SELECT testSVToJsonb2('[null,null]');+SELECT testSVToJsonb2('[1,2,3]');+SELECT testSVToJsonb2('[-1,2,-3]');+SELECT testSVToJsonb2('[1.2,2.3,3.4]');+SELECT testSVToJsonb2('[-1.2,2.3,-3.4]');+SELECT testSVToJsonb2('["string1","string2"]');++SELECT testSVToJsonb2('{}');+SELECT testSVToJsonb2('{"1":null}');+SELECT testSVToJsonb2('{"1":1}');+SELECT testSVToJsonb2('{"1":-1}');+SELECT testSVToJsonb2('{"1":1.1}');+SELECT testSVToJsonb2('{"1":-1.1}');+SELECT testSVToJsonb2('{"1":"string1"}');++SELECT testSVToJsonb2('{"1":{"2":[3,4,5]},"2":3}');++DROP EXTENSION plperlu CASCADE;diff --git a/contrib/jsonb_plperl/sql/jsonb_plperlu_relocatability.sql b/contrib/jsonb_plperl/sql/jsonb_plperlu_relocatability.sqlnew file mode 100644index 0000000..29233e4--- /dev/null+++ b/contrib/jsonb_plperl/sql/jsonb_plperlu_relocatability.sql@@ -0,0 +1,14 @@+CREATE EXTENSION jsonb_plperlu CASCADE;+CREATE SCHEMA test;+alter extension jsonb_plperlu set schema test;+create function test.test(val jsonb) returns jsonb+language plperlu+transform for type jsonb+as $$+return val+$$;++select test.test('1'::jsonb);++drop extension plperlu cascade;+drop schema test cascade;diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgmlindex 731b469..d96452f 100644--- a/doc/src/sgml/json.sgml+++ b/doc/src/sgml/json.sgml@@ -569,4 +569,18 @@ 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_plperlu</literal> and <literal>jsonb_plperl</literal>+ If you use them, <type>jsonb</type> values are mapped to+ Perl RV.+ </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, pgsql-hackers@lists.postgresql.org
Subject: Re: Transform for pl/perl
In-Reply-To: <20171207125602.5326f103@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