Data Types in PL Python

Arrays, Lists

Single Dimension

SQL array values are passed into PL/Python as a Python list. To return an SQL array value out of a PL/Python function, return a Python list.

CREATE FUNCTION return_arr()
  RETURNS int[]
AS $$
return [1, 2, 3, 4, 5]
$$ LANGUAGE plpythonu;

SELECT return_arr();
 return_arr  
-------------
 {1,2,3,4,5}
(1 row)

Multiple Dimension

Multidimensional arrays are passed into PL/Python as nested Python lists. A 2-dimensional array is a list of lists. When returning a multi-dimensional SQL array out of a PL/Python function, the inner lists at each level must all be of the same size.

CREATE FUNCTION 2d_array(x int[]) RETURNS int[] AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;

SELECT * FROM 2d_array(ARRAY[[1,2,3],[4,5,6]]);
INFO:  ([[1, 2, 3], [4, 5, 6]], <type 'list'>)
 2d_array 
---------------------------------
 {{1,2,3},{4,5,6}}
(1 row)

Important Note:

Python sequences like tuples are also accepted as PostgreSQl Arrays. However, they are not always treated as one-dimensional arrays, because they are ambiguous with composite types. For the same reason, when a composite type is used in a multi-dimensional array, it must be represented by a tuple, rather than a list.

Strings, Text

strings are sequences in python
Strings

CREATE FUNCTION return_str_arr()
  RETURNS varchar[]
AS $$
return "hello"
$$ LANGUAGE plpython3u;

SELECT return_str_arr();
 return_str_arr
----------------
 {h,e,l,l,o}
(1 row)

Text

CREATE FUNCTION return_str()
  RETURNS text
AS $$
    return "hello"
$$ LANGUAGE plpython3u;
SELECT * from return_str();
 return_str 
------------
 hello
(1 row)

Composite Type