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

Type

CREATE TYPE employee AS (
  name   text,
  salary  integer
);

Single row of composite type

Example 1 : Sequence

CREATE FUNCTION make_pair (person employee)
  RETURNS named_value
AS $$
  return (person)
  # or alternatively, as list: return [ name, value ]
$$ LANGUAGE plpython3u;
SELECT * FROM make_pair(('john', 100));
 name  | salary 
-------+--------
 john |    100
(1 row)

• Returned sequence objects must have the same number of items as the composite result type has fields. The item with index 0 is assigned to the first field of the composite type, 1 to the second and so on.
• To return an SQL null for any column, insert None at the corresponding position.
• When an array of composite types is returned, it cannot be returned as a list, because it is ambiguous whether the Python list represents a composite type, or another array dimension.

Example 2 : Dictionary

CREATE FUNCTION make_pair (name text, salary integer)
  RETURNS employee
AS $$
  return { "name": name, "salary": value }
$$ LANGUAGE plpython3u;
SELECT * FROM make_pair('john', 100);
 name | value 
------+-------
 john |   100
(1 row)

Any extra dictionary key/value pairs are ignored. Missing keys are treated as errors. To return an SQL null value for any column, insert None with the corresponding column name as the key.

Example 3 : Object

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  class named_value:
    def __init__ (self, n, v):
      self.name = n
      self.value = v
  return named_value(name, value)
$$ LANGUAGE plpython3u;
SELECT * FROM make_pair('john', 100);
 name | value 
------+-------
 john |   100
(1 row)

Multiple Rows of composite type

Example 1 : Sequence

CREATE FUNCTION make_pair (person employee)
  RETURNS SETOF employee
AS $$
  return person, person
$$ LANGUAGE plpython3u;
SELECT * FROM make_pair(('john', 100));
 name  | salary 
-------+--------
 john |    100
 john |    100
(2 row)

Example 2 : Sequence

CREATE TYPE greet AS (
  how text,
  who text
);
CREATE FUNCTION greeter (how text)
  RETURNS SETOF greeting
AS $$
  return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
$$ LANGUAGE plpython3u;
SELECT * FROM greet_separate_cols('hello');
  how  |    who     
-------+------------
 hello | World
 hello | PostgreSQL
 hello | PL/Python
(3 rows)

Example 3 : Generator (yield)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  for who in [ "World", "PostgreSQL", "PL/Python" ]:
    yield ( how, who )
$$ LANGUAGE plpython3u;
SELECT * FROM greet('welcome');
   how   |    who     
---------+------------
 welcome | World
 welcome | PostgreSQL
 welcome | PL/Python
(3 rows)

Example 4

CREATE FUNCTION multiout_simple_setof(n integer, OUT integer, OUT integer) 
  RETURNS SETOF record
AS $$
  return [(1, 2)] * n
$$ LANGUAGE plpython3u;
 SELECT * FROM multiout_simple_setof(5);
 column1 | column2 
---------+---------
       1 |       2
       1 |       2
       1 |       2
       1 |       2
       1 |       2
(5 rows)

For more details refer to Data Types