Reference：PL/Python#

PL/Python是一个可装卸的过程化语言。使用PL/Python，我们可以创建UDF和存储过程，这样我们可以充分利用Python的优势，以便于快速建立稳健的数据库和应用程序。我们也可以在匿名代码块中使用PL/Python代码块。稍后阐述用法。

PL/Python 安装#

梧桐数据库安装过程中是默认安装PL/Python插件的。plpython3u是专门用于支持Python 3.x版本的一个PL/Python版本。我们的数据库在安装过程中会在我们的系统上安装一个Python 3.9环境，作为它的依赖项之一，所以不需要单独安装Python 3.9。

PL/Python语言被安装在数据库中。我们需要再数据库中注册PL/Python之后，才能在数据库中创建和执行PL/Python UDF。如果某个database需要使用 PL/Python，则我们需要在其中使用CREATE EXTENSION命令。因为它是untrusted language，所以只能使用超级用户来注册进当前的database。例如我们作为超级用户用户，可以使用如下命令来注册和关闭Python3.9的PL/Python功能。

CREATE EXTENSION plpython3u;
DROP EXTENSION plpython3u;

如果存在任意数据库对象依赖PL/Python，则这条DROP命令默认执行失败。此时可以使用CASCADE选项来删除之，请注意增加该选项，会同时删除了我们用PL/Python创建的任何数据库对象。

使用PL/Python开发UDF#

入参和返回值说明#

PL/Python UDF函数体是一个Python脚本。当函数被调用时，其参数作为args[]数组的元素被调用。命名参数也作为Python脚本的普通变量。结果是从PL/Python函数返回的，带有return语句，或者在结果集语句的情况下使用yield语句。如果您不提供返回值，Python返回默认值None。 PL/Python将Python的None转换为SQL的null值。

数据库与Python数据类型映射关系

primitive type	Python data type
boolean	bool
bytea	bytes
smallint,bigint,oid	int
real,double	float
numberic	decimal
other primitive types	string
sql null value	None

Notes：当UDF返回类型为布尔值时，梧桐数据库将根据Python规则计算返回值。即0和空字符串是false，但值得注意的是“f”是true。举例：

CREATE OR REPLACE FUNCTION pybool_func(a int) RETURNS boolean AS $$ 
if (a > 0): 
return True 
else: 
return False 
$$ LANGUAGE plpython3u; 
SELECT pybool_func(-1); 
pybool_func 
------------- 
f 
(1 row)

支持Arrays和Lists（数组和列表）#

可以使用Python list将SQL Arrays值传递到PL/Python函数中。类似地，PL/Python函数以Python list的形式返回SQL数组值。在典型的PL/Python使用模式中，我们可以使用中括号[ ]来指定一个数组。下面的例子创建了一个PL/Python函数，它返回了一个整型数组。

CREATE FUNCTION return_py_int_array() 
RETURNS int[] 
AS $$ 
return [1, 11, 21, 31] 
$$ LANGUAGE plpython3u; 

SELECT return_py_int_array(); 
return_py_int_array 
--------------------- 
{1,11,21,31} 
(1 row)

PL/Python将多维数组视为list的list。将多维数组传递给使用嵌套Python list的PL/Python函数。当PL/Python函数返回多维数组时，每个级别的内部list都必须具有相同的大小。以下示例创建了一个PL/Python函数，该函数采用的多维整数数组作为输入。函数显示所提供参数的类型，并返回多维数组。

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

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

SELECT * FROM return_multidim_py_array(ARRAY[[1,2,3], [4,5,6,7]]);
ERROR:  multidimensional arrays must have array expressions with matching dimensions

Notes：为了向后兼容不支持多维数组支持的版本，PL/Python还接受如元组（tuple）这样的其他Python序列作为的函数参数。在这种情况下，Python序列总是被视为一维数组，因为它们与复合类型具有二义性。

支持复合类型composite type#

可以使用Python mappings把复合类型的参数传递给PL/Python函数。mapping元素的名称即是复合类型的属性名称。如果属性具有null值，其映射值为None。您可以将复合类型结果作为序列类型（元组或列表）返回。当复合类型被用做多维数组时，须指定复合类型为tuple，而不是list。你无法将复合类型的数组作为list返回，因为无法确定该list是一个复合类型还是另一个数组维度。一般使用（）指定复合类型元组。

CREATE TYPE type_record AS ( 
first text, 
second int4 
); 
CREATE FUNCTION composite_type_as_list() 
RETURNS type_record[] 
AS $$ 
return [[('wutong_1', 1), ('wutong_2', 1)], [('wutong_1', 2), ('wutong_2', 2)], [('wutong_1', 3), 
('wutong', 3)]]; 
$$ LANGUAGE plpython3u; 

db=# SELECT * FROM composite_type_as_list();
                                     composite_type_as_list                                 
