Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit341e166

Browse files
committed
Transforms for jsonb to PL/Perl
Add a new contrib module jsonb_plperl that provides a transform betweenjsonb and PL/Perl. jsonb values are converted to appropriate Perl typessuch 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 parenta08dc71 commit341e166

File tree

13 files changed

+963
-4
lines changed

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+
ifdefUSE_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+
overrideCPPFLAGS += -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+
overrideCPPFLAGS :=$(CPPFLAGS)$(perl_embed_ccflags) -I$(perl_archlibexp)/CORE
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)
[8]ページ先頭
©2009-2025 Movatter.jp