本篇文章将会是这个系列的最后一篇。数据库是一门实践为王的技术,所以我无法通过寥寥几篇专栏就把PostgreSQL给说清楚,更多有价值的功能及用法只能有待你自己去亲自动手尝试了。

今天我还会介绍一个属于PostgreSQL独有的特性,并且这个特性如同一把钥匙,可以给你打开一个崭新的数据库编程世界,至少我是这么认为的,那么我们赶紧开始吧。

SQL这种东西,本身是面向结果的,而不是面向过程的。这跟传统的编程语言有很大的区别,比如你在使用JavaScriptJava时,这些语言都是基于过程的,你要一步一步通过代码表达式把要做的逻辑过程编写出来,让计算机理解。而SQL是不具备这种能力的,虽然它看起来更高级,你只需要对着数据库说“要有光”(select light from universe),“光”就来了。

但是不可否认,SQL语言的表达能力是有限的,有些复杂的逻辑我们无法依赖SQL直接实现。比如:计算当前时间是本年度的第几周,这显然已经超出了SQL的能力范围。所以数据库的创造者们,给数据库增加了SQL之外的能力,也就是Procedural Language(过程语言)简称PL。这种过程语言就给开发者提供了传统编程语言的逻辑方式,方便我们在数据里实现一种叫存储过程/函数的东西,进而拓展了数据库本身的逻辑能力。

几乎每个关系型数据库都有自己的PL实现,比如OraclePL/SQLSQL ServerT-SQL,而在PostgreSQL中,提供的就是PL/pgSQL

这当然也不是什么新鲜事。作为本系列的最后一篇,如果只是讲讲PL/pgSQL未免有些平淡了。

容我给你展示一组清单:

  • PL/Java
  • PL/PHP
  • PL/Perl
  • PL/Python
  • PL/V8

结合前文所述,您大概已经猜到了。PL/xxx其实就是在说用基于某个语言实现数据库里的存储过程。换句话说,我们可以用自己熟知的高级语言,如:JavaPHPPython来编写数据库的存储过程。当然了,这个功能是PostgreSQL独有的,别的数据库可享受不到。至于PL/V8,如果你熟悉前端开发的话,应该不难猜出这里说的是JavaScriptV8引擎。

下面我就用PL/Python来演示下,如何用Python3来开发PostgreSQL的存储过程。

首先需要在数据库服务器上安装Python3,然后再安装PostgreSQLPython3扩展。以ubuntu为例,你需要执行:

apt-get install postgresql-plpython3-13

注意最后的数字13,应该与你本地的PostgreSQL版本相对应。一旦扩展安装关闭,就可以通过一条SQL语句,在数据库中开启对应的扩展功能,你需要执行。

create extension "plpython3u";

下面我们尝试创建一个存储过程(其实称为“函数”可能更准确,不过“存储过程”比“函数”更贴近数据库的专有词汇,为了方便大家理解,我采用了“存储过程”的说法)。

create function pymax(a integer, b integer)
  returns integer
language plpython3u
as $$
if a > b:
    return a
return b
$$;

这是一个简单的比较两个整型大小的存储过程,本身没什么技术含量。需要特别注意的是$$之间的代码段是通过language指定的,当我们指定了plpython3u后,就可以用Python3来编写函数了。

简单执行测试一下:

studypg=# select pymax(2, 3);
 pymax
-------
     3
(1 row)

可以看到函数顺利执行成功了。

聪明的你可能会说,能利用上Python的语言本身固然有点意思,但是实际价值却不大。要是能利用上Python的生态,那才是真的强。答案当然是Yes啦。

众所周知,最近几年Python在机器学习领域可谓是独领风骚。这得益于其多年在数学领域的积累,而numpy这个包,更是所有数学工作者的最爱。下面我就用numpy演示,如何在PostgreSQL中使用Python的强大生态。

首先安装numpy包,执行:

pip3 install numpy

然后我们创建一个存储过程,用来计算以e为底的自然数对数,语句如下:

create or replace function pylog(x float)
  returns float
language plpython3u
as $$
import numpy as np

return np.log(x)
$$;

看下执行结果:

studypg=# select pylog(1);
 pylog
-------
     0
(1 row)

studypg=# select pylog(0.1);
       pylog
-------------------
 -2.30258509299405
(1 row)

非常完美,借助Python生态的力量,我们仅用几句话就实现了原本SQL根本无法实现的功能。其实这还只是冰山一角。笔者甚至通过Python版的数据库函数作为数据库表的触发器,拦截到数据的变化,直接把变化信息转发到MQTT服务器,从而让其他外部应用,可以通过对MQTT消息的订阅,得到数据变化的推送信息。

好了,关于PostgreSQL中神奇的存储过程,就介绍到这里了。其实在PostgreSQL中还有很多神奇的事情值得挖掘,比如触发器,PostgreSQL不光可以拦截DML还可以拦截DDL;还有外部数据源FDWPostgreSQL中可以引用多种外部数据,包括MySQLOracle等主流关系型数据库,甚至还支持RedisMongoDB等非关系型数据库。总之,只有想不到,没有做不到。

关于PostgreSQL的系列文章至此就结束了。希望我的文章能激发出你对数据库的兴趣,愿这门悠久而强大的技术能够在你的工作中起到一些积极作用。最重要的,请一定要多实践。我们下个话题再见😃

推荐阅读本系列的其他文章: