Postgresql 中文操作指南

46.2. Data Values #

一般而言，PL/Python 是为了在 PostgreSQL 和 Python 世界之间提供“自然”映射。此项将告知以下所述的数据映射规则。

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.2.1. Data Type Mapping #

在调用 PL/Python 函数时，其参数将从 PostgreSQL 数据类型转换为相应的 Python 类型：

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

在 PL/Python 函数返回时，其返回值将转换为该函数声明的 PostgreSQL 返回数据类型，如下所示：

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

请注意，不会标记声明的 PostgreSQL 返回类型与实际返回对象的 Python 数据类型之间的逻辑不匹配；在任何情况下都将转换此值。

Note that logical mismatches between the declared PostgreSQL return type and the Python data type of the actual return object are not flagged; the value will be converted in any case.

46.2.2. Null, None #

如果向函数传递 SQL NULL 值，参数值在 Python 中将显示为 None。例如， Section 46.1中显示的 _pymax_的函数定义将返回 NULL 输入的错误答案。我们可以将 _STRICT_添加到函数定义中，以让 PostgreSQL 执行更合理的操作：如果传递了 NULL 值，则根本不会调用函数，而只会自动返回 NULL 结果。或者，我们可以在函数主体中检查 NULL 输入：

If an SQL null value is passed to a function, the argument value will appear as None in Python. For example, the function definition of pymax shown in Section 46.1 will return the wrong answer for null inputs. We could add STRICT to the function definition to make PostgreSQL do something more reasonable: if a null value is passed, the function will not be called at all, but will just return a null result automatically. Alternatively, we could check for null inputs in the function body:

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

如上所示，若要从 PL/Python 函数返回 SQL null 值，请返回 None 值。无论该函数是否严格，都可以执行此项操作。

As shown above, to return an SQL null value from a PL/Python function, return the value None. This can be done whether the function is strict or not.

46.2.3. Arrays, Lists #

SQL 数组值将作为 Python 列表传递到 PL/Python 中。若要从 PL/Python 函数中返回 SQL 数组值，请返回一个 Python 列表：

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 plpython3u;

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

多维数组将作为嵌套 Python 列表传递到 PL/Python 中。例如，二维数组是列表列表。在从 PL/Python 函数中返回多维 SQL 数组时，每个级别的内部列表都必须具有相同大小。例如：

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 $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpython3u;

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

其他 Python 序列，如元组，在向后兼容低于 PostgreSQL 9.6 的版本时也可接受，当时不支持多维数组。但是，它们始终被视为一维数组，因为它们与复合类型混淆。出于同样的原因，在使用复合类型作为多维数组时，必须将其表示为元组，而不是列表。

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.

请注意，在 Python 中，字符串是序列，这可能对 Python 程序员有不良的影响：

Note that in Python, strings are sequences, which can have undesirable effects that might be familiar to Python programmers:

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)

46.2.4. Composite Types #

复合类型参数将作为 Python 映射传递到该函数。映射的元素名称是复合类型的属性名称。如果传递的行中的某个属性具有 null 值，则它在映射中的值将为 None。这里有一个示例：

Composite-type arguments are passed to the function as Python mappings. The element names of the mapping are the attribute names of the composite type. If an attribute in the passed row has the null value, it has the value None in the mapping. Here is an example:

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

CREATE FUNCTION overpaid (e employee)
  RETURNS boolean
AS $$
  if e["salary"] > 200000:
    return True
  if (e["age"] < 30) and (e["salary"] > 100000):
    return True
  return False
$$ LANGUAGE plpython3u;

有多种方法可以从 Python 函数返回行或复合类型。以下示例假定我们有：

There are multiple ways to return row or composite types from a Python function. The following examples assume we have:

CREATE TYPE named_value AS (
  name   text,
  value  integer
);

可以按下面方式返回复合结果：

A composite result can be returned as a:

Sequence type (a tuple or list, but not a set because it is not indexable)
- 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. For example:

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

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.
- Mapping (dictionary)
The value for each result type column is retrieved from the mapping with the column name as key. Example:

CREATE FUNCTION make_pair (name text, value integer)
  RETURNS named_value
AS $$
  return { "name": name, "value": value }
$$ LANGUAGE plpython3u;

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.
- 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):
      self.name = n
      self.value = v
  return named_value(name, value)

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

支持具有 OUT 参数的函数。例如：

Functions with OUT parameters are also supported. For example:

CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
return (1, 2)
$$ LANGUAGE plpython3u;

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 plpython3u;

CALL python_triple(5, 10);

46.2.5. Set-Returning Functions #

PL/Python 函数还可以返回标量或复合类型的集合。有多种方法可以实现这一点，因为返回的对象在内部变为一个迭代器。以下示例假设我们具有复合类型：

A PL/Python function can also return sets of scalar or composite types. There are several ways to achieve this because the returned object is internally turned into an iterator. The following examples assume we have composite type:

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
  return ( [ how, "World" ], [ how, "PostgreSQL" ], [ how, "PL/Python" ] )
$$ LANGUAGE plpython3u;

Iterator (any object providing iter and next methods)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  class producer:
    def __init__ (self, how, who):
      self.how = 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.how, self.who[self.ndx] )

  return producer(how, [ "World", "PostgreSQL", "PL/Python" ])
$$ LANGUAGE plpython3u;

Generator (yield)

CREATE FUNCTION greet (how text)
  RETURNS SETOF greeting
AS $$
  for who in [ "World", "PostgreSQL", "PL/Python" ]:
    yield ( how, who )
$$ LANGUAGE plpython3u;

还支持具有 OUT 参数（使用 RETURNS SETOF record）的集合返回函数。例如：

Set-returning functions with OUT parameters (using RETURNS SETOF record) are also supported. For example:

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(3);