摘要
在PostgreSQL的标准发布中当前有四种过程语言可用: PL/pgSQL、 PL/Tcl、 PL/Perl以及 PL/Python。 还有其他过程语言可用,但是它们没有被包括在核心发布中。本文主要介绍使用PL/Python过程语言实现一个将表导出到csv文件的函数。
环境准备
- 需要重源码编译postgres,目的是启用PL/Python服务端编程语言。要编译PL/Python服务器端编程语言, 你需要一个Python的安装,包括头文件和distutils模块。最低的版本要求是Python 2.4。如果是版本3.1或更高版本,则支持Python 3。
- 安装python,本文使用python 3版本进行演示
$ sudo apt install python3 python3-dev
- 编译postgres源码
$ ./configure --enable-shared --with-python
$ make -j 8
$ sudo make install
因为PL/Python将以共享库的方式编译,
libpython
库在大多数平台上也必须是一个共享库。在默认的从源码安装Python时不是这样的,而是在很多操作系统发布中有一个共享库可用。如果选择了编译 PL/Python但找不到一个共享的libpython
,configure
将会失败。这可能意味着你不得不安装额外的包或者(部分)重编译Python安装以提供这个共享库。在从源码编译时,请用--enable-shared
标志运行 Python的配置脚本。
PL/Python函数简介
举个例子
CREATE FUNCTION py_add (a integer, b integer)
RETURNS integer
AS $$
return a + b
$$ LANGUAGE plpython3u;
- 查看函数
- 运行函数
操作步骤
如何获取表字段属性
以pgbench中的pgbench_accounts为例。
select * from pg_attribute where attrelid = (select oid from pg_class where relname = 'pgbench_accounts') and attnum > 0 order by attnum;
plpy函数使用
PL/Python 语言模块会自动导入一个被称为plpy
的 Python 模块。这个模块中的函数和常量在 Python 代码中可以用plpy.foo
样的方式访问。
CREATE OR REPLACE FUNCTION count_odd_fetch(batch_size integer) RETURNS integer AS $$
odd = 0
cursor = plpy.cursor("select num from largetable")
while True:
rows = cursor.fetch(batch_size)
if not rows:
break
for row in rows:
if row['num'] % 2:
odd += 1
return odd
$$ LANGUAGE plpython3u;
demo
CREATE or replace FUNCTION tab2file(file_name varchar, table_name varchar) RETURNS varchar AS $$
import csv
header_list =[]
plan = plpy.prepare("select * from pg_attribute where attrelid = (select oid from pg_class where relname = $1) and attnum > 0 order by attnum;", ["text"])
t_column = plpy.execute(plan, [table_name])
for c in t_column:
header_list .append(c['attname'])
f = open(file_name, mode="w", encoding="utf-8", newline="")
writer = csv.DictWriter(f, header_list)
writer.writeheader()
sql = "select * from " + table_name + ";"
cursor = plpy.cursor(sql)
while True:
rows = cursor.fetch(100)
if not rows:
break
writer.writerows(rows)
f.close()
sql = "select count(*) from " + table_name + ";"
count = plpy.execute(sql)
return count[0]['count']
$$ LANGUAGE plpython3u;
写在后面
上面只是演示pl/python的用法,把表导出csv并不是一个很好的很好的应用场景。postgres的copy to是最佳选择。
让我们看一下性能,应该有一个数量级的差距,当然python代码还是可以进一步优化的。