Re: [HACKERS] Transform for pl/perl
Hi 2017-10-24 14:27 GMT+02:00 Anthony Bykov : > There are some moments I should mention: > 1. {"1":1}::jsonb is transformed into HV {"1"=>"1"}, while > ["1","2"]::jsonb is transformed into AV ["1", "2"] > > 2. If there is a numeric value appear in jsonb, it will be transformed > to SVnv through string (Numeric->String->SV->SVnv). Not the best > solution, but as far as I understand this is usual practise in > postgresql to serialize Numerics and de-serialize them. > > 3. SVnv is transformed into jsonb through string > (SVnv->String->Numeric). > > An example may also be helpful to understand extension. So, as an > example, function "test" transforms incoming jsonb into perl, > transforms it back into jsonb and returns it. > > create extension jsonb_plperl cascade; > > create or replace function test(val jsonb) > returns jsonb > transform for type jsonb > language plperl > as $$ > return $_[0]; > $$; > > select test('{"1":1,"example": null}'::jsonb); > > I am looking to this patch: 1. the patch contains some artefacts - look the word "hstore" 2. I got lot of warnings make[1]: Vstupuje se do adresáře „/home/pavel/src/postgresql/contrib/jsonb_plperl“ gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -ggdb -Og -g3 -fno-omit-frame-pointer -fPIC -I../../src/pl/plperl -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/lib64/perl5/CORE -c -o jsonb_plperl.o jsonb_plperl.c jsonb_plperl.c: In function ‘SV_FromJsonbValue’: jsonb_plperl.c:83:9: warning: ‘result’ may be used uninitialized in this function [-Wmaybe-uninitialized] return (result); ^ jsonb_plperl.c: In function ‘SV_FromJsonb’: jsonb_plperl.c:95:10: warning: ‘object’ may be used uninitialized in this function [-Wmaybe-uninitialized] HV *object; ^~ In file included from /usr/lib64/perl5/CORE/perl.h:5644:0, from ../../src/pl/plperl/plperl.h:52, from jsonb_plperl.c:17: /usr/lib64/perl5/CORE/embed.h:404:19: warning: ‘value’ may be used uninitialized in this function [-Wmaybe-uninitialized] #define newRV(a) Perl_newRV(aTHX_ a) ^~ jsonb_plperl.c:101:10: note: ‘value’ was declared here SV *value; ^ gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -ggdb -Og -g3 -fno-omit-frame-pointer -fPIC -shared -o jsonb_plperl.so jsonb_plperl.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/usr/lib64/perl5/CORE',--enable-new-dtags -Wl,-z,relro -specs=/usr/lib/rpm/redhat/redhat-hardened-ld -fstack-protector-strong -L/usr/local/lib -L/usr/lib64/perl5/CORE -lperl -lpthread -lresolv -lnsl -ldl -lm -lcrypt -lutil -lc make[1]: Opouští se adresář „/home/pavel/src/postgresql/contrib/jsonb_plperl“ [pavel@nemesis contrib]$ gcc --version gcc (GCC) 7.2.1 20170915 (Red Hat 7.2.1-2) Copyright (C) 2017 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 3. regress tests passed 4. There are not any documentation - probably it should be part of PLPerl 5. The regress tests doesn't coverage other datatypes than numbers. I miss boolean, binary, object, ... Maybe using data::dumper or some similar can be interesting Note - it is great extension, I am pleasured so transformations are used. Regards Pavel > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] Transform for pl/perl
On Tue, Oct 24, 2017 at 1:01 PM, anthony wrote: > Hello. > Please, check out jsonb transform > (https://www.postgresql.org/docs/9.5/static/sql-createtransform.html) > for pl/perl language I've implemented. Neat. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transform for pl/perl
There are some moments I should mention: 1. {"1":1}::jsonb is transformed into HV {"1"=>"1"}, while ["1","2"]::jsonb is transformed into AV ["1", "2"] 2. If there is a numeric value appear in jsonb, it will be transformed to SVnv through string (Numeric->String->SV->SVnv). Not the best solution, but as far as I understand this is usual practise in postgresql to serialize Numerics and de-serialize them. 3. SVnv is transformed into jsonb through string (SVnv->String->Numeric). An example may also be helpful to understand extension. So, as an example, function "test" transforms incoming jsonb into perl, transforms it back into jsonb and returns it. create extension jsonb_plperl cascade; create or replace function test(val jsonb) returns jsonb transform for type jsonb language plperl as $$ return $_[0]; $$; select test('{"1":1,"example": null}'::jsonb); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Transform for pl/perl
Hello. Please, check out jsonb transform (https://www.postgresql.org/docs/9.5/static/sql-createtransform.html) for pl/perl language I've implemented.diff --git a/contrib/Makefile b/contrib/Makefile index 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/Makefile new file mode 100644 index 000..8c427c5 --- /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_plperlu + +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)/CORE diff --git a/contrib/jsonb_plperl/expected/jsonb_plperl.out b/contrib/jsonb_plperl/expected/jsonb_plperl.out new file mode 100644 index 000..7a85361 --- /dev/null +++ b/contrib/jsonb_plperl/expected/jsonb_plperl.out @@ -0,0 +1,76 @@ +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 testAVToJsonb(); +testavtojsonb +- + [{"a": 1, "b": "boo", "c": null}, {"d": 2}] +(1 row) + +-- test jsonb -> scalar -> jsonb +CREATE FUNCTION testSVToJsonb2(val jsonb) RETURNS jsonb +LANGUAGE plperl +TRANSFORM FOR TYPE jsonb +AS $$ +return $_[0]; +$$; +SELECT testSVToJsonb2('1'); + testsvtojsonb2 + + 1 +(1 row) + +SELECT testSVToJsonb2('[1,2,3]'); + testsvtojsonb2 + + [1, 2, 3] +(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 5 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 testsvtojsonb2(jsonb) diff --git a/contrib/jsonb_plperl/expected/jsonb_plperlu.out b/contrib/jsonb_plperl/expected/jsonb_plperlu.out new file mode 100644 index 000..6d4be1c --- /dev/null +++ b/contrib/jsonb_plperl/expected/jsonb_plperlu.out @@ -0,0 +1,33 @@ +CREATE EXTENSION jsonb_plperlu CASCADE; +NOTICE: installing required extension "plperlu" +-- test jsonb -> hash +CREATE FUNCTION testJsonbToHV(val jsonb) RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +return $_[0]; +$$; +SELECT testJsonbToHV('{"aa":"bb", "cc":null, "dd":2}'::jsonb); + testjsonbtohv +--- + {"aa": "bb", "cc": null, "dd": 2} +(1 row) + +-- test jsonb -> av +CREATE FUNCTION testJsonbToAV(val jsonb) RETURNS jsonb +LANGUAGE plperlu +TRANSFORM FOR TYPE jsonb +AS $$ +return $_[0]; +$$; +SELECT testJsonbToAV('["bb", nul