Skip to content

Commit 341e166

Browse files
peterepetere
committed
Transforms for jsonb to PL/Perl
Add a new contrib module jsonb_plperl that provides a transform between jsonb and PL/Perl. jsonb values are converted to appropriate Perl types such as arrays and hashes, and vice versa. Author: Anthony Bykov <a.bykov@postgrespro.ru> Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com> Reviewed-by: Aleksander Alekseev <a.alekseev@postgrespro.ru> Reviewed-by: Nikita Glukhov <n.gluhov@postgrespro.ru>
1 parent a08dc71 commit 341e166

13 files changed

+963
-4
lines changed

‎contrib/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -75,9 +75,9 @@ ALWAYS_SUBDIRS += sepgsql
7575
endif
7676

7777
ifeq ($(with_perl),yes)
78-
SUBDIRS += hstore_plperl
78+
SUBDIRS += hstore_plperl jsonb_plperl
7979
else
80-
ALWAYS_SUBDIRS += hstore_plperl
80+
ALWAYS_SUBDIRS += hstore_plperl jsonb_plperl
8181
endif
8282

8383
ifeq ($(with_python),yes)

‎contrib/jsonb_plperl/.gitignore

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/jsonb_plperl/Makefile

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
# contrib/jsonb_plperl/Makefile
2+
3+
MODULE_big = jsonb_plperl
4+
OBJS = jsonb_plperl.o $(WIN32RES)
5+
PGFILEDESC = "jsonb_plperl - jsonb transform for plperl"
6+
7+
PG_CPPFLAGS = -I$(top_srcdir)/src/pl/plperl
8+
9+
EXTENSION = jsonb_plperlu jsonb_plperl
10+
DATA = jsonb_plperlu--1.0.sql jsonb_plperl--1.0.sql
11+
12+
REGRESS = jsonb_plperl jsonb_plperlu
13+
14+
ifdef USE_PGXS
15+
PG_CONFIG = pg_config
16+
PGXS := $(shell $(PG_CONFIG) --pgxs)
17+
include $(PGXS)
18+
else
19+
subdir = contrib/jsonb_plperl
20+
top_builddir = ../..
21+
include $(top_builddir)/src/Makefile.global
22+
include $(top_srcdir)/contrib/contrib-global.mk
23+
endif
24+
25+
# We must link libperl explicitly
26+
ifeq ($(PORTNAME), win32)
27+
# these settings are the same as for plperl
28+
override CPPFLAGS += -DPLPERL_HAVE_UID_GID -Wno-comment
29+
# ... see silliness in plperl Makefile ...
30+
SHLIB_LINK += $(sort $(wildcard ../../src/pl/plperl/libperl*.a))
31+
else
32+
rpathdir = $(perl_archlibexp)/CORE
33+
SHLIB_LINK += $(perl_embed_ldflags)
34+
endif
35+
36+
# As with plperl we need to make sure that the CORE directory is included
37+
# last, probably because it sometimes contains some header files with names
38+
# that clash with some of ours, or with some that we include, notably on
39+
# Windows.
40+
override CPPFLAGS := $(CPPFLAGS) $(perl_embed_ccflags) -I$(perl_archlibexp)/CORE

‎contrib/jsonb_plperl/expected/jsonb_plperl.out

