
Development Versions: devel
Unsupported versions: 9.3 / 9.2 / 9.1 / 9.0

46.3. Data Values

Generally speaking, the aim of PL/Python is to provide a natural mapping between the PostgreSQL and the Python worlds. This informs the data mapping rules described below.

46.3.1. Data Type Mapping

When a PL/Python function is called, its arguments are converted from their PostgreSQL data type to a corresponding Python type:

  • PostgreSQL boolean is converted to Python bool.


  • PostgreSQL real and double are converted to Python float.

  • PostgreSQL numeric is converted to Python Decimal. This type is imported from the cdecimal package if that is available. Otherwise, decimal.Decimal from the standard library will be used. cdecimal is significantly faster than decimal. In Python 3.3 and up, however, cdecimal has been integrated into the standard library under the name decimal, so there is no longer any difference.



  • For nonscalar data types, see below.

When a PL/Python function returns, its return value is converted to the function's declared PostgreSQL return data type as follows:



  • For all other PostgreSQL return types, the return value is converted to a string using the Python built-in str, and the result is passed to the input function of the PostgreSQL data type. (If the Python value is a float, it is converted using the repr built-in instead of str, to avoid loss of precision.)


  • For nonscalar data types, see below.


46.3.2. Null, None


CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
    return None
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;


46.3.3. Arrays, Lists


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

SELECT return_arr();
(1 row)

Multidimensional arrays are passed into PL/Python as nested Python lists. A 2-dimensional array is a list of lists, for example. 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. For example:

CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$, type(x))
return x
$$ LANGUAGE plpythonu;

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

Other Python sequences, like tuples, are also accepted for backwards-compatibility with PostgreSQL versions 9.6 and below, when multi-dimensional arrays were not supported. However, they are 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.


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

SELECT return_str_arr();
(1 row)

46.3.4. Composite Types


CREATE TABLE employee (
  name text,
  salary integer,
  age integer

CREATE FUNCTION overpaid (e employee)
  RETURNS boolean
AS $$
  if e["salary"] > 200000:
    return True
    return True
  return False
$$ LANGUAGE plpythonu;


CREATE TYPE named_value AS (
  name   text,
  value  integer

A composite result can be returned as a:



CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  return ( name, value )
  # or alternatively, as tuple: return [ name, value ]
$$ LANGUAGE plpythonu;


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.

Mapping (dictionary)


CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
$$ LANGUAGE plpythonu;


Object (any object providing method __getattr__)

This works the same as a mapping. Example:

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  class named_value:
    def __init__ (self, n, v): = n
      self.value = v
  return named_value(name, value)

  # or simply
  class nv: pass = name
  nv.value = value
  return nv
$$ LANGUAGE plpythonu;

Functions with OUT parameters are also supported. For example:

return (1, 2)
$$ LANGUAGE plpythonu;

SELECT * FROM multiout_simple();

Output parameters of procedures are passed back the same way. For example:

CREATE PROCEDURE python_triple(INOUT a integer, INOUT b integer) AS $$
return (a * 3, b * 3)
$$ LANGUAGE plpythonu;

CALL python_triple(5, 10);

46.3.5. Set-returning Functions


CREATE TYPE greeting AS (
  how text,
  who text

A set result can be returned from a:

Sequence type (tuple, list, set)
CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  # return tuple containing lists as composite types
  # all other combinations work also
$$ LANGUAGE plpythonu;
Iterator (any object providing __iter__ and next methods)
CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  class producer:
    def __init__ (self, how, who): = how
      self.who = who
      self.ndx = -1

    def __iter__ (self):
      return self

    def next (self):
      self.ndx += 1
      if self.ndx == len(self.who):
        raise StopIteration
      return (, self.who[self.ndx] )

$$ LANGUAGE plpythonu;
Generator (yield)
CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
    yield ( how, who )
$$ LANGUAGE plpythonu;


return [(1, 2)] * n
$$ LANGUAGE plpythonu;

SELECT * FROM multiout_simple_setof(3);

Submit correction
