PolarDB-for-PostgreSQL

1
CREATE EXTENSION jsonb_plpython2u CASCADE;
2
NOTICE:  installing required extension "plpython2u"
3
-- test jsonb -> python dict
4
CREATE FUNCTION test1(val jsonb) RETURNS int
5
LANGUAGE plpythonu
6
TRANSFORM FOR TYPE jsonb
7
AS $$
8
assert isinstance(val, dict)
9
assert(val == {'a': 1, 'c': 'NULL'})
10
return len(val)
11
$$;
12
SELECT test1('{"a": 1, "c": "NULL"}'::jsonb);
13
 test1 
14
-------
15
     2
16
(1 row)
17

18
-- test jsonb -> python dict
19
-- complex dict with dicts as value
20
CREATE FUNCTION test1complex(val jsonb) RETURNS int
21
LANGUAGE plpython2u
22
TRANSFORM FOR TYPE jsonb
23
AS $$
24
assert isinstance(val, dict)
25
assert(val == {"d": {"d": 1}})
26
return len(val)
27
$$;
28
SELECT test1complex('{"d": {"d": 1}}'::jsonb);
29
 test1complex 
30
--------------
31
            1
32
(1 row)
33

34
-- test jsonb[] -> python dict
35
-- dict with array as value
36
CREATE FUNCTION test1arr(val jsonb) RETURNS int
37
LANGUAGE plpythonu
38
TRANSFORM FOR TYPE jsonb
39
AS $$
40
assert isinstance(val, dict)
41
assert(val == {"d": [12, 1]})
42
return len(val)
43
$$;
44
SELECT test1arr('{"d":[12, 1]}'::jsonb);
45
 test1arr 
46
----------
47
        1
48
(1 row)
49

50
-- test jsonb[] -> python list
51
-- simple list
52
CREATE FUNCTION test2arr(val jsonb) RETURNS int
53
LANGUAGE plpythonu
54
TRANSFORM FOR TYPE jsonb
55
AS $$
56
assert isinstance(val, list)
57
assert(val == [12, 1])
58
return len(val)
59
$$;
60
SELECT test2arr('[12, 1]'::jsonb);
61
 test2arr 
62
----------
63
        2
64
(1 row)
65

66
-- test jsonb[] -> python list
67
-- array of dicts
68
CREATE FUNCTION test3arr(val jsonb) RETURNS int
69
LANGUAGE plpythonu
70
TRANSFORM FOR TYPE jsonb
71
AS $$
72
assert isinstance(val, list)
73
assert(val == [{"a": 1,"b": 2}, {"c": 3,"d": 4}])
74
return len(val)
75
$$;
76
SELECT test3arr('[{"a": 1, "b": 2}, {"c": 3,"d": 4}]'::jsonb);
77
 test3arr 
78
----------
79
        2
80
(1 row)
81

82
-- test jsonb int -> python int
83
CREATE FUNCTION test1int(val jsonb) RETURNS int
84
LANGUAGE plpythonu
85
TRANSFORM FOR TYPE jsonb
86
AS $$
87
assert(val == 1)
88
return val
89
$$;
90
SELECT test1int('1'::jsonb);
91
 test1int 
92
----------
93
        1
94
(1 row)
95

96
-- test jsonb string -> python string
97
CREATE FUNCTION test1string(val jsonb) RETURNS text
98
LANGUAGE plpythonu
99
TRANSFORM FOR TYPE jsonb
100
AS $$
101
assert(val == "a")
102
return val
103
$$;
104
SELECT test1string('"a"'::jsonb);
105
 test1string 
106
-------------
107
 a
108
(1 row)
109

110
-- test jsonb null -> python None
111
CREATE FUNCTION test1null(val jsonb) RETURNS int
112
LANGUAGE plpythonu
113
TRANSFORM FOR TYPE jsonb
114
AS $$
115
assert(val == None)
116
return 1
117
$$;
118
SELECT test1null('null'::jsonb);
119
 test1null 
120
-----------
121
         1
122
(1 row)
123

124
-- test python -> jsonb
125
CREATE FUNCTION roundtrip(val jsonb) RETURNS jsonb
126
LANGUAGE plpythonu
127
TRANSFORM FOR TYPE jsonb
128
as $$
129
return val
130
$$;
131
SELECT roundtrip('null'::jsonb);
132
 roundtrip 
133
-----------
134
 
135
(1 row)
136

137
SELECT roundtrip('1'::jsonb);
138
 roundtrip 
139
-----------
140
 1
141
(1 row)
142

