--
-- 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, <class 'bool'>)
CONTEXT:  PL/Python function "test_type_conversion_bool"
 test_type_conversion_bool 
---------------------------
 t
(1 row)

SELECT * FROM test_type_conversion_bool(false);
INFO:  (False, <class 'bool'>)
CONTEXT:  PL/Python function "test_type_conversion_bool"
 test_type_conversion_bool 
---------------------------
 f
(1 row)

SELECT * FROM test_type_conversion_bool(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_bool"
 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)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 test_type_conversion_bool_other 
---------------------------------
 f
(1 row)

SELECT * FROM test_type_conversion_bool_other(1);
INFO:  (5, True)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 test_type_conversion_bool_other 
---------------------------------
 t
(1 row)

SELECT * FROM test_type_conversion_bool_other(2);
INFO:  ('', False)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 test_type_conversion_bool_other 
---------------------------------
 f
(1 row)

SELECT * FROM test_type_conversion_bool_other(3);
INFO:  ('fa', True)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 test_type_conversion_bool_other 
---------------------------------
 t
(1 row)

SELECT * FROM test_type_conversion_bool_other(4);
INFO:  ([], False)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 test_type_conversion_bool_other 
---------------------------------
 f
(1 row)

SELECT * FROM test_type_conversion_bool_other(5);
INFO:  ([0], True)
CONTEXT:  PL/Python function "test_type_conversion_bool_other"
 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', <class 'str'>)
CONTEXT:  PL/Python function "test_type_conversion_char"
 test_type_conversion_char 
---------------------------
 a
(1 row)

SELECT * FROM test_type_conversion_char(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_char"
 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, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int2"
 test_type_conversion_int2 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int2(-100::int2);
INFO:  (-100, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int2"
 test_type_conversion_int2 
---------------------------
                      -100
(1 row)

SELECT * FROM test_type_conversion_int2(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_int2"
 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, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int4"
 test_type_conversion_int4 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int4(-100);
INFO:  (-100, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int4"
 test_type_conversion_int4 
---------------------------
                      -100
(1 row)

SELECT * FROM test_type_conversion_int4(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_int4"
 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, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int8"
 test_type_conversion_int8 
---------------------------
                       100
(1 row)

SELECT * FROM test_type_conversion_int8(-100);
INFO:  (-100, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int8"
 test_type_conversion_int8 
---------------------------
                      -100
(1 row)

SELECT * FROM test_type_conversion_int8(5000000000);
INFO:  (5000000000, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_int8"
 test_type_conversion_int8 
---------------------------
                5000000000
(1 row)

SELECT * FROM test_type_conversion_int8(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_int8"
 test_type_conversion_int8 
---------------------------
                          
(1 row)

CREATE FUNCTION test_type_conversion_numeric(x numeric) RETURNS numeric AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;
/* The current implementation converts numeric to float. */
SELECT * FROM test_type_conversion_numeric(100);
INFO:  (100.0, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_numeric"
 test_type_conversion_numeric 
------------------------------
                        100.0
(1 row)

SELECT * FROM test_type_conversion_numeric(-100);
INFO:  (-100.0, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_numeric"
 test_type_conversion_numeric 
------------------------------
                       -100.0
(1 row)

SELECT * FROM test_type_conversion_numeric(5000000000.5);
INFO:  (5000000000.5, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_numeric"
 test_type_conversion_numeric 
------------------------------
                 5000000000.5
(1 row)

SELECT * FROM test_type_conversion_numeric(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_numeric"
 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, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float4"
 test_type_conversion_float4 
-----------------------------
                         100
(1 row)

SELECT * FROM test_type_conversion_float4(-100);
INFO:  (-100.0, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float4"
 test_type_conversion_float4 
-----------------------------
                        -100
(1 row)

SELECT * FROM test_type_conversion_float4(5000.5);
INFO:  (5000.5, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float4"
 test_type_conversion_float4 
-----------------------------
                      5000.5
(1 row)

SELECT * FROM test_type_conversion_float4(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_float4"
 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, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float8"
 test_type_conversion_float8 
-----------------------------
                         100
(1 row)

SELECT * FROM test_type_conversion_float8(-100);
INFO:  (-100.0, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float8"
 test_type_conversion_float8 
-----------------------------
                        -100
(1 row)

SELECT * FROM test_type_conversion_float8(5000000000.5);
INFO:  (5000000000.5, <class 'float'>)
CONTEXT:  PL/Python function "test_type_conversion_float8"
 test_type_conversion_float8 
-----------------------------
                5000000000.5
(1 row)

SELECT * FROM test_type_conversion_float8(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_float8"
 test_type_conversion_float8 
-----------------------------
                            
(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', <class 'str'>)
CONTEXT:  PL/Python function "test_type_conversion_text"
 test_type_conversion_text 
---------------------------
 hello world
(1 row)

SELECT * FROM test_type_conversion_text(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_text"
 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', <class 'bytes'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea"
 test_type_conversion_bytea 
----------------------------
 \x68656c6c6f20776f726c64
(1 row)

SELECT * FROM test_type_conversion_bytea(E'null\\000byte');
INFO:  (b'null\x00byte', <class 'bytes'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea"
 test_type_conversion_bytea 
----------------------------
 \x6e756c6c0062797465
(1 row)

SELECT * FROM test_type_conversion_bytea(null);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea"
 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, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_uint2"
 test_type_conversion_uint2 
----------------------------
                         50
(1 row)

SELECT * FROM test_type_conversion_uint2(100::uint2, -50);
INFO:  (100, <class 'int'>)
CONTEXT:  PL/Python function "test_type_conversion_uint2"
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, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_uint2"
 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', <class 'bytes'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea10"
 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', <class 'bytes'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea10"
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', <class 'bytes'>)
CONTEXT:  PL/Python function "test_type_conversion_bytea10"
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], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,100}
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]);
INFO:  ([0, -100, 55], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,-100,55}
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[NULL,1]);
INFO:  ([None, 1], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {NULL,1}
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[]::integer[]);
INFO:  ([], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {}
(1 row)

SELECT * FROM test_type_conversion_array_int4(NULL);
INFO:  (None, <class 'NoneType'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[[1,2,3],[4,5,6]]);
ERROR:  cannot convert multidimensional array to Python list
DETAIL:  PL/Python only supports one-dimensional arrays.
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
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'], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_text"
 test_type_conversion_array_text 
---------------------------------
 {foo,bar}
(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], <class 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_bytea"
 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 integer: "abc"
CONTEXT:  while creating return value
PL/Python function "test_type_conversion_array_mixed2"
CREATE FUNCTION test_type_conversion_array_record() RETURNS type_record[] AS $$
return [None]
$$ LANGUAGE plpython3u;
ERROR:  PL/Python functions cannot return type type_record[]
DETAIL:  PL/Python does not support conversion to arrays of row types.
SELECT * FROM test_type_conversion_array_record();
ERROR:  function test_type_conversion_array_record() does not exist
LINE 1: SELECT * FROM test_type_conversion_array_record();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
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"
--
-- 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}
CONTEXT:  PL/Python function "test_prep_bool_output"
 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'}
CONTEXT:  PL/Python function "test_prep_bytea_output"
 test_prep_bytea_output 
------------------------
 \xaa00bb
(1 row)

