7.0版PL/Python函数

注意事项

• PL/Python默认支持plpython3u，本文将围绕plpython3u展开举例说明。

• 由于plpython3u被视为“不可信”语言，因此不建议您使用plpython3u执行任何高风险操作。云原生数据仓库 AnalyticDB PostgreSQL 版对此特性做了限制，如需使用plpython3u语言，请提交工单。

• 不支持PL/Python触发器。

• 不支持可更新游标（UPDATE ... WHERE CURRENT OF和DELETE ... WHERE CURRENT OF）。

版本限制

AnalyticDB for PostgreSQL7.0版实例支持PL/Python。

语法

您需使用标准的CREATE FUNCTION语法声明创建自定义函数，更多详情请参见CREATE FUNCTION。

CREATE FUNCTION function_name ()
  RETURNS return_type
AS $$
  # PL/Python function_body
$$ LANGUAGE plpython3u STRICT;

参数说明

• function_name：必选参数，自定义函数的名称。

• return_type：必选参数，函数的返回类型。

• body：必选参数，函数的主体。调用该函数时，其参数将作为数组args[]的元素传递，命名参数也作为普通变量传递给Python脚本，结果返回return语句中声明的返回类型。

• STRICT： 可选参数，当函数声明为STRICT时，如果输入参数为空，则自动返回空，而不是执行body中的代码，可减少不必要的计算，同时让函数代码更简洁。

  下面两个示例的作用相同，均为：返回两个值中的较大值，如果任意一个值为空，则返回空。但左侧示例（声明了STRICT）代码更简洁。

  CREATE FUNCTION py_int_max (a integer, b integer) RETURNS integer AS $$ RETURN MAX(a,b) $$ LANGUAGE plpython3u STRICT;

  CREATE FUNCTION py_int_max (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;

数据映射

基础数据类型

云原生数据仓库 AnalyticDB PostgreSQL 版与Python数据类型的对应关系如下表，更多内容请参见数据类型。

数组和列表

在plpython3u函数中，SQL数组会自动转换为Python列表。同样地，函数返回的Python列表会自动转换为SQL数组。

一维数组

在plpython3u经典使用模式中，您可以使用[]指定一维数组。

以下示例将构建一个返回整数一维数组的plpython3u函数，并调用该函数查看返回结果。

CREATE FUNCTION create_py_int_array()
  RETURNS int[]
AS $$
  RETURN [1, 2, 3, 4]
$$ LANGUAGE plpython3u;

调用该函数。

SELECT create_py_int_array();

结果如下。

create_py_int_array
---------------------
{1,2,3,4}
(1 row) 

多维数组

plpython3u将多维数组视为列表的列表。使用嵌套的Python列表将多维数组传递给plpython3u函数。当plpython3u函数返回一个多维数组时，每个层级的列表必须都具有相同的维度。

以下示例将构建一个plpython3u函数。该函数将多维数组的整数作为输入，并返回多维数组。

CREATE FUNCTION create_multidim_py_array(x int4[]) 
  RETURNS int4[]
AS $$
  plpy.info(x, type(x))
  RETURN x
$$ LANGUAGE plpython3u;

调用该函数。

SELECT * FROM create_multidim_py_array(ARRAY[[1,2,3], [4,5,6]]);

结果如下。

 create_multidim_py_array 
--------------------------
 {{1,2,3},{4,5,6}}
(1 row) 

复合类型

您可通过Python映射将复合类型参数传递给plpython3u函数，映射参数名称为复合类型的属性名称。如果属性为空，则其映射值为None。复合类型结果可以作为序列类型（元组或列表）返回。当在多维数组中使用复合类型时，必须将复合类型指定为元组，而不是列表。不能将复合类型数组作为列表返回，因为这样会导致不明确的数据类型。

以下示例将构建一个复合类型和一个返回复合类型数组的plpython3u函数。

CREATE TYPE type_record AS (
  FIRST text,
  SECOND int4
);

CREATE FUNCTION composite_type_as_list()
  RETURNS type_record[]
AS $$              
  RETURN [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)]];
$$ LANGUAGE plpython3u;

调用该函数。

SELECT * FROM composite_type_as_list();

结果如下。

                               composite_type_as_list                           
------------------------------------------------------------------------------------
 {{"(first,1)","(second,1)"},{"(first,2)","(second,2)"}}
(1 row) 

更多plpython3u处理数组和复合类型详情，请参见数组列表。

返回集合的函数

plpython3u函数可以根据任何序列类型（元组、列表、集合），返回标量或复合类型的集合。

以下示例将构建一个复合类型并构建函数返回复合类型的集合。

-- 复合类型
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": how, "who": "World"}, {"how": how, "who": "ADB PG"} )
$$ LANGUAGE plpython3u;

调用该函数。

SELECT greet('hello');

结果如下。

       greet
-------------------
 (hello,World)
 (hello,ADB PG)
(2 rows)

准备和执行SQL查询

在plpython3u中，plpy模块提供了两个Python函数plpy.execute和plpy.prepare来执行SQL查询。此外，plpython3u还支持plpy.subtransaction函数，以帮助在显式子事务中管理plpy.execute调用。更多plpy.subtransaction信息，请参见显式子事务。

plpy.execute

调用plpy.execute执行SQL查询。plpy.execute接受两个参数：一个是SQL语句（必选参数），另一个是返回的最大行数（可选参数）。plpy.execute返回Python结果对象。这个结果对象可以像列表或字典一样访问。您可以通过行号和列名来访问结果对象中的行，其中行编号从0开始计数。

例如，调用plpy.execute函数查询my_table表，且最多返回5行数据，返回结果存储在rv对象中。

rv = plpy.execute("SELECT * FROM my_table", 5)。

假设my_table中存在列my_column，通过列名访问结果对象中的行：my_col_data = rv[i]["my_column"]。

结果对象提供nrows()、status()等附加方法，更多详情请参见附加方法。

plpy.prepare

plpy.prepare用于准备查询的执行计划。调用plpy.prepare时，需要传入SQL查询字符串。如果SQL查询字符串中包含参数引用，则还需要在列表中指定这些参数的类型。

以下示例将创建查询计划plan。

# 字符串text是为变量$1传递的数据类型。
plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", ["text"]) 

调用plpy.execute函数执行查询执行计划。

rv = plpy.execute(plan, [ "Fred" ])

DO匿名代码块

以下示例中使用DO命令来直接执行匿名代码块。

CREATE TEMP TABLE temp_tbl AS VALUES (2) DISTRIBUTED RANDOMLY;
DO $$
  row = plpy.execute("SELECT * FROM temp_tbl", 1)
  attr = row[0]["column1"]
  plpy.notice("attr is %s" % attr)
$$ LANGUAGE plpython3u;

Python错误和消息处理

Python模块plpy提供plpy.debug、plpy.log、plpy.info、plpy.notice、plpy.warning、plpy.error和plpy.fatal函数来管理错误和消息。

plpy.error和plpy.fatal会抛出Python异常。如果异常未被捕获，则会传播到调用查询，导致当前事务或子事务中止。raise plpy.error(msg)和raise plpy.fatal(msg)分别等同于plpy.error和plpy.fatal。其他消息函数仅生成不同优先级的消息。

Python依赖包管理

Python依赖包存储在协调节点以及计算节点的本地云盘中。如需安装阿里云镜像源中的其他依赖包，可提交工单。