Postgresql 中文操作指南

46.2. Data Values #

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

46.2.1. Data Type Mapping #

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

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

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

46.2.2. Null, None #

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

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 值。无论该函数是否严格，都可以执行此项操作。

46.2.3. Arrays, Lists #

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

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 数组时，每个级别的内部列表都必须具有相同大小。例如：

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 的版本时也可接受，当时不支持多维数组。但是，它们始终被视为一维数组，因为它们与复合类型混淆。出于同样的原因，在使用复合类型作为多维数组时，必须将其表示为元组，而不是列表。

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

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。这里有一个示例：

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 函数返回行或复合类型。以下示例假定我们有：

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

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

  • 序列类型（元组或列表，但不是集合因为它不可索引）

    • 所返回的序列对象必须与复合结果类型具有相同数量的项。索引为 0 的项将被赋值给复合类型的第一个域，1 将被赋值给第二个域，依此类推。例如：

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

  • 若要为任意列返回 SQL null，请在相应位置插入 None。

  • 当返回复合类型的数组时，它不能作为列表返回，因为无法明确 Python 列表表示的是复合类型还是另一个数组维度。

    • Mapping (dictionary)

  • 每一结果类型列的值都从以列名称作为键的映射中检索。示例：

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

  • 将忽略任何额外的字典键值对。将把缺少的键视为错误。若要为任意列返回 SQL null 值，请插入 None，并将其相应列名称用作键。

    • 对象（提供方法 getattr 的任何对象）

  • 其工作原理与映射相同。示例：

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 参数的函数。例如：

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

SELECT * FROM multiout_simple();

过程的输出参数以相同的方式传递回来。例如：

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

CREATE TYPE greeting AS (
  how text,
  who text
);

集合结果可以从以下内容返回：

  • 序列类型（元组、列表、集合）

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 （提供 iter 和 next 方法的任意对象）

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）的集合返回函数。例如：

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