1CREATE EXTENSION jsonb_plpython2u CASCADE; 2 3-- test jsonb -> python dict 4CREATE FUNCTION test1(val jsonb) RETURNS int 5LANGUAGE plpythonu 6TRANSFORM FOR TYPE jsonb 7AS $$ 8assert isinstance(val, dict) 9assert(val == {'a': 1, 'c': 'NULL'}) 10return len(val) 11$$; 12 13SELECT test1('{"a": 1, "c": "NULL"}'::jsonb); 14 15-- test jsonb -> python dict 16-- complex dict with dicts as value 17CREATE FUNCTION test1complex(val jsonb) RETURNS int 18LANGUAGE plpython2u 19TRANSFORM FOR TYPE jsonb 20AS $$ 21assert isinstance(val, dict) 22assert(val == {"d": {"d": 1}}) 23return len(val) 24$$; 25 26SELECT test1complex('{"d": {"d": 1}}'::jsonb); 27 28 29-- test jsonb[] -> python dict 30-- dict with array as value 31CREATE FUNCTION test1arr(val jsonb) RETURNS int 32LANGUAGE plpythonu 33TRANSFORM FOR TYPE jsonb 34AS $$ 35assert isinstance(val, dict) 36assert(val == {"d": [12, 1]}) 37return len(val) 38$$; 39 40SELECT test1arr('{"d":[12, 1]}'::jsonb); 41 42-- test jsonb[] -> python list 43-- simple list 44CREATE FUNCTION test2arr(val jsonb) RETURNS int 45LANGUAGE plpythonu 46TRANSFORM FOR TYPE jsonb 47AS $$ 48assert isinstance(val, list) 49assert(val == [12, 1]) 50return len(val) 51$$; 52 53SELECT test2arr('[12, 1]'::jsonb); 54 55-- test jsonb[] -> python list 56-- array of dicts 57CREATE FUNCTION test3arr(val jsonb) RETURNS int 58LANGUAGE plpythonu 59TRANSFORM FOR TYPE jsonb 60AS $$ 61assert isinstance(val, list) 62assert(val == [{"a": 1,"b": 2}, {"c": 3,"d": 4}]) 63return len(val) 64$$; 65 66SELECT test3arr('[{"a": 1, "b": 2}, {"c": 3,"d": 4}]'::jsonb); 67 68-- test jsonb int -> python int 69CREATE FUNCTION test1int(val jsonb) RETURNS int 70LANGUAGE plpythonu 71TRANSFORM FOR TYPE jsonb 72AS $$ 73assert(val == 1) 74return val 75$$; 76 77SELECT test1int('1'::jsonb); 78 79-- test jsonb string -> python string 80CREATE FUNCTION test1string(val jsonb) RETURNS text 81LANGUAGE plpythonu 82TRANSFORM FOR TYPE jsonb 83AS $$ 84assert(val == "a") 85return val 86$$; 87 88SELECT test1string('"a"'::jsonb); 89 90-- test jsonb null -> python None 91CREATE FUNCTION test1null(val jsonb) RETURNS int 92LANGUAGE plpythonu 93TRANSFORM FOR TYPE jsonb 94AS $$ 95assert(val == None) 96return 1 97$$; 98 99SELECT test1null('null'::jsonb); 100 101-- test python -> jsonb 102CREATE FUNCTION roundtrip(val jsonb) RETURNS jsonb 103LANGUAGE plpythonu 104TRANSFORM FOR TYPE jsonb 105as $$ 106return val 107$$; 108 109SELECT roundtrip('null'::jsonb); 110SELECT roundtrip('1'::jsonb); 111SELECT roundtrip('1234567890.0987654321'::jsonb); 112SELECT roundtrip('-1234567890.0987654321'::jsonb); 113SELECT roundtrip('true'::jsonb); 114SELECT roundtrip('"string"'::jsonb); 115 116SELECT roundtrip('{"1": null}'::jsonb); 117SELECT roundtrip('{"1": 1}'::jsonb); 118SELECT roundtrip('{"1": true}'::jsonb); 119SELECT roundtrip('{"1": "string"}'::jsonb); 120 121SELECT roundtrip('[null]'::jsonb); 122SELECT roundtrip('[1]'::jsonb); 123SELECT roundtrip('[true]'::jsonb); 124SELECT roundtrip('["string"]'::jsonb); 125SELECT roundtrip('[null, 1]'::jsonb); 126SELECT roundtrip('[1, true]'::jsonb); 127SELECT roundtrip('[true, "string"]'::jsonb); 128SELECT roundtrip('["string", "string2"]'::jsonb); 129 130-- complex numbers -> jsonb 131CREATE FUNCTION testComplexNumbers() RETURNS jsonb 132LANGUAGE plpythonu 133TRANSFORM FOR TYPE jsonb 134AS $$ 135x = 1 + 2j 136return x 137$$; 138 139SELECT testComplexNumbers(); 140 141-- range -> jsonb 142CREATE FUNCTION testRange() RETURNS jsonb 143LANGUAGE plpythonu 144TRANSFORM FOR TYPE jsonb 145AS $$ 146x = range(3) 147return x 148$$; 149 150SELECT testRange(); 151 152-- 0xff -> jsonb 153CREATE FUNCTION testDecimal() RETURNS jsonb 154LANGUAGE plpythonu 155TRANSFORM FOR TYPE jsonb 156AS $$ 157x = 0xff 158return x 159$$; 160 161SELECT testDecimal(); 162 163-- tuple -> jsonb 164CREATE FUNCTION testTuple() RETURNS jsonb 165LANGUAGE plpythonu 166TRANSFORM FOR TYPE jsonb 167AS $$ 168x = (1, 'String', None) 169return x 170$$; 171 172SELECT testTuple(); 173 174-- interesting dict -> jsonb 175CREATE FUNCTION test_dict1() RETURNS jsonb 176LANGUAGE plpythonu 177TRANSFORM FOR TYPE jsonb 178AS $$ 179x = {"a": 1, None: 2, 33: 3} 180return x 181$$; 182 183SELECT test_dict1(); 184