143
SELECT roundtrip('1234567890.0987654321'::jsonb);
144
       roundtrip       
145
-----------------------
146
 1234567890.0987654321
147
(1 row)
148

149
SELECT roundtrip('-1234567890.0987654321'::jsonb);
150
       roundtrip        
151
------------------------
152
 -1234567890.0987654321
153
(1 row)
154

155
SELECT roundtrip('true'::jsonb);
156
 roundtrip 
157
-----------
158
 true
159
(1 row)
160

161
SELECT roundtrip('"string"'::jsonb);
162
 roundtrip 
163
-----------
164
 "string"
165
(1 row)
166

167
SELECT roundtrip('{"1": null}'::jsonb);
168
  roundtrip  
169
-------------
170
 {"1": null}
171
(1 row)
172

173
SELECT roundtrip('{"1": 1}'::jsonb);
174
 roundtrip 
175
-----------
176
 {"1": 1}
177
(1 row)
178

179
SELECT roundtrip('{"1": true}'::jsonb);
180
  roundtrip  
181
-------------
182
 {"1": true}
183
(1 row)
184

185
SELECT roundtrip('{"1": "string"}'::jsonb);
186
    roundtrip    
187
-----------------
188
 {"1": "string"}
189
(1 row)
190

191
SELECT roundtrip('[null]'::jsonb);
192
 roundtrip 
193
-----------
194
 [null]
195
(1 row)
196

197
SELECT roundtrip('[1]'::jsonb);
198
 roundtrip 
199
-----------
200
 [1]
201
(1 row)
202

203
SELECT roundtrip('[true]'::jsonb);
204
 roundtrip 
205
-----------
206
 [true]
207
(1 row)
208

209
SELECT roundtrip('["string"]'::jsonb);
210
 roundtrip  
211
------------
212
 ["string"]
213
(1 row)
214

215
SELECT roundtrip('[null, 1]'::jsonb);
216
 roundtrip 
217
-----------
218
 [null, 1]
219
(1 row)
220

221
SELECT roundtrip('[1, true]'::jsonb);
222
 roundtrip 
223
-----------
224
 [1, true]
225
(1 row)
226

227
SELECT roundtrip('[true, "string"]'::jsonb);
228
    roundtrip     
229
------------------
230
 [true, "string"]
231
(1 row)
232

233
SELECT roundtrip('["string", "string2"]'::jsonb);
234
       roundtrip       
235
-----------------------
236
 ["string", "string2"]
237
(1 row)
238

239
-- complex numbers -> jsonb
240
CREATE FUNCTION testComplexNumbers() RETURNS jsonb
241
LANGUAGE plpythonu
242
TRANSFORM FOR TYPE jsonb
243
AS $$
244
x = 1 + 2j
245
return x
246
$$;
247
SELECT testComplexNumbers();
248
ERROR:  could not convert value "(1+2j)" to jsonb
249
CONTEXT:  while creating return value
250
PL/Python function "testcomplexnumbers"
251
-- range -> jsonb
252
CREATE FUNCTION testRange() RETURNS jsonb
253
LANGUAGE plpythonu
254
TRANSFORM FOR TYPE jsonb
255
AS $$
256
x = range(3)
257
return x
258
$$;
259
SELECT testRange();
260
 testrange 
261
-----------
262
 [0, 1, 2]
263
(1 row)
264

265
-- 0xff -> jsonb
266
CREATE FUNCTION testDecimal() RETURNS jsonb
267
LANGUAGE plpythonu
268
TRANSFORM FOR TYPE jsonb
269
AS $$
270
x = 0xff
271
return x
272
$$;
273
SELECT testDecimal();
274
 testdecimal 
275
-------------
276
 255
277
(1 row)
278

279
-- tuple -> jsonb
280
CREATE FUNCTION testTuple() RETURNS jsonb
281
LANGUAGE plpythonu
282
TRANSFORM FOR TYPE jsonb
283
AS $$
284
x = (1, 'String', None)
285
return x
286
$$;
287
SELECT testTuple();
288
      testtuple      
289
---------------------
290
 [1, "String", null]
291
(1 row)
292

293
-- interesting dict -> jsonb
294
CREATE FUNCTION test_dict1() RETURNS jsonb
295
LANGUAGE plpythonu
296
TRANSFORM FOR TYPE jsonb
297
AS $$
298
x = {"a": 1, None: 2, 33: 3}
299
return x
300
$$;
301
SELECT test_dict1();
302
        test_dict1        
303
--------------------------
304
 {"": 2, "a": 1, "33": 3}
305
(1 row)
306

307