PostgreSQL与过程语言PL/Python

PostgreSQL允许使用除了 SQL 和 C 之外的其他语言编写用户定义的函数。这些其他的语言通常被称作过程语言（PL）。对于一个用过程语言编写的函数，数据库服务器没有关于如何解释该函数的源文本的内建知识。因此，这个任务被交给一个了解语言细节的特殊处理器。该处理器能够自己处理所有的解析、语法分析、执行工作，或者它可以作为一种PostgreSQL和编程语言既有实现之间的“粘合剂”。就像任何其他 C 函数一样，处理器本身是一个编译到共享对象并且按需载入的 C 语言函数。

在PostgreSQL的标准发布中当前有四种过程语言可用： PL/pgSQL、 PL/Tcl、 PL/Perl以及 PL/Python。 还有其他过程语言可用，

PL	Language	Homepage
PL/Lua	Lua	https://github.com/pllua/pllua
PL/R	R	http://www.joeconway.com/plr.html
PL/sh	Unix shell	https://github.com/petere/plsh
PL/v8	JavaScript	https://github.com/plv8/plv8
PL/go	Go	https://github.com/microo8/plgo
PL/Java	Java	https://tada.github.io/pljava
PL/sh	Unix shell	https://github.com/petere/plsh

先看看Postgres 支持的语言

SELECT * FROM pg_language; 

再看看Postgres支持哪些语言模版，

select * from pg_pltemplate;

从上面的结果可以看出tcl和perl分别多了个u的小尾巴，这里的u表示不可信赖的（unTrusted），它的含义是可以执行系统级命令，言外之意需要管理员权限，好处是能干更多工作，坏处就是，权限太高，而plpython则都是带u的，也就是说不可信赖的。

plpython是根据系统环境默认，PL/Python 同时支持 Python 2 和 Python 3 ，当前默认为Python2，启用该插件需要在数据库里执行，

CREATE EXTENSION plpythonu

如果你想使用python3，需要自己编译安装plpython3u，

CREATE EXTENSION plpython3u

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

CREATE FUNCTION funcname (argument-list) RETURNS return-typeAS $$ # PL/Python 函数体$$ LANGUAGE plpythonu;

例如，返回两个整数中较大的函数，

CREATE FUNCTION pymax (a integer, b integer) RETURNS integerAS $$ if a > b:   return a return b$$ LANGUAGE plpythonu;select pymax(3,5)#返回5

 这个函数会返回一个表，

CREATE OR REPLACE FUNCTION store_foo()   RETURNS TABLE (id int, name text)AS $$   return (       dict(id=i, name='foo-{0}'.format(i))       for i in range(5)   )$$ LANGUAGE plpythonu;

select * from store_foo(); 

运行时引入外部模块，

假设/tmp/foo/foo.py的代码如下，

def f():    return 'hello from foo'

我们创建个函数调用它，

CREATE FUNCTION foo_module()   RETURNS textAS $$   import sys   sys.path.insert(0, '/tmp/foo')   import foo   return foo.f()$$ LANGUAGE plpythonu;select foo_module();#返回foo_modulehello from foo

PL/Python不仅仅可以做些简单的运算，返回一张表，因为Python的强大，大部分Python可以完成的任务都可以根据需要放到PL/Python中，比如Pandas操作，机器学习等等。

比如我们写个函数来读取系统环境变量，

create extension plpythonu;  create type py_environ_type as (name text, value text);create or replace function py_environ(name varchar DEFAULT NULL) returns setof py_environ_typeas $$ import os aev = [] if name is None:   for k, v in os.environ.items():     aev.append((k, v)) else:   v = os.getenv(name)   if v is not None:     aev.append((name,v)) return aev;$$ language plpythonu;

#查看全部变量select * from py_environ();

只查找某个变量

select value from py_environ('PATH');#返回value/usr/bin:/bin:/usr/sbin:/sbin

也可以使用where来过滤想要的信息，比如查询PG相关的变量

select name, value from py_environ() where name like 'PG%';

官方文档有比较详细的介绍，下面一篇文章，我讲介绍在Postgres里进行机器学习，敬请期待。

参考，

• http://www.postgres.cn/docs/10/plpython-funcs.html

• Getting Started With Python inside PostgreSQL，https://dev.nextthought.com/blog/2018/09/getting-started-with-pgsql-plpythonu.html

• Simple Examples of using PL/Python in a SQL Database，https://nbviewer.jupyter.org/github/ihuston/plpython_examples/blob/master/simple_sql_example_notebook.ipynb#Simple-Examples-of-using-PL/Python-in-a-SQL-Database

欢迎关注我的公众号，alitrack

衡数提供下列服务，有意请留言

• PySpark培训

• Excel培训

• 数据挖掘咨询

• 数据挖掘外包

• 人才推荐