-------------------------------------------------------------------------------------------------
 {{"(wutong_1,1)","(wutong_2,1)"},{"(wutong_1,2)","(wutong_2,2)"},{"(wutong_1,3)","(wutong,3)"}}
(1 row)

支持Set-Returning Functions#

可以使用SETOF,从任意sequence type（tuple, list, set）返回一个scalar或者composite type的集合。

CREATE TYPE greeting AS ( 
how text, 
who text 
); 
CREATE FUNCTION greet (my_how text) 
RETURNS SETOF greeting 
AS $$ 
-- return tuple containing lists as composite types 
-- all other combinations work also 
return ( {"how": my_how, "who": "World"}, {"how": my_how, "who": "wutong"} ) 
$$ LANGUAGE plpython3u; 
select greet('hello'); 
greet 
------------------- 
(hello,World) 
(hello,wutong) 
(2 rows) 

Running和Preparing SQL查询#

PL/PYTHON组件提供了两种Python函数，分别用于运行一条SQL查询以及为一个查询准备一个执行计划。他们是plpy.execute和plpy.prepare。执行计划的准备对于一个查询来说是非常有用的，尤其当我们在多个Python函数中运行同一个查询的时候。 PL/Python还支持plpy.subtransaction（）函数来在一个显示的subtransaction中帮助管理plpy.execute调用。请参阅PostgreSQL文档中的Explicit Subtransactions以了解关于plpy.subtransaction（）的其他信息。此处不再详述。

plpy.execute#

调用plpy.execute，并把查询字符串和可选的限制参数作为入参，来运行一个Query，其结果被保存在一个Python list对象或者中dictionary对象。结果对象中的行可以通过行号和列名来访问。结果集的行编号以0开头。可以修改结果对象。结果对象具有以下额外的方法：（1）nrows 返回结果行数（2）status 是 SPI_execute() 的返回值例如，下面这个Python语句在PL/Python UDF中运行了一条查询。

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

这个plpy.execute函数最多从my_table返回5行，结果集存在rv对象中。如果这个表有my_column列，可以这么写：

my_col_data = rv[i]["my_column"]

由于函数最多返回5行，因此索引i可以是0到4之间的整数。

plpy.prepare#

函数plpy.prepare为查询准备执行计划。它的参数是Query字符串以及参数类型列表（如果Query中有参数引用）。例如，这个语句可以在PL/Python用户定义函数中这样使用：

plan = plpy.prepare("SELECT last_name FROM my_users WHERE first_name = $1", [ "text" ]) 

字符串文本是为变量$1传递的变量的数据类型。之后，您可以使用函数plpy.execute来运行它：

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

第三个参数是返回行数的限制，是可选的。为了在函数调用之间有效地使用保存计划，您可以使用Python持久化存储字典SD或GD。全局字典SD可用于存储函数调用之间的数据。此变量是私有的静态数据。全局字典GD是公共数据，可用于会话中的所有Python函数（需小心使用GD）。每个函数在Python解释器中都有自己的执行环境，因此全局数据和myfunc中的函数参数对myfunc2不可用。GD字典中的数据除外，如前所述。如下示例使用SD字典：

CREATE FUNCTION usesavedplan() RETURNS int AS $$ 
if "plan" in SD: 
plan = SD["plan"] 
else:
plan = plpy.prepare("SELECT 1")
SD["plan"] = plan
return 1
$$ LANGUAGE plpython3u;

处理Phthon错误和信息#

Python模块plpy实现了以下功能来管理错误和消息：

plpy.debug 
plpy.log 
plpy.info 
plpy.notice 
plpy.warning 
plpy.error 
plpy.fatal 
plpy.debug 

消息函数plpy.error和plpy.fatal引发一个Python异常，如果未捕获，则回传到查询层面，会导致当前事务或子事务取消。函数raise plpy.ERROR(msg) and raise plpy.FATAL(msg)相当于分别调用plpy.error和plpy.fatal。其他消息函数仅生成不同优先级的消息。是否将具有特定优先级的消息报告给客户端、写入服务器日志，或者两者都由数据库服务器配置参数log_min_messages控制和client_min_messages。此处不再赘述。

使用GD字典来提升PL/Python性能#

引入Python模块是一项代价较大的操作，可能会影响性能。如果经常导入同一个模块，可以使用Python全局变量在第一次调用时加载模块，而不需要在后续调用时导入相同的模块。下面的PL/Python函数使用GD持久化存储字典来避免导入同一模块。

CREATE FUNCTION pytest() returns text as $$ 
if 'mymodule' not in GD: 
import mymodule 
GD['mymodule'] = mymodule 
return GD['mymodule'].sumd([1,2,3]) 
$$ LANGUAGE plpython3u; 

使用PL/Python 开发存储过程#

