-- -- Test data type behavior -- -- -- Base/common types -- CREATE FUNCTION test_type_conversion_bool(x bool) RETURNS bool AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bool(true); INFO: (True, ) test_type_conversion_bool --------------------------- t (1 row) SELECT * FROM test_type_conversion_bool(false); INFO: (False, ) test_type_conversion_bool --------------------------- f (1 row) SELECT * FROM test_type_conversion_bool(null); INFO: (None, ) test_type_conversion_bool --------------------------- (1 row) -- test various other ways to express Booleans in Python CREATE FUNCTION test_type_conversion_bool_other(n int) RETURNS bool AS $$ # numbers if n == 0: ret = 0 elif n == 1: ret = 5 # strings elif n == 2: ret = '' elif n == 3: ret = 'fa' # true in Python, false in PostgreSQL # containers elif n == 4: ret = [] elif n == 5: ret = [0] plpy.info(ret, not not ret) return ret $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bool_other(0); INFO: (0, False) test_type_conversion_bool_other --------------------------------- f (1 row) SELECT * FROM test_type_conversion_bool_other(1); INFO: (5, True) test_type_conversion_bool_other --------------------------------- t (1 row) SELECT * FROM test_type_conversion_bool_other(2); INFO: ('', False) test_type_conversion_bool_other --------------------------------- f (1 row) SELECT * FROM test_type_conversion_bool_other(3); INFO: ('fa', True) test_type_conversion_bool_other --------------------------------- t (1 row) SELECT * FROM test_type_conversion_bool_other(4); INFO: ([], False) test_type_conversion_bool_other --------------------------------- f (1 row) SELECT * FROM test_type_conversion_bool_other(5); INFO: ([0], True) test_type_conversion_bool_other --------------------------------- t (1 row) CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_char('a'); INFO: ('a', ) test_type_conversion_char --------------------------- a (1 row) SELECT * FROM test_type_conversion_char(null); INFO: (None, ) test_type_conversion_char --------------------------- (1 row) CREATE FUNCTION test_type_conversion_int2(x int2) RETURNS int2 AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int2(100::int2); INFO: (100, ) test_type_conversion_int2 --------------------------- 100 (1 row) SELECT * FROM test_type_conversion_int2(-100::int2); INFO: (-100, ) test_type_conversion_int2 --------------------------- -100 (1 row) SELECT * FROM test_type_conversion_int2(null); INFO: (None, ) test_type_conversion_int2 --------------------------- (1 row) CREATE FUNCTION test_type_conversion_int4(x int4) RETURNS int4 AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int4(100); INFO: (100, ) test_type_conversion_int4 --------------------------- 100 (1 row) SELECT * FROM test_type_conversion_int4(-100); INFO: (-100, ) test_type_conversion_int4 --------------------------- -100 (1 row) SELECT * FROM test_type_conversion_int4(null); INFO: (None, ) test_type_conversion_int4 --------------------------- (1 row) CREATE FUNCTION test_type_conversion_int8(x int8) RETURNS int8 AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_int8(100); INFO: (100, ) test_type_conversion_int8 --------------------------- 100 (1 row) SELECT * FROM test_type_conversion_int8(-100); INFO: (-100, ) test_type_conversion_int8 --------------------------- -100 (1 row) SELECT * FROM test_type_conversion_int8(5000000000); INFO: (5000000000, ) test_type_conversion_int8 --------------------------- 5000000000 (1 row) SELECT * FROM test_type_conversion_int8(null); INFO: (None, ) test_type_conversion_int8 --------------------------- (1 row) CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ # print just the class name, not the type, to avoid differences # between decimal and cdecimal plpy.info(str(x), x.__class__.__name__) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_numeric(100); INFO: ('100', 'Decimal') test_type_conversion_numeric ------------------------------ 100 (1 row) SELECT * FROM test_type_conversion_numeric(-100); INFO: ('-100', 'Decimal') test_type_conversion_numeric ------------------------------ -100 (1 row) SELECT * FROM test_type_conversion_numeric(100.0); INFO: ('100.0', 'Decimal') test_type_conversion_numeric ------------------------------ 100.0 (1 row) SELECT * FROM test_type_conversion_numeric(100.00); INFO: ('100.00', 'Decimal') test_type_conversion_numeric ------------------------------ 100.00 (1 row) SELECT * FROM test_type_conversion_numeric(5000000000.5); INFO: ('5000000000.5', 'Decimal') test_type_conversion_numeric ------------------------------ 5000000000.5 (1 row) SELECT * FROM test_type_conversion_numeric(1234567890.0987654321); INFO: ('1234567890.0987654321', 'Decimal') test_type_conversion_numeric ------------------------------ 1234567890.0987654321 (1 row) SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321); INFO: ('-1234567890.0987654321', 'Decimal') test_type_conversion_numeric ------------------------------ -1234567890.0987654321 (1 row) SELECT * FROM test_type_conversion_numeric(null); INFO: ('None', 'NoneType') test_type_conversion_numeric ------------------------------ (1 row) CREATE FUNCTION test_type_conversion_float4(x float4) RETURNS float4 AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_float4(100); INFO: (100.0, ) test_type_conversion_float4 ----------------------------- 100 (1 row) SELECT * FROM test_type_conversion_float4(-100); INFO: (-100.0, ) test_type_conversion_float4 ----------------------------- -100 (1 row) SELECT * FROM test_type_conversion_float4(5000.5); INFO: (5000.5, ) test_type_conversion_float4 ----------------------------- 5000.5 (1 row) SELECT * FROM test_type_conversion_float4(null); INFO: (None, ) test_type_conversion_float4 ----------------------------- (1 row) CREATE FUNCTION test_type_conversion_float8(x float8) RETURNS float8 AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_float8(100); INFO: (100.0, ) test_type_conversion_float8 ----------------------------- 100 (1 row) SELECT * FROM test_type_conversion_float8(-100); INFO: (-100.0, ) test_type_conversion_float8 ----------------------------- -100 (1 row) SELECT * FROM test_type_conversion_float8(5000000000.5); INFO: (5000000000.5, ) test_type_conversion_float8 ----------------------------- 5000000000.5 (1 row) SELECT * FROM test_type_conversion_float8(null); INFO: (None, ) test_type_conversion_float8 ----------------------------- (1 row) SELECT * FROM test_type_conversion_float8(100100100.654321); INFO: (100100100.654321, ) test_type_conversion_float8 ----------------------------- 100100100.654321 (1 row) CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_oid(100); INFO: (100, ) test_type_conversion_oid -------------------------- 100 (1 row) SELECT * FROM test_type_conversion_oid(2147483649); INFO: (2147483649, ) test_type_conversion_oid -------------------------- 2147483649 (1 row) SELECT * FROM test_type_conversion_oid(null); INFO: (None, ) test_type_conversion_oid -------------------------- (1 row) CREATE FUNCTION test_type_conversion_text(x text) RETURNS text AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_text('hello world'); INFO: ('hello world', ) test_type_conversion_text --------------------------- hello world (1 row) SELECT * FROM test_type_conversion_text(null); INFO: (None, ) test_type_conversion_text --------------------------- (1 row) CREATE FUNCTION test_type_conversion_bytea(x bytea) RETURNS bytea AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bytea('hello world'); INFO: (b'hello world', ) test_type_conversion_bytea ---------------------------- \x68656c6c6f20776f726c64 (1 row) SELECT * FROM test_type_conversion_bytea(E'null\\000byte'); INFO: (b'null\x00byte', ) test_type_conversion_bytea ---------------------------- \x6e756c6c0062797465 (1 row) SELECT * FROM test_type_conversion_bytea(null); INFO: (None, ) test_type_conversion_bytea ---------------------------- (1 row) CREATE FUNCTION test_type_marshal() RETURNS bytea AS $$ import marshal return marshal.dumps('hello world') $$ LANGUAGE plpython3u; CREATE FUNCTION test_type_unmarshal(x bytea) RETURNS text AS $$ import marshal try: return marshal.loads(x) except ValueError as e: return 'FAILED: ' + str(e) $$ LANGUAGE plpython3u; SELECT test_type_unmarshal(x) FROM test_type_marshal() x; test_type_unmarshal --------------------- hello world (1 row) -- -- Domains -- CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL); CREATE FUNCTION test_type_conversion_booltrue(x booltrue, y bool) RETURNS booltrue AS $$ return y $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_booltrue(true, true); test_type_conversion_booltrue ------------------------------- t (1 row) SELECT * FROM test_type_conversion_booltrue(false, true); ERROR: value for domain booltrue violates check constraint "booltrue_check" SELECT * FROM test_type_conversion_booltrue(true, false); ERROR: value for domain booltrue violates check constraint "booltrue_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_booltrue" CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0); CREATE FUNCTION test_type_conversion_uint2(x uint2, y int) RETURNS uint2 AS $$ plpy.info(x, type(x)) return y $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_uint2(100::uint2, 50); INFO: (100, ) test_type_conversion_uint2 ---------------------------- 50 (1 row) SELECT * FROM test_type_conversion_uint2(100::uint2, -50); INFO: (100, ) ERROR: value for domain uint2 violates check constraint "uint2_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_uint2" SELECT * FROM test_type_conversion_uint2(null, 1); INFO: (None, ) test_type_conversion_uint2 ---------------------------- 1 (1 row) CREATE DOMAIN nnint AS int CHECK (VALUE IS NOT NULL); CREATE FUNCTION test_type_conversion_nnint(x nnint, y int) RETURNS nnint AS $$ return y $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_nnint(10, 20); test_type_conversion_nnint ---------------------------- 20 (1 row) SELECT * FROM test_type_conversion_nnint(null, 20); ERROR: value for domain nnint violates check constraint "nnint_check" SELECT * FROM test_type_conversion_nnint(10, null); ERROR: value for domain nnint violates check constraint "nnint_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_nnint" CREATE DOMAIN bytea10 AS bytea CHECK (octet_length(VALUE) = 10 AND VALUE IS NOT NULL); CREATE FUNCTION test_type_conversion_bytea10(x bytea10, y bytea) RETURNS bytea10 AS $$ plpy.info(x, type(x)) return y $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_bytea10('hello wold', 'hello wold'); INFO: (b'hello wold', ) test_type_conversion_bytea10 ------------------------------ \x68656c6c6f20776f6c64 (1 row) SELECT * FROM test_type_conversion_bytea10('hello world', 'hello wold'); ERROR: value for domain bytea10 violates check constraint "bytea10_check" SELECT * FROM test_type_conversion_bytea10('hello word', 'hello world'); INFO: (b'hello word', ) ERROR: value for domain bytea10 violates check constraint "bytea10_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_bytea10" SELECT * FROM test_type_conversion_bytea10(null, 'hello word'); ERROR: value for domain bytea10 violates check constraint "bytea10_check" SELECT * FROM test_type_conversion_bytea10('hello word', null); INFO: (b'hello word', ) ERROR: value for domain bytea10 violates check constraint "bytea10_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_bytea10" -- -- Arrays -- CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]); INFO: ([0, 100], ) test_type_conversion_array_int4 --------------------------------- {0,100} (1 row) SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]); INFO: ([0, -100, 55], ) test_type_conversion_array_int4 --------------------------------- {0,-100,55} (1 row) SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]); INFO: ([None, 1], ) test_type_conversion_array_int4 --------------------------------- {NULL,1} (1 row) SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]); INFO: ([], ) test_type_conversion_array_int4 --------------------------------- {} (1 row) SELECT * FROM test_type_conversion_array_int4(NULL); INFO: (None, ) test_type_conversion_array_int4 --------------------------------- (1 row) SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]); INFO: ([[1, 2, 3], [4, 5, 6]], ) test_type_conversion_array_int4 --------------------------------- {{1,2,3},{4,5,6}} (1 row) SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]); INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], ) test_type_conversion_array_int4 --------------------------------------------------- {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} (1 row) SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}'); INFO: ([1, 2, 3], ) test_type_conversion_array_int4 --------------------------------- {1,2,3} (1 row) CREATE FUNCTION test_type_conversion_array_int8(x int8[]) RETURNS int8[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_int8(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]::int8[]); INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], ) test_type_conversion_array_int8 --------------------------------------------------- {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} (1 row) CREATE FUNCTION test_type_conversion_array_date(x date[]) RETURNS date[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_date(ARRAY[[['2016-09-21','2016-09-22',NULL],[NULL,'2016-10-21','2016-10-22']], [[NULL,'2016-11-21','2016-10-21'],['2015-09-21','2015-09-22','2014-09-21']]]::date[]); INFO: ([[['09-21-2016', '09-22-2016', None], [None, '10-21-2016', '10-22-2016']], [[None, '11-21-2016', '10-21-2016'], ['09-21-2015', '09-22-2015', '09-21-2014']]], ) test_type_conversion_array_date --------------------------------------------------------------------------------------------------------------------------------- {{{09-21-2016,09-22-2016,NULL},{NULL,10-21-2016,10-22-2016}},{{NULL,11-21-2016,10-21-2016},{09-21-2015,09-22-2015,09-21-2014}}} (1 row) CREATE FUNCTION test_type_conversion_array_timestamp(x timestamp[]) RETURNS timestamp[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_timestamp(ARRAY[[['2016-09-21 15:34:24.078792-04','2016-10-22 11:34:24.078795-04',NULL], [NULL,'2016-10-21 11:34:25.078792-04','2016-10-21 11:34:24.098792-04']], [[NULL,'2016-01-21 11:34:24.078792-04','2016-11-21 11:34:24.108792-04'], ['2015-09-21 11:34:24.079792-04','2014-09-21 11:34:24.078792-04','2013-09-21 11:34:24.078792-04']]]::timestamp[]); INFO: ([[['Wed Sep 21 15:34:24.078792 2016', 'Sat Oct 22 11:34:24.078795 2016', None], [None, 'Fri Oct 21 11:34:25.078792 2016', 'Fri Oct 21 11:34:24.098792 2016']], [[None, 'Thu Jan 21 11:34:24.078792 2016', 'Mon Nov 21 11:34:24.108792 2016'], ['Mon Sep 21 11:34:24.079792 2015', 'Sun Sep 21 11:34:24.078792 2014', 'Sat Sep 21 11:34:24.078792 2013']]], ) test_type_conversion_array_timestamp ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ {{{"Wed Sep 21 15:34:24.078792 2016","Sat Oct 22 11:34:24.078795 2016",NULL},{NULL,"Fri Oct 21 11:34:25.078792 2016","Fri Oct 21 11:34:24.098792 2016"}},{{NULL,"Thu Jan 21 11:34:24.078792 2016","Mon Nov 21 11:34:24.108792 2016"},{"Mon Sep 21 11:34:24.079792 2015","Sun Sep 21 11:34:24.078792 2014","Sat Sep 21 11:34:24.078792 2013"}}} (1 row) CREATE OR REPLACE FUNCTION pyreturnmultidemint4(h int4, i int4, j int4, k int4 ) RETURNS int4[] AS $BODY$ m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] plpy.info(m, type(m)) return m $BODY$ LANGUAGE plpython3u; select pyreturnmultidemint4(8,5,3,2); INFO: ([[[[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]]], [[[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]], [[0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7], [0, 1, 2, 3, 4, 5, 6, 7]]]], ) pyreturnmultidemint4 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {{{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}}},{{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}},{{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7},{0,1,2,3,4,5,6,7}}}} (1 row) CREATE OR REPLACE FUNCTION pyreturnmultidemint8(h int4, i int4, j int4, k int4 ) RETURNS int8[] AS $BODY$ m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] plpy.info(m, type(m)) return m $BODY$ LANGUAGE plpython3u; select pyreturnmultidemint8(5,5,3,2); INFO: ([[[[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]]], [[[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]], [[0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4], [0, 1, 2, 3, 4]]]], ) pyreturnmultidemint8 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {{{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}}},{{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}},{{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4},{0,1,2,3,4}}}} (1 row) CREATE OR REPLACE FUNCTION pyreturnmultidemfloat4(h int4, i int4, j int4, k int4 ) RETURNS float4[] AS $BODY$ m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] plpy.info(m, type(m)) return m $BODY$ LANGUAGE plpython3u; select pyreturnmultidemfloat4(6,5,3,2); INFO: ([[[[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]]], [[[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]], [[0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5], [0, 1, 2, 3, 4, 5]]]], ) pyreturnmultidemfloat4 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {{{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}}},{{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}},{{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5},{0,1,2,3,4,5}}}} (1 row) CREATE OR REPLACE FUNCTION pyreturnmultidemfloat8(h int4, i int4, j int4, k int4 ) RETURNS float8[] AS $BODY$ m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] plpy.info(m, type(m)) return m $BODY$ LANGUAGE plpython3u; select pyreturnmultidemfloat8(7,5,3,2); INFO: ([[[[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]]], [[[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]], [[0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6], [0, 1, 2, 3, 4, 5, 6]]]], ) pyreturnmultidemfloat8 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- {{{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}}},{{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}},{{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6},{0,1,2,3,4,5,6}}}} (1 row) CREATE FUNCTION test_type_conversion_array_text(x text[]) RETURNS text[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_text(ARRAY['foo', 'bar']); INFO: (['foo', 'bar'], ) test_type_conversion_array_text --------------------------------- {foo,bar} (1 row) SELECT * FROM test_type_conversion_array_text(ARRAY[['foo', 'bar'],['foo2', 'bar2']]); INFO: ([['foo', 'bar'], ['foo2', 'bar2']], ) test_type_conversion_array_text --------------------------------- {{foo,bar},{foo2,bar2}} (1 row) CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]); INFO: ([b'\xde\xad\xbe\xef', None], ) test_type_conversion_array_bytea ---------------------------------- {"\\xdeadbeef",NULL} (1 row) CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$ return [123, 'abc'] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_mixed1(); test_type_conversion_array_mixed1 ----------------------------------- {123,abc} (1 row) CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$ return [123, 'abc'] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_mixed2(); ERROR: invalid input syntax for type integer: "abc" CONTEXT: while creating return value PL/Python function "test_type_conversion_array_mixed2" -- check output of multi-dimensional arrays CREATE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [['a'], ['b'], ['c']] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); test_type_conversion_md_array_out ----------------------------------- {{a},{b},{c}} (1 row) CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [[], []] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); test_type_conversion_md_array_out ----------------------------------- {} (1 row) CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [[], [1]] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); -- fail ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_md_array_out" CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [[], 1] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); -- fail ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_md_array_out" CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [1, []] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); -- fail ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_md_array_out" CREATE OR REPLACE FUNCTION test_type_conversion_md_array_out() RETURNS text[] AS $$ return [[1], [[]]] $$ LANGUAGE plpython3u; select test_type_conversion_md_array_out(); -- fail ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_md_array_out" CREATE FUNCTION test_type_conversion_mdarray_malformed() RETURNS int[] AS $$ return [[1,2,3],[4,5]] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_mdarray_malformed(); ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_mdarray_malformed" CREATE FUNCTION test_type_conversion_mdarray_malformed2() RETURNS text[] AS $$ return [[1,2,3], "abc"] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_mdarray_malformed2(); ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_mdarray_malformed2" CREATE FUNCTION test_type_conversion_mdarray_malformed3() RETURNS text[] AS $$ return ["abc", [1,2,3]] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_mdarray_malformed3(); ERROR: multidimensional arrays must have array expressions with matching dimensions CONTEXT: while creating return value PL/Python function "test_type_conversion_mdarray_malformed3" CREATE FUNCTION test_type_conversion_mdarray_toodeep() RETURNS int[] AS $$ return [[[[[[[1]]]]]]] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_mdarray_toodeep(); ERROR: number of array dimensions exceeds the maximum allowed (6) CONTEXT: while creating return value PL/Python function "test_type_conversion_mdarray_toodeep" CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$ return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_record(); test_type_conversion_array_record ----------------------------------- {"(one,42)","(two,11)"} (1 row) CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$ return 'abc' $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_string(); test_type_conversion_array_string ----------------------------------- {a,b,c} (1 row) CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$ return ('abc', 'def') $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_tuple(); test_type_conversion_array_tuple ---------------------------------- {abc,def} (1 row) CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$ return 5 $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_error(); ERROR: return value of function with array return type is not a Python sequence CONTEXT: while creating return value PL/Python function "test_type_conversion_array_error" -- -- Domains over arrays -- CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]); CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$ plpy.info(x, type(x)) return x $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain); INFO: ([0, 100], ) test_type_conversion_array_domain ----------------------------------- {0,100} (1 row) SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain); INFO: (None, ) test_type_conversion_array_domain ----------------------------------- (1 row) CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$ return [2,1] $$ LANGUAGE plpython3u; SELECT * FROM test_type_conversion_array_domain_check_violation(); ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" CONTEXT: while creating return value PL/Python function "test_type_conversion_array_domain_check_violation" -- -- Arrays of domains -- CREATE FUNCTION test_read_uint2_array(x uint2[]) RETURNS uint2 AS $$ plpy.info(x, type(x)) return x[0] $$ LANGUAGE plpython3u; select test_read_uint2_array(array[1::uint2]); INFO: ([1], ) test_read_uint2_array ----------------------- 1 (1 row) CREATE FUNCTION test_build_uint2_array(x int2) RETURNS uint2[] AS $$ return [x, x] $$ LANGUAGE plpython3u; select test_build_uint2_array(1::int2); test_build_uint2_array ------------------------ {1,1} (1 row) select test_build_uint2_array(-1::int2); -- fail ERROR: value for domain uint2 violates check constraint "uint2_check" CONTEXT: while creating return value PL/Python function "test_build_uint2_array" -- -- ideally this would work, but for now it doesn't, because the return value -- is [[2,4], [2,4]] which our conversion code thinks should become a 2-D -- integer array, not an array of arrays. -- CREATE FUNCTION test_type_conversion_domain_array(x integer[]) RETURNS ordered_pair_domain[] AS $$ return [x, x] $$ LANGUAGE plpython3u; select test_type_conversion_domain_array(array[2,4]); ERROR: return value of function with array return type is not a Python sequence CONTEXT: while creating return value PL/Python function "test_type_conversion_domain_array" select test_type_conversion_domain_array(array[4,2]); -- fail ERROR: return value of function with array return type is not a Python sequence CONTEXT: while creating return value PL/Python function "test_type_conversion_domain_array" CREATE FUNCTION test_type_conversion_domain_array2(x ordered_pair_domain) RETURNS integer AS $$ plpy.info(x, type(x)) return x[1] $$ LANGUAGE plpython3u; select test_type_conversion_domain_array2(array[2,4]); INFO: ([2, 4], ) test_type_conversion_domain_array2 ------------------------------------ 4 (1 row) select test_type_conversion_domain_array2(array[4,2]); -- fail ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" CREATE FUNCTION test_type_conversion_array_domain_array(x ordered_pair_domain[]) RETURNS ordered_pair_domain AS $$ plpy.info(x, type(x)) return x[0] $$ LANGUAGE plpython3u; select test_type_conversion_array_domain_array(array[array[2,4]::ordered_pair_domain]); INFO: ([[2, 4]], ) test_type_conversion_array_domain_array ----------------------------------------- {2,4} (1 row) --- --- Composite types --- CREATE TABLE employee ( name text, basesalary integer, bonus integer ); INSERT INTO employee VALUES ('John', 100, 10), ('Mary', 200, 10); CREATE OR REPLACE FUNCTION test_composite_table_input(e employee) RETURNS integer AS $$ return e['basesalary'] + e['bonus'] $$ LANGUAGE plpython3u; SELECT name, test_composite_table_input(employee.*) FROM employee; name | test_composite_table_input ------+---------------------------- John | 110 Mary | 210 (2 rows) ALTER TABLE employee DROP bonus; SELECT name, test_composite_table_input(employee.*) FROM employee; ERROR: KeyError: 'bonus' CONTEXT: Traceback (most recent call last): PL/Python function "test_composite_table_input", line 2, in return e['basesalary'] + e['bonus'] PL/Python function "test_composite_table_input" ALTER TABLE employee ADD bonus integer; UPDATE employee SET bonus = 10; SELECT name, test_composite_table_input(employee.*) FROM employee; name | test_composite_table_input ------+---------------------------- John | 110 Mary | 210 (2 rows) CREATE TYPE named_pair AS ( i integer, j integer ); CREATE OR REPLACE FUNCTION test_composite_type_input(p named_pair) RETURNS integer AS $$ return sum(p.values()) $$ LANGUAGE plpython3u; SELECT test_composite_type_input(row(1, 2)); test_composite_type_input --------------------------- 3 (1 row) ALTER TYPE named_pair RENAME TO named_pair_2; SELECT test_composite_type_input(row(1, 2)); test_composite_type_input --------------------------- 3 (1 row) -- -- Domains within composite -- CREATE TYPE nnint_container AS (f1 int, f2 nnint); CREATE FUNCTION nnint_test(x int, y int) RETURNS nnint_container AS $$ return {'f1': x, 'f2': y} $$ LANGUAGE plpython3u; SELECT nnint_test(null, 3); nnint_test ------------ (,3) (1 row) SELECT nnint_test(3, null); -- fail ERROR: value for domain nnint violates check constraint "nnint_check" CONTEXT: while creating return value PL/Python function "nnint_test" -- -- Domains of composite -- CREATE DOMAIN ordered_named_pair AS named_pair_2 CHECK((VALUE).i <= (VALUE).j); CREATE FUNCTION read_ordered_named_pair(p ordered_named_pair) RETURNS integer AS $$ return p['i'] + p['j'] $$ LANGUAGE plpython3u; SELECT read_ordered_named_pair(row(1, 2)); read_ordered_named_pair ------------------------- 3 (1 row) SELECT read_ordered_named_pair(row(2, 1)); -- fail ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" CREATE FUNCTION build_ordered_named_pair(i int, j int) RETURNS ordered_named_pair AS $$ return {'i': i, 'j': j} $$ LANGUAGE plpython3u; SELECT build_ordered_named_pair(1,2); build_ordered_named_pair -------------------------- (1,2) (1 row) SELECT build_ordered_named_pair(2,1); -- fail ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" CONTEXT: while creating return value PL/Python function "build_ordered_named_pair" CREATE FUNCTION build_ordered_named_pairs(i int, j int) RETURNS ordered_named_pair[] AS $$ return [{'i': i, 'j': j}, {'i': i, 'j': j+1}] $$ LANGUAGE plpython3u; SELECT build_ordered_named_pairs(1,2); build_ordered_named_pairs --------------------------- {"(1,2)","(1,3)"} (1 row) SELECT build_ordered_named_pairs(2,1); -- fail ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" CONTEXT: while creating return value PL/Python function "build_ordered_named_pairs" -- -- Prepared statements -- CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int LANGUAGE plpython3u AS $$ plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python return rv[0]['val'] $$; SELECT test_prep_bool_input(); -- 1 test_prep_bool_input ---------------------- 1 (1 row) CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool LANGUAGE plpython3u AS $$ plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) rv = plpy.execute(plan, [0], 5) plpy.info(rv[0]) return rv[0]['val'] $$; SELECT test_prep_bool_output(); -- false INFO: {'val': False} test_prep_bool_output ----------------------- f (1 row) CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int LANGUAGE plpython3u AS $$ plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) rv = plpy.execute(plan, [bb], 5) return rv[0]['val'] $$; SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) test_prep_bytea_input ----------------------- 3 (1 row) CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea LANGUAGE plpython3u AS $$ plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") rv = plpy.execute(plan, [], 5) plpy.info(rv[0]) return rv[0]['val'] $$; SELECT test_prep_bytea_output(); INFO: {'val': b'\xaa\x00\xbb'} test_prep_bytea_output ------------------------ \xaa00bb (1 row)