Postgresql 中文操作指南

46.1. PL/Python Functions #

PL/Python 中的函数通过标准 CREATE FUNCTION 语法声明：

Functions in PL/Python are declared via the standard CREATE FUNCTION syntax:

CREATE FUNCTION funcname (argument-list)
  RETURNS return-type
AS $$
  # PL/Python function body
$$ LANGUAGE plpython3u;

函数的主体就是一个 Python 脚本。当调用该函数时，其参数作为列表 args 的元素传递；命名参数也作为普通变量传递至 Python 脚本。通常来说，使用命名参数可读性更高。结果会以通常的方式（在结果集语句中使用 return 或 yield）从 Python 代码返回。如果不提供返回值，Python 会返回默认值 None。PL/Python 将 Python 的 None 转换为 SQL null 值。在过程中，Python 代码中的结果必须为 None（通常通过在没有 return 语句的情况下结束过程，或在没有参数的情况下使用 return 语句来实现）；否则，将出现错误。

The body of a function is simply a Python script. When the function is called, its arguments are passed as elements of the list args; named arguments are also passed as ordinary variables to the Python script. Use of named arguments is usually more readable. The result is returned from the Python code in the usual way, with return or yield (in case of a result-set statement). If you do not provide a return value, Python returns the default None. PL/Python translates Python’s None into the SQL null value. In a procedure, the result from the Python code must be None (typically achieved by ending the procedure without a return statement or by using a return statement without argument); otherwise, an error will be raised.

例如，可以将某个函数定义为返回两个整数中较大的一个：

For example, a function to return the greater of two integers can be defined as:

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

作为函数定义主体所给出的 Python 代码将转换为一个 Python 函数。例如，以上内容将得到以下结果：

The Python code that is given as the body of the function definition is transformed into a Python function. For example, the above results in:

def __plpython_procedure_pymax_23456():
  if a > b:
    return a
  return b

假定 23456 是 PostgreSQL 分配给该函数的 OID。

assuming that 23456 is the OID assigned to the function by PostgreSQL.

这些参数将被设置成全局变量。由于 Python 的范围限定规则，这是一个微妙的后果，即参数变量不能在函数内部重新分配为涉及变量名称本身的表达式的值，除非重新声明该变量为该块中的全局变量。例如，以下操作不可行：

The arguments are set as global variables. Because of the scoping rules of Python, this has the subtle consequence that an argument variable cannot be reassigned inside the function to the value of an expression that involves the variable name itself, unless the variable is redeclared as global in the block. For example, the following won’t work:

CREATE FUNCTION pystrip(x text)
  RETURNS text
AS $$
  x = x.strip()  # error
  return x
$$ LANGUAGE plpython3u;

这是因为对 x 进行分配，使得 x 为整个块的局部变量，因此，该分配右端的 x 指的是一个尚未分配的局部变量 x，而不是 PL/Python 函数参数。可以使用 global 语句来对此进行处理：

because assigning to x makes x a local variable for the entire block, and so the x on the right-hand side of the assignment refers to a not-yet-assigned local variable x, not the PL/Python function parameter. Using the global statement, this can be made to work:

CREATE FUNCTION pystrip(x text)
  RETURNS text
AS $$
  global x
  x = x.strip()  # ok now
  return x
$$ LANGUAGE plpython3u;

但建议不要依赖于 PL/Python 的此项实现细节。最好将函数参数视为只读。

But it is advisable not to rely on this implementation detail of PL/Python. It is better to treat the function parameters as read-only.