Lines changed: 211 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,211 @@
1+
CREATE EXTENSION jsonb_plperl CASCADE;
2+
NOTICE: installing required extension "plperl"
3+
CREATE FUNCTION testHVToJsonb() RETURNS jsonb
4+
LANGUAGE plperl
5+
TRANSFORM FOR TYPE jsonb
6+
AS $$
7+
$val = {a => 1, b => 'boo', c => undef};
8+
return $val;
9+
$$;
10+
SELECT testHVToJsonb();
11+
testhvtojsonb
12+
---------------------------------
13+
{"a": 1, "b": "boo", "c": null}
14+
(1 row)
15+
16+
CREATE FUNCTION testAVToJsonb() RETURNS jsonb
17+
LANGUAGE plperl
18+
TRANSFORM FOR TYPE jsonb
19+
AS $$
20+
$val = [{a => 1, b => 'boo', c => undef}, {d => 2}];
21+
return $val;
22+
$$;
23+
SELECT testAVToJsonb();
24+
testavtojsonb
25+
---------------------------------------------
26+
[{"a": 1, "b": "boo", "c": null}, {"d": 2}]
27+
(1 row)
28+
29+
CREATE FUNCTION testSVToJsonb() RETURNS jsonb
30+
LANGUAGE plperl
31+
TRANSFORM FOR TYPE jsonb
32+
AS $$
33+
$val = 1;
34+
return $val;
35+
$$;
36+
SELECT testSVToJsonb();
37+
testsvtojsonb
38+
---------------
39+
1
40+
(1 row)
41+
42+
CREATE FUNCTION testRegexpToJsonb() RETURNS jsonb
43+
LANGUAGE plperl
44+
TRANSFORM FOR TYPE jsonb
45+
AS $$
46+
return ('1' =~ m(0\t2));
47+
$$;
48+
SELECT testRegexpToJsonb();
49+
ERROR: cannot transform this Perl type to jsonb
50+
CONTEXT: PL/Perl function "testregexptojsonb"
51+
CREATE FUNCTION roundtrip(val jsonb) RETURNS jsonb
52+
LANGUAGE plperl
53+
TRANSFORM FOR TYPE jsonb
54+
AS $$
55+
return $_[0];
56+
$$;
57+
SELECT roundtrip('null');
58+
roundtrip
59+
-----------
60+
null
61+
(1 row)
62+
63+
SELECT roundtrip('1');
64+
roundtrip
65+
-----------
66+
1
67+
(1 row)
68+
69+
SELECT roundtrip('1E+131071');
70+
ERROR: cannot convert infinite value to jsonb
71+
CONTEXT: PL/Perl function "roundtrip"
72+
SELECT roundtrip('-1');
73+
roundtrip
74+
-----------
75+
-1
76+
(1 row)
77+
78+
SELECT roundtrip('1.2');
79+
roundtrip
80+
-----------
81+
1.2
82+
(1 row)
83+
84+
SELECT roundtrip('-1.2');
85+
roundtrip
86+
-----------
87+
-1.2
88+
(1 row)
89+
90+
SELECT roundtrip('"string"');
91+
roundtrip
92+
-----------
93+
"string"
94+
(1 row)
95+
96+
SELECT roundtrip('"NaN"');
97+
roundtrip
98+
-----------
99+
"NaN"
100+
(1 row)
101+
102+
SELECT roundtrip('true');
103+
roundtrip
104+
-----------
105+
1
106+
(1 row)
107+
108+
SELECT roundtrip('false');
109+
roundtrip
110+
-----------
111+
0
112+
(1 row)
113+
114+
SELECT roundtrip('[]');
115+
roundtrip
116+
-----------
117+
[]
118+
(1 row)
119+
120+
SELECT roundtrip('[null, null]');
121+
roundtrip
122+
--------------
123+
[null, null]
124+
(1 row)
125+
126+
SELECT roundtrip('[1, 2, 3]');
127+
roundtrip
128+
-----------
129+
[1, 2, 3]
130+
(1 row)
131+
132+
SELECT roundtrip('[-1, 2, -3]');
133+
roundtrip
134+
-------------
135+
[-1, 2, -3]
136+
(1 row)
137+
138+
SELECT roundtrip('[1.2, 2.3, 3.4]');
139+
roundtrip
140+
-----------------
141+
[1.2, 2.3, 3.4]
142+
(1 row)
143+
144+
SELECT roundtrip('[-1.2, 2.3, -3.4]');
145+
roundtrip
146+
-------------------
147+
[-1.2, 2.3, -3.4]
148+
(1 row)
149+
150+
SELECT roundtrip('["string1", "string2"]');
151+
roundtrip
152+
------------------------
153+
["string1", "string2"]
154+
(1 row)
155+
156+
SELECT roundtrip('{}');
157+
roundtrip
158+
-----------
159+
{}
160+
(1 row)
161+
162+
SELECT roundtrip('{"1": null}');
163+
roundtrip
164+
-------------
165+
{"1": null}
166+
(1 row)
167+
168+
SELECT roundtrip('{"1": 1}');
169+
roundtrip
170+
-----------
171+
{"1": 1}
172+
(1 row)
173+
174+
SELECT roundtrip('{"1": -1}');
175+
roundtrip
176+
-----------
177+
{"1": -1}
178+
(1 row)
179+
180+
SELECT roundtrip('{"1": 1.1}');
181+
roundtrip
182+
------------
183+
{"1": 1.1}
184+
(1 row)
185+
186+
SELECT roundtrip('{"1": -1.1}');
187+
roundtrip
188+
-------------
189+
{"1": -1.1}
190+
(1 row)
191+
192+
SELECT roundtrip('{"1": "string1"}');
193+
roundtrip
194+
------------------
195+
{"1": "string1"}
196+
(1 row)
197+
198+
SELECT roundtrip('{"1": {"2": [3, 4, 5]}, "2": 3}');
199+
roundtrip
200+
---------------------------------
201+
{"1": {"2": [3, 4, 5]}, "2": 3}
202+
(1 row)
203+
204+
DROP EXTENSION plperl CASCADE;
205+
NOTICE: drop cascades to 6 other objects
206+
DETAIL: drop cascades to extension jsonb_plperl
207+
drop cascades to function testhvtojsonb()
208+
drop cascades to function testavtojsonb()
209+
drop cascades to function testsvtojsonb()
210+
drop cascades to function testregexptojsonb()
211+
drop cascades to function roundtrip(jsonb)

0 commit comments

Comments
 (0)