PL/Python过程类似于PL/Pythn函数。请参阅用户定义程序有关梧桐数据库中的存储过程以及它们与函数的区别的更多信息。在PL/Python过程中，Python代码的结果必须为None（通常通过在不使用return语句的情况下结束过程，或者使用不带参数的return语句），否则将引发错误。您可以与传递函数的出参相同的方式来传递一个存储过程的出参：

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

CALL python_triple(5, 10);
 a  | b  
----+----
 15 | 30
(1 row)

在从顶级调用的过程中，或从调用的匿名代码块（DO命令）中最高级别可以控制事务。要提交当前事务请调用plpy.commit（）。要回滚当前事务请调用plpy.rollback（）。（请注意，不可以通过plpy.execute（）或者类似的函数来运行SQL命令COMMIT或ROLLBACK。你必须使用这些函数进行提交或回滚）。事务结束后，将创建一个新事务自动启动，因此没有额外单独的功能。

CREATE PROCEDURE transaction_test1() 
LANGUAGE plpython3u 
AS $$ 
for i in range(0, 10): 
plpy.execute("INSERT INTO test1 (a) VALUES (%d)" % i) 
if i % 2 == 0: 
plpy.commit() 
else: 
plpy.rollback() 
$$; 
CALL transaction_test1(); 

当显式子事务处于活动状态时，事务无法结束。

安装Python组件#

当在梧桐数据库中为PL/Python开发安装Python模块时，需要确保该模块被添加到集群中所有段主机（segment hosts）和镜像主机（mirror hosts）的梧桐Python环境中。当扩展数据库（增加新的段主机）时，您需要确保这些新主机上的Python环境也包含了必要的Python模块，以便能够支持通过PL/Python编写的函数。梧桐数据库提供了一组与数据科学相关的Python模块，您可以轻松开发PL/Python函数。这些模块以两个.gppkg格式的文件提供的，这些文件可以被安装到集群中，用以支持Python 3.9的开发。请参阅Python数据科学模块包所提供模块的安装说明和说明。使用额外的Python模块的功能，需要额外的安装步骤，此处不再详述。

PL/Python使用限制#

不支持PL/Python triggers。不允许使用 PL/Python 编写触发器。触发器是在满足特定条件（如插入、更新或删除数据）时自动执行的数据库对象。由于 PL/Python 的执行环境可能带来安全风险，我们的数据库限制了其在这方面的使用。
PL/Python只能作为untrusted language使用。在梧桐数据库中，PL/Python 被视为一个不受信任的语言。这意味着使用 PL/Python 编写的函数或过程在执行时可能会受到额外的限制或监视，以确保数据库的安全性和稳定性。
不支持可更新的游标（如 UPDATE … WHERE CURRENT OF 和 DELETE … WHERE CURRENT OF）。

plpython3u较于旧版的变化#

create language plpythonu改成create language plpython3u
除法运算符(/)执行的是真除法,即使两个操作数都是整数，结果也会是浮点数。
except Exception as e: 代替了except Exception , e:
print(“Hello, World!”) 作为一个函数代替了语句print “Hello, World!”
无法再将table数组作为出入参，此种情况可以用type数组代替。例如

CREATE TYPE type_record AS (first text, second int4 ) ;
CREATE OR REPLACE FUNCTION test_type(a int) RETURNS 
 type_record[]
 AS 
$$
    return  [('first', a), ('second', a)]
$$
LANGUAGE plpython3u;

select test_type(9);
         test_type          
----------------------------
 {"(first,9)","(second,9)"}
(1 row)

plpython3u的UDF定义中不支持直接嵌套其他UDF，但是可以通过其他方式实现类似的功能。例如

CREATE OR REPLACE FUNCTION test_division(a int, b int) RETURNS float AS 
$$
try:  
    plpy.notice(f"test_division: {a}")  
    plpy.notice(f"test_division: {b}")  
    return a / b;
except Exception as e:  
    plpy.notice(f"An error occurred: {str(e)}")  
    raise  
$$
 LANGUAGE plpython3u;

CREATE OR REPLACE FUNCTION test_nested3(a int, b int) RETURNS float AS 
$$
try:  
    a1 = a + 100
    b1 = b - 10
    plan = plpy.prepare("SELECT test_division($1, $2) AS c1", ["int", "int"])  
    rv = plpy.execute(plan, [a1, b1])  
    return rv[0]['c1']
except Exception as e:  
    plpy.notice(f"An error occurred: {str(e)}")  
    raise  
$$
 LANGUAGE plpython3u;

select test_nested3(10,2);
 test_nested3 
--------------
       -13.75
(1 row)

Python 3采用了更加一致的Unicode支持，字符串在Python 3中默认是Unicode字符串。
Python 3的标准库和模块与Python 2有所不同。一些在Python 2中可用的模块可能在Python 3中已被弃用或替换。

OushuDB 产品文档

Reference：PL/Python

本页目录