1-- 2-- Test data type behavior 3-- 4-- 5-- Base/common types 6-- 7CREATE FUNCTION test_type_conversion_bool(x bool) RETURNS bool AS $$ 8plpy.info(x, type(x)) 9return x 10$$ LANGUAGE plpython3u; 11SELECT * FROM test_type_conversion_bool(true); 12INFO: (True, <class 'bool'>) 13 test_type_conversion_bool 14--------------------------- 15 t 16(1 row) 17 18SELECT * FROM test_type_conversion_bool(false); 19INFO: (False, <class 'bool'>) 20 test_type_conversion_bool 21--------------------------- 22 f 23(1 row) 24 25SELECT * FROM test_type_conversion_bool(null); 26INFO: (None, <class 'NoneType'>) 27 test_type_conversion_bool 28--------------------------- 29 30(1 row) 31 32-- test various other ways to express Booleans in Python 33CREATE FUNCTION test_type_conversion_bool_other(n int) RETURNS bool AS $$ 34# numbers 35if n == 0: 36 ret = 0 37elif n == 1: 38 ret = 5 39# strings 40elif n == 2: 41 ret = '' 42elif n == 3: 43 ret = 'fa' # true in Python, false in PostgreSQL 44# containers 45elif n == 4: 46 ret = [] 47elif n == 5: 48 ret = [0] 49plpy.info(ret, not not ret) 50return ret 51$$ LANGUAGE plpython3u; 52SELECT * FROM test_type_conversion_bool_other(0); 53INFO: (0, False) 54 test_type_conversion_bool_other 55--------------------------------- 56 f 57(1 row) 58 59SELECT * FROM test_type_conversion_bool_other(1); 60INFO: (5, True) 61 test_type_conversion_bool_other 62--------------------------------- 63 t 64(1 row) 65 66SELECT * FROM test_type_conversion_bool_other(2); 67INFO: ('', False) 68 test_type_conversion_bool_other 69--------------------------------- 70 f 71(1 row) 72 73SELECT * FROM test_type_conversion_bool_other(3); 74INFO: ('fa', True) 75 test_type_conversion_bool_other 76--------------------------------- 77 t 78(1 row) 79 80SELECT * FROM test_type_conversion_bool_other(4); 81INFO: ([], False) 82 test_type_conversion_bool_other 83--------------------------------- 84 f 85(1 row) 86 87SELECT * FROM test_type_conversion_bool_other(5); 88INFO: ([0], True) 89 test_type_conversion_bool_other 90--------------------------------- 91 t 92(1 row) 93 94CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$ 95plpy.info(x, type(x)) 96return x 97$$ LANGUAGE plpython3u; 98SELECT * FROM test_type_conversion_char('a'); 99INFO: ('a', <class 'str'>) 100 test_type_conversion_char 101--------------------------- 102 a 103(1 row) 104 105SELECT * FROM test_type_conversion_char(null); 106INFO: (None, <class 'NoneType'>) 107 test_type_conversion_char 108--------------------------- 109 110(1 row) 111 112CREATE FUNCTION test_type_conversion_int2(x int2) RETURNS int2 AS $$ 113plpy.info(x, type(x)) 114return x 115$$ LANGUAGE plpython3u; 116SELECT * FROM test_type_conversion_int2(100::int2); 117INFO: (100, <class 'int'>) 118 test_type_conversion_int2 119--------------------------- 120 100 121(1 row) 122 123SELECT * FROM test_type_conversion_int2(-100::int2); 124INFO: (-100, <class 'int'>) 125 test_type_conversion_int2 126--------------------------- 127 -100 128(1 row) 129 130SELECT * FROM test_type_conversion_int2(null); 131INFO: (None, <class 'NoneType'>) 132 test_type_conversion_int2 133--------------------------- 134 135(1 row) 136 137CREATE FUNCTION test_type_conversion_int4(x int4) RETURNS int4 AS $$ 138plpy.info(x, type(x)) 139return x 140$$ LANGUAGE plpython3u; 141SELECT * FROM test_type_conversion_int4(100); 142INFO: (100, <class 'int'>) 143 test_type_conversion_int4 144--------------------------- 145 100 146(1 row) 147 148SELECT * FROM test_type_conversion_int4(-100); 149INFO: (-100, <class 'int'>) 150 test_type_conversion_int4 151--------------------------- 152 -100 153(1 row) 154 155SELECT * FROM test_type_conversion_int4(null); 156INFO: (None, <class 'NoneType'>) 157 test_type_conversion_int4 158--------------------------- 159 160(1 row) 161 162CREATE FUNCTION test_type_conversion_int8(x int8) RETURNS int8 AS $$ 163plpy.info(x, type(x)) 164return x 165$$ LANGUAGE plpython3u; 166SELECT * FROM test_type_conversion_int8(100); 167INFO: (100, <class 'int'>) 168 test_type_conversion_int8 169--------------------------- 170 100 171(1 row) 172 173SELECT * FROM test_type_conversion_int8(-100); 174INFO: (-100, <class 'int'>) 175 test_type_conversion_int8 176--------------------------- 177 -100 178(1 row) 179 180SELECT * FROM test_type_conversion_int8(5000000000); 181INFO: (5000000000, <class 'int'>) 182 test_type_conversion_int8 183--------------------------- 184 5000000000 185(1 row) 186 187SELECT * FROM test_type_conversion_int8(null); 188INFO: (None, <class 'NoneType'>) 189 test_type_conversion_int8 190--------------------------- 191 192(1 row) 193 194CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$ 195# print just the class name, not the type, to avoid differences 196# between decimal and cdecimal 197plpy.info(str(x), x.__class__.__name__) 198return x 199$$ LANGUAGE plpython3u; 200SELECT * FROM test_type_conversion_numeric(100); 201INFO: ('100', 'Decimal') 202 test_type_conversion_numeric 203------------------------------ 204 100 205(1 row) 206 207SELECT * FROM test_type_conversion_numeric(-100); 208INFO: ('-100', 'Decimal') 209 test_type_conversion_numeric 210------------------------------ 211 -100 212(1 row) 213 214SELECT * FROM test_type_conversion_numeric(100.0); 215INFO: ('100.0', 'Decimal') 216 test_type_conversion_numeric 217------------------------------ 218 100.0 219(1 row) 220 221SELECT * FROM test_type_conversion_numeric(100.00); 222INFO: ('100.00', 'Decimal') 223 test_type_conversion_numeric 224------------------------------ 225 100.00 226(1 row) 227 228SELECT * FROM test_type_conversion_numeric(5000000000.5); 229INFO: ('5000000000.5', 'Decimal') 230 test_type_conversion_numeric 231------------------------------ 232 5000000000.5 233(1 row) 234 235SELECT * FROM test_type_conversion_numeric(1234567890.0987654321); 236INFO: ('1234567890.0987654321', 'Decimal') 237 test_type_conversion_numeric 238------------------------------ 239 1234567890.0987654321 240(1 row) 241 242SELECT * FROM test_type_conversion_numeric(-1234567890.0987654321); 243INFO: ('-1234567890.0987654321', 'Decimal') 244 test_type_conversion_numeric 245------------------------------ 246 -1234567890.0987654321 247(1 row) 248 249SELECT * FROM test_type_conversion_numeric(null); 250INFO: ('None', 'NoneType') 251 test_type_conversion_numeric 252------------------------------ 253 254(1 row) 255 256CREATE FUNCTION test_type_conversion_float4(x float4) RETURNS float4 AS $$ 257plpy.info(x, type(x)) 258return x 259$$ LANGUAGE plpython3u; 260SELECT * FROM test_type_conversion_float4(100); 261INFO: (100.0, <class 'float'>) 262 test_type_conversion_float4 263----------------------------- 264 100 265(1 row) 266 267SELECT * FROM test_type_conversion_float4(-100); 268INFO: (-100.0, <class 'float'>) 269 test_type_conversion_float4 270----------------------------- 271 -100 272(1 row) 273 274SELECT * FROM test_type_conversion_float4(5000.5); 275INFO: (5000.5, <class 'float'>) 276 test_type_conversion_float4 277----------------------------- 278 5000.5 279(1 row) 280 281SELECT * FROM test_type_conversion_float4(null); 282INFO: (None, <class 'NoneType'>) 283 test_type_conversion_float4 284----------------------------- 285 286(1 row) 287 288CREATE FUNCTION test_type_conversion_float8(x float8) RETURNS float8 AS $$ 289plpy.info(x, type(x)) 290return x 291$$ LANGUAGE plpython3u; 292SELECT * FROM test_type_conversion_float8(100); 293INFO: (100.0, <class 'float'>) 294 test_type_conversion_float8 295----------------------------- 296 100 297(1 row) 298 299SELECT * FROM test_type_conversion_float8(-100); 300INFO: (-100.0, <class 'float'>) 301 test_type_conversion_float8 302----------------------------- 303 -100 304(1 row) 305 306SELECT * FROM test_type_conversion_float8(5000000000.5); 307INFO: (5000000000.5, <class 'float'>) 308 test_type_conversion_float8 309----------------------------- 310 5000000000.5 311(1 row) 312 313SELECT * FROM test_type_conversion_float8(null); 314INFO: (None, <class 'NoneType'>) 315 test_type_conversion_float8 316----------------------------- 317 318(1 row) 319 320SELECT * FROM test_type_conversion_float8(100100100.654321); 321INFO: (100100100.654321, <class 'float'>) 322 test_type_conversion_float8 323----------------------------- 324 100100100.654321 325(1 row) 326 327CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$ 328plpy.info(x, type(x)) 329return x 330$$ LANGUAGE plpython3u; 331SELECT * FROM test_type_conversion_oid(100); 332INFO: (100, <class 'int'>) 333 test_type_conversion_oid 334-------------------------- 335 100 336(1 row) 337 338SELECT * FROM test_type_conversion_oid(2147483649); 339INFO: (2147483649, <class 'int'>) 340 test_type_conversion_oid 341-------------------------- 342 2147483649 343(1 row) 344 345SELECT * FROM test_type_conversion_oid(null); 346INFO: (None, <class 'NoneType'>) 347 test_type_conversion_oid 348-------------------------- 349 350(1 row) 351 352CREATE FUNCTION test_type_conversion_text(x text) RETURNS text AS $$ 353plpy.info(x, type(x)) 354return x 355$$ LANGUAGE plpython3u; 356SELECT * FROM test_type_conversion_text('hello world'); 357INFO: ('hello world', <class 'str'>) 358 test_type_conversion_text 359--------------------------- 360 hello world 361(1 row) 362 363SELECT * FROM test_type_conversion_text(null); 364INFO: (None, <class 'NoneType'>) 365 test_type_conversion_text 366--------------------------- 367 368(1 row) 369 370CREATE FUNCTION test_type_conversion_bytea(x bytea) RETURNS bytea AS $$ 371plpy.info(x, type(x)) 372return x 373$$ LANGUAGE plpython3u; 374SELECT * FROM test_type_conversion_bytea('hello world'); 375INFO: (b'hello world', <class 'bytes'>) 376 test_type_conversion_bytea 377---------------------------- 378 \x68656c6c6f20776f726c64 379(1 row) 380 381SELECT * FROM test_type_conversion_bytea(E'null\\000byte'); 382INFO: (b'null\x00byte', <class 'bytes'>) 383 test_type_conversion_bytea 384---------------------------- 385 \x6e756c6c0062797465 386(1 row) 387 388SELECT * FROM test_type_conversion_bytea(null); 389INFO: (None, <class 'NoneType'>) 390 test_type_conversion_bytea 391---------------------------- 392 393(1 row) 394 395CREATE FUNCTION test_type_marshal() RETURNS bytea AS $$ 396import marshal 397return marshal.dumps('hello world') 398$$ LANGUAGE plpython3u; 399CREATE FUNCTION test_type_unmarshal(x bytea) RETURNS text AS $$ 400import marshal 401try: 402 return marshal.loads(x) 403except ValueError as e: 404 return 'FAILED: ' + str(e) 405$$ LANGUAGE plpython3u; 406SELECT test_type_unmarshal(x) FROM test_type_marshal() x; 407 test_type_unmarshal 408--------------------- 409 hello world 410(1 row) 411 412-- 413-- Domains 414-- 415CREATE DOMAIN booltrue AS bool CHECK (VALUE IS TRUE OR VALUE IS NULL); 416CREATE FUNCTION test_type_conversion_booltrue(x booltrue, y bool) RETURNS booltrue AS $$ 417return y 418$$ LANGUAGE plpython3u; 419SELECT * FROM test_type_conversion_booltrue(true, true); 420 test_type_conversion_booltrue 421------------------------------- 422 t 423(1 row) 424 425SELECT * FROM test_type_conversion_booltrue(false, true); 426ERROR: value for domain booltrue violates check constraint "booltrue_check" 427SELECT * FROM test_type_conversion_booltrue(true, false); 428ERROR: value for domain booltrue violates check constraint "booltrue_check" 429CONTEXT: while creating return value 430PL/Python function "test_type_conversion_booltrue" 431CREATE DOMAIN uint2 AS int2 CHECK (VALUE >= 0); 432CREATE FUNCTION test_type_conversion_uint2(x uint2, y int) RETURNS uint2 AS $$ 433plpy.info(x, type(x)) 434return y 435$$ LANGUAGE plpython3u; 436SELECT * FROM test_type_conversion_uint2(100::uint2, 50); 437INFO: (100, <class 'int'>) 438 test_type_conversion_uint2 439---------------------------- 440 50 441(1 row) 442 443SELECT * FROM test_type_conversion_uint2(100::uint2, -50); 444INFO: (100, <class 'int'>) 445ERROR: value for domain uint2 violates check constraint "uint2_check" 446CONTEXT: while creating return value 447PL/Python function "test_type_conversion_uint2" 448SELECT * FROM test_type_conversion_uint2(null, 1); 449INFO: (None, <class 'NoneType'>) 450 test_type_conversion_uint2 451---------------------------- 452 1 453(1 row) 454 455CREATE DOMAIN nnint AS int CHECK (VALUE IS NOT NULL); 456CREATE FUNCTION test_type_conversion_nnint(x nnint, y int) RETURNS nnint AS $$ 457return y 458$$ LANGUAGE plpython3u; 459SELECT * FROM test_type_conversion_nnint(10, 20); 460 test_type_conversion_nnint 461---------------------------- 462 20 463(1 row) 464 465SELECT * FROM test_type_conversion_nnint(null, 20); 466ERROR: value for domain nnint violates check constraint "nnint_check" 467SELECT * FROM test_type_conversion_nnint(10, null); 468ERROR: value for domain nnint violates check constraint "nnint_check" 469CONTEXT: while creating return value 470PL/Python function "test_type_conversion_nnint" 471CREATE DOMAIN bytea10 AS bytea CHECK (octet_length(VALUE) = 10 AND VALUE IS NOT NULL); 472CREATE FUNCTION test_type_conversion_bytea10(x bytea10, y bytea) RETURNS bytea10 AS $$ 473plpy.info(x, type(x)) 474return y 475$$ LANGUAGE plpython3u; 476SELECT * FROM test_type_conversion_bytea10('hello wold', 'hello wold'); 477INFO: (b'hello wold', <class 'bytes'>) 478 test_type_conversion_bytea10 479------------------------------ 480 \x68656c6c6f20776f6c64 481(1 row) 482 483SELECT * FROM test_type_conversion_bytea10('hello world', 'hello wold'); 484ERROR: value for domain bytea10 violates check constraint "bytea10_check" 485SELECT * FROM test_type_conversion_bytea10('hello word', 'hello world'); 486INFO: (b'hello word', <class 'bytes'>) 487ERROR: value for domain bytea10 violates check constraint "bytea10_check" 488CONTEXT: while creating return value 489PL/Python function "test_type_conversion_bytea10" 490SELECT * FROM test_type_conversion_bytea10(null, 'hello word'); 491ERROR: value for domain bytea10 violates check constraint "bytea10_check" 492SELECT * FROM test_type_conversion_bytea10('hello word', null); 493INFO: (b'hello word', <class 'bytes'>) 494ERROR: value for domain bytea10 violates check constraint "bytea10_check" 495CONTEXT: while creating return value 496PL/Python function "test_type_conversion_bytea10" 497-- 498-- Arrays 499-- 500CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$ 501plpy.info(x, type(x)) 502return x 503$$ LANGUAGE plpython3u; 504SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]); 505INFO: ([0, 100], <class 'list'>) 506 test_type_conversion_array_int4 507--------------------------------- 508 {0,100} 509(1 row) 510 511SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]); 512INFO: ([0, -100, 55], <class 'list'>) 513 test_type_conversion_array_int4 514--------------------------------- 515 {0,-100,55} 516(1 row) 517 518SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]); 519INFO: ([None, 1], <class 'list'>) 520 test_type_conversion_array_int4 521--------------------------------- 522 {NULL,1} 523(1 row) 524 525SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]); 526INFO: ([], <class 'list'>) 527 test_type_conversion_array_int4 528--------------------------------- 529 {} 530(1 row) 531 532SELECT * FROM test_type_conversion_array_int4(NULL); 533INFO: (None, <class 'NoneType'>) 534 test_type_conversion_array_int4 535--------------------------------- 536 537(1 row) 538 539SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]); 540INFO: ([[1, 2, 3], [4, 5, 6]], <class 'list'>) 541 test_type_conversion_array_int4 542--------------------------------- 543 {{1,2,3},{4,5,6}} 544(1 row) 545 546SELECT * FROM test_type_conversion_array_int4(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]); 547INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], <class 'list'>) 548 test_type_conversion_array_int4 549--------------------------------------------------- 550 {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} 551(1 row) 552 553SELECT * FROM test_type_conversion_array_int4('[2:4]={1,2,3}'); 554INFO: ([1, 2, 3], <class 'list'>) 555 test_type_conversion_array_int4 556--------------------------------- 557 {1,2,3} 558(1 row) 559 560CREATE FUNCTION test_type_conversion_array_int8(x int8[]) RETURNS int8[] AS $$ 561plpy.info(x, type(x)) 562return x 563$$ LANGUAGE plpython3u; 564SELECT * FROM test_type_conversion_array_int8(ARRAY[[[1,2,NULL],[NULL,5,6]],[[NULL,8,9],[10,11,12]]]::int8[]); 565INFO: ([[[1, 2, None], [None, 5, 6]], [[None, 8, 9], [10, 11, 12]]], <class 'list'>) 566 test_type_conversion_array_int8 567--------------------------------------------------- 568 {{{1,2,NULL},{NULL,5,6}},{{NULL,8,9},{10,11,12}}} 569(1 row) 570 571CREATE FUNCTION test_type_conversion_array_date(x date[]) RETURNS date[] AS $$ 572plpy.info(x, type(x)) 573return x 574$$ LANGUAGE plpython3u; 575SELECT * FROM test_type_conversion_array_date(ARRAY[[['2016-09-21','2016-09-22',NULL],[NULL,'2016-10-21','2016-10-22']], 576 [[NULL,'2016-11-21','2016-10-21'],['2015-09-21','2015-09-22','2014-09-21']]]::date[]); 577INFO: ([[['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']]], <class 'list'>) 578 test_type_conversion_array_date 579--------------------------------------------------------------------------------------------------------------------------------- 580 {{{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}}} 581(1 row) 582 583CREATE FUNCTION test_type_conversion_array_timestamp(x timestamp[]) RETURNS timestamp[] AS $$ 584plpy.info(x, type(x)) 585return x 586$$ LANGUAGE plpython3u; 587SELECT * FROM test_type_conversion_array_timestamp(ARRAY[[['2016-09-21 15:34:24.078792-04','2016-10-22 11:34:24.078795-04',NULL], 588 [NULL,'2016-10-21 11:34:25.078792-04','2016-10-21 11:34:24.098792-04']], 589 [[NULL,'2016-01-21 11:34:24.078792-04','2016-11-21 11:34:24.108792-04'], 590 ['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[]); 591INFO: ([[['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']]], <class 'list'>) 592 test_type_conversion_array_timestamp 593------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 594 {{{"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"}}} 595(1 row) 596 597CREATE OR REPLACE FUNCTION pyreturnmultidemint4(h int4, i int4, j int4, k int4 ) RETURNS int4[] AS $BODY$ 598m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] 599plpy.info(m, type(m)) 600return m 601$BODY$ LANGUAGE plpython3u; 602select pyreturnmultidemint4(8,5,3,2); 603INFO: ([[[[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]]]], <class 'list'>) 604 pyreturnmultidemint4 605------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 606 {{{{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}}}} 607(1 row) 608 609CREATE OR REPLACE FUNCTION pyreturnmultidemint8(h int4, i int4, j int4, k int4 ) RETURNS int8[] AS $BODY$ 610m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] 611plpy.info(m, type(m)) 612return m 613$BODY$ LANGUAGE plpython3u; 614select pyreturnmultidemint8(5,5,3,2); 615INFO: ([[[[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]]]], <class 'list'>) 616 pyreturnmultidemint8 617------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 618 {{{{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}}}} 619(1 row) 620 621CREATE OR REPLACE FUNCTION pyreturnmultidemfloat4(h int4, i int4, j int4, k int4 ) RETURNS float4[] AS $BODY$ 622m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] 623plpy.info(m, type(m)) 624return m 625$BODY$ LANGUAGE plpython3u; 626select pyreturnmultidemfloat4(6,5,3,2); 627INFO: ([[[[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]]]], <class 'list'>) 628 pyreturnmultidemfloat4 629------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 630 {{{{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}}}} 631(1 row) 632 633CREATE OR REPLACE FUNCTION pyreturnmultidemfloat8(h int4, i int4, j int4, k int4 ) RETURNS float8[] AS $BODY$ 634m = [[[[x for x in range(h)] for y in range(i)] for z in range(j)] for w in range(k)] 635plpy.info(m, type(m)) 636return m 637$BODY$ LANGUAGE plpython3u; 638select pyreturnmultidemfloat8(7,5,3,2); 639INFO: ([[[[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]]]], <class 'list'>) 640 pyreturnmultidemfloat8 641------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 642 {{{{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}}}} 643(1 row) 644 645CREATE FUNCTION test_type_conversion_array_text(x text[]) RETURNS text[] AS $$ 646plpy.info(x, type(x)) 647return x 648$$ LANGUAGE plpython3u; 649SELECT * FROM test_type_conversion_array_text(ARRAY['foo', 'bar']); 650INFO: (['foo', 'bar'], <class 'list'>) 651 test_type_conversion_array_text 652--------------------------------- 653 {foo,bar} 654(1 row) 655 656SELECT * FROM test_type_conversion_array_text(ARRAY[['foo', 'bar'],['foo2', 'bar2']]); 657INFO: ([['foo', 'bar'], ['foo2', 'bar2']], <class 'list'>) 658 test_type_conversion_array_text 659--------------------------------- 660 {{foo,bar},{foo2,bar2}} 661(1 row) 662 663CREATE FUNCTION test_type_conversion_array_bytea(x bytea[]) RETURNS bytea[] AS $$ 664plpy.info(x, type(x)) 665return x 666$$ LANGUAGE plpython3u; 667SELECT * FROM test_type_conversion_array_bytea(ARRAY[E'\\xdeadbeef'::bytea, NULL]); 668INFO: ([b'\xde\xad\xbe\xef', None], <class 'list'>) 669 test_type_conversion_array_bytea 670---------------------------------- 671 {"\\xdeadbeef",NULL} 672(1 row) 673 674CREATE FUNCTION test_type_conversion_array_mixed1() RETURNS text[] AS $$ 675return [123, 'abc'] 676$$ LANGUAGE plpython3u; 677SELECT * FROM test_type_conversion_array_mixed1(); 678 test_type_conversion_array_mixed1 679----------------------------------- 680 {123,abc} 681(1 row) 682 683CREATE FUNCTION test_type_conversion_array_mixed2() RETURNS int[] AS $$ 684return [123, 'abc'] 685$$ LANGUAGE plpython3u; 686SELECT * FROM test_type_conversion_array_mixed2(); 687ERROR: invalid input syntax for integer: "abc" 688CONTEXT: while creating return value 689PL/Python function "test_type_conversion_array_mixed2" 690CREATE FUNCTION test_type_conversion_mdarray_malformed() RETURNS int[] AS $$ 691return [[1,2,3],[4,5]] 692$$ LANGUAGE plpython3u; 693SELECT * FROM test_type_conversion_mdarray_malformed(); 694ERROR: wrong length of inner sequence: has length 2, but 3 was expected 695DETAIL: To construct a multidimensional array, the inner sequences must all have the same length. 696CONTEXT: while creating return value 697PL/Python function "test_type_conversion_mdarray_malformed" 698CREATE FUNCTION test_type_conversion_mdarray_toodeep() RETURNS int[] AS $$ 699return [[[[[[[1]]]]]]] 700$$ LANGUAGE plpython3u; 701SELECT * FROM test_type_conversion_mdarray_toodeep(); 702ERROR: number of array dimensions exceeds the maximum allowed (6) 703CONTEXT: while creating return value 704PL/Python function "test_type_conversion_mdarray_toodeep" 705CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$ 706return [{'first': 'one', 'second': 42}, {'first': 'two', 'second': 11}] 707$$ LANGUAGE plpython3u; 708SELECT * FROM test_type_conversion_array_record(); 709 test_type_conversion_array_record 710----------------------------------- 711 {"(one,42)","(two,11)"} 712(1 row) 713 714CREATE FUNCTION test_type_conversion_array_string() RETURNS text[] AS $$ 715return 'abc' 716$$ LANGUAGE plpython3u; 717SELECT * FROM test_type_conversion_array_string(); 718 test_type_conversion_array_string 719----------------------------------- 720 {a,b,c} 721(1 row) 722 723CREATE FUNCTION test_type_conversion_array_tuple() RETURNS text[] AS $$ 724return ('abc', 'def') 725$$ LANGUAGE plpython3u; 726SELECT * FROM test_type_conversion_array_tuple(); 727 test_type_conversion_array_tuple 728---------------------------------- 729 {abc,def} 730(1 row) 731 732CREATE FUNCTION test_type_conversion_array_error() RETURNS int[] AS $$ 733return 5 734$$ LANGUAGE plpython3u; 735SELECT * FROM test_type_conversion_array_error(); 736ERROR: return value of function with array return type is not a Python sequence 737CONTEXT: while creating return value 738PL/Python function "test_type_conversion_array_error" 739-- 740-- Domains over arrays 741-- 742CREATE DOMAIN ordered_pair_domain AS integer[] CHECK (array_length(VALUE,1)=2 AND VALUE[1] < VALUE[2]); 743CREATE FUNCTION test_type_conversion_array_domain(x ordered_pair_domain) RETURNS ordered_pair_domain AS $$ 744plpy.info(x, type(x)) 745return x 746$$ LANGUAGE plpython3u; 747SELECT * FROM test_type_conversion_array_domain(ARRAY[0, 100]::ordered_pair_domain); 748INFO: ([0, 100], <class 'list'>) 749 test_type_conversion_array_domain 750----------------------------------- 751 {0,100} 752(1 row) 753 754SELECT * FROM test_type_conversion_array_domain(NULL::ordered_pair_domain); 755INFO: (None, <class 'NoneType'>) 756 test_type_conversion_array_domain 757----------------------------------- 758 759(1 row) 760 761CREATE FUNCTION test_type_conversion_array_domain_check_violation() RETURNS ordered_pair_domain AS $$ 762return [2,1] 763$$ LANGUAGE plpython3u; 764SELECT * FROM test_type_conversion_array_domain_check_violation(); 765ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" 766CONTEXT: while creating return value 767PL/Python function "test_type_conversion_array_domain_check_violation" 768-- 769-- Arrays of domains 770-- 771CREATE FUNCTION test_read_uint2_array(x uint2[]) RETURNS uint2 AS $$ 772plpy.info(x, type(x)) 773return x[0] 774$$ LANGUAGE plpythonu; 775select test_read_uint2_array(array[1::uint2]); 776INFO: ([1], <class 'list'>) 777 test_read_uint2_array 778----------------------- 779 1 780(1 row) 781 782CREATE FUNCTION test_build_uint2_array(x int2) RETURNS uint2[] AS $$ 783return [x, x] 784$$ LANGUAGE plpythonu; 785select test_build_uint2_array(1::int2); 786 test_build_uint2_array 787------------------------ 788 {1,1} 789(1 row) 790 791select test_build_uint2_array(-1::int2); -- fail 792ERROR: value for domain uint2 violates check constraint "uint2_check" 793CONTEXT: while creating return value 794PL/Python function "test_build_uint2_array" 795-- 796-- ideally this would work, but for now it doesn't, because the return value 797-- is [[2,4], [2,4]] which our conversion code thinks should become a 2-D 798-- integer array, not an array of arrays. 799-- 800CREATE FUNCTION test_type_conversion_domain_array(x integer[]) 801 RETURNS ordered_pair_domain[] AS $$ 802return [x, x] 803$$ LANGUAGE plpythonu; 804select test_type_conversion_domain_array(array[2,4]); 805ERROR: return value of function with array return type is not a Python sequence 806CONTEXT: while creating return value 807PL/Python function "test_type_conversion_domain_array" 808select test_type_conversion_domain_array(array[4,2]); -- fail 809ERROR: return value of function with array return type is not a Python sequence 810CONTEXT: while creating return value 811PL/Python function "test_type_conversion_domain_array" 812CREATE FUNCTION test_type_conversion_domain_array2(x ordered_pair_domain) 813 RETURNS integer AS $$ 814plpy.info(x, type(x)) 815return x[1] 816$$ LANGUAGE plpythonu; 817select test_type_conversion_domain_array2(array[2,4]); 818INFO: ([2, 4], <class 'list'>) 819 test_type_conversion_domain_array2 820------------------------------------ 821 4 822(1 row) 823 824select test_type_conversion_domain_array2(array[4,2]); -- fail 825ERROR: value for domain ordered_pair_domain violates check constraint "ordered_pair_domain_check" 826CREATE FUNCTION test_type_conversion_array_domain_array(x ordered_pair_domain[]) 827 RETURNS ordered_pair_domain AS $$ 828plpy.info(x, type(x)) 829return x[0] 830$$ LANGUAGE plpythonu; 831select test_type_conversion_array_domain_array(array[array[2,4]::ordered_pair_domain]); 832INFO: ([[2, 4]], <class 'list'>) 833 test_type_conversion_array_domain_array 834----------------------------------------- 835 {2,4} 836(1 row) 837 838--- 839--- Composite types 840--- 841CREATE TABLE employee ( 842 name text, 843 basesalary integer, 844 bonus integer 845); 846INSERT INTO employee VALUES ('John', 100, 10), ('Mary', 200, 10); 847CREATE OR REPLACE FUNCTION test_composite_table_input(e employee) RETURNS integer AS $$ 848return e['basesalary'] + e['bonus'] 849$$ LANGUAGE plpython3u; 850SELECT name, test_composite_table_input(employee.*) FROM employee; 851 name | test_composite_table_input 852------+---------------------------- 853 John | 110 854 Mary | 210 855(2 rows) 856 857ALTER TABLE employee DROP bonus; 858SELECT name, test_composite_table_input(employee.*) FROM employee; 859ERROR: KeyError: 'bonus' 860CONTEXT: Traceback (most recent call last): 861 PL/Python function "test_composite_table_input", line 2, in <module> 862 return e['basesalary'] + e['bonus'] 863PL/Python function "test_composite_table_input" 864ALTER TABLE employee ADD bonus integer; 865UPDATE employee SET bonus = 10; 866SELECT name, test_composite_table_input(employee.*) FROM employee; 867 name | test_composite_table_input 868------+---------------------------- 869 John | 110 870 Mary | 210 871(2 rows) 872 873CREATE TYPE named_pair AS ( 874 i integer, 875 j integer 876); 877CREATE OR REPLACE FUNCTION test_composite_type_input(p named_pair) RETURNS integer AS $$ 878return sum(p.values()) 879$$ LANGUAGE plpython3u; 880SELECT test_composite_type_input(row(1, 2)); 881 test_composite_type_input 882--------------------------- 883 3 884(1 row) 885 886ALTER TYPE named_pair RENAME TO named_pair_2; 887SELECT test_composite_type_input(row(1, 2)); 888 test_composite_type_input 889--------------------------- 890 3 891(1 row) 892 893-- 894-- Domains within composite 895-- 896CREATE TYPE nnint_container AS (f1 int, f2 nnint); 897CREATE FUNCTION nnint_test(x int, y int) RETURNS nnint_container AS $$ 898return {'f1': x, 'f2': y} 899$$ LANGUAGE plpythonu; 900SELECT nnint_test(null, 3); 901 nnint_test 902------------ 903 (,3) 904(1 row) 905 906SELECT nnint_test(3, null); -- fail 907ERROR: value for domain nnint violates check constraint "nnint_check" 908CONTEXT: while creating return value 909PL/Python function "nnint_test" 910-- 911-- Domains of composite 912-- 913CREATE DOMAIN ordered_named_pair AS named_pair_2 CHECK((VALUE).i <= (VALUE).j); 914CREATE FUNCTION read_ordered_named_pair(p ordered_named_pair) RETURNS integer AS $$ 915return p['i'] + p['j'] 916$$ LANGUAGE plpythonu; 917SELECT read_ordered_named_pair(row(1, 2)); 918 read_ordered_named_pair 919------------------------- 920 3 921(1 row) 922 923SELECT read_ordered_named_pair(row(2, 1)); -- fail 924ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" 925CREATE FUNCTION build_ordered_named_pair(i int, j int) RETURNS ordered_named_pair AS $$ 926return {'i': i, 'j': j} 927$$ LANGUAGE plpythonu; 928SELECT build_ordered_named_pair(1,2); 929 build_ordered_named_pair 930-------------------------- 931 (1,2) 932(1 row) 933 934SELECT build_ordered_named_pair(2,1); -- fail 935ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" 936CONTEXT: while creating return value 937PL/Python function "build_ordered_named_pair" 938CREATE FUNCTION build_ordered_named_pairs(i int, j int) RETURNS ordered_named_pair[] AS $$ 939return [{'i': i, 'j': j}, {'i': i, 'j': j+1}] 940$$ LANGUAGE plpythonu; 941SELECT build_ordered_named_pairs(1,2); 942 build_ordered_named_pairs 943--------------------------- 944 {"(1,2)","(1,3)"} 945(1 row) 946 947SELECT build_ordered_named_pairs(2,1); -- fail 948ERROR: value for domain ordered_named_pair violates check constraint "ordered_named_pair_check" 949CONTEXT: while creating return value 950PL/Python function "build_ordered_named_pairs" 951-- 952-- Prepared statements 953-- 954CREATE OR REPLACE FUNCTION test_prep_bool_input() RETURNS int 955LANGUAGE plpython3u 956AS $$ 957plan = plpy.prepare("SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS val", ['boolean']) 958rv = plpy.execute(plan, ['fa'], 5) # 'fa' is true in Python 959return rv[0]['val'] 960$$; 961SELECT test_prep_bool_input(); -- 1 962 test_prep_bool_input 963---------------------- 964 1 965(1 row) 966 967CREATE OR REPLACE FUNCTION test_prep_bool_output() RETURNS bool 968LANGUAGE plpython3u 969AS $$ 970plan = plpy.prepare("SELECT $1 = 1 AS val", ['int']) 971rv = plpy.execute(plan, [0], 5) 972plpy.info(rv[0]) 973return rv[0]['val'] 974$$; 975SELECT test_prep_bool_output(); -- false 976INFO: {'val': False} 977 test_prep_bool_output 978----------------------- 979 f 980(1 row) 981 982CREATE OR REPLACE FUNCTION test_prep_bytea_input(bb bytea) RETURNS int 983LANGUAGE plpython3u 984AS $$ 985plan = plpy.prepare("SELECT octet_length($1) AS val", ['bytea']) 986rv = plpy.execute(plan, [bb], 5) 987return rv[0]['val'] 988$$; 989SELECT test_prep_bytea_input(E'a\\000b'); -- 3 (embedded null formerly truncated value) 990 test_prep_bytea_input 991----------------------- 992 3 993(1 row) 994 995CREATE OR REPLACE FUNCTION test_prep_bytea_output() RETURNS bytea 996LANGUAGE plpython3u 997AS $$ 998plan = plpy.prepare("SELECT decode('aa00bb', 'hex') AS val") 999rv = plpy.execute(plan, [], 5) 1000plpy.info(rv[0]) 1001return rv[0]['val'] 1002$$; 1003SELECT test_prep_bytea_output(); 1004INFO: {'val': b'\xaa\x00\xbb'} 1005 test_prep_bytea_output 1006------------------------ 1007 \xaa00bb 1008(1 row) 1009 1010