在PostgreSQL上安装并使用扩展模块的教程

安装模块

注意: 我的运行环境是 Ubuntu 10.04 和 PostgreSQL 8.4

首先安装 postgresql-contrib 包并重启数据库服务器，然后检查 contrib 目录看是否包含一些可用模块：

sudo apt-get install postgresql-contrib
sudo /etc/init.d/postgresql-8.4 restart
cd /usr/share/postgresql/8.4/contrib/
ls

然后我们创建一个名为 module_test 的数据库：

su postgres
createdb module_test

然后我们将模块 chkpass, fuzzystrmatch, isn 和 hstore 应用到 module_test 数据库，执行下面命令即可：

psql -d module_test -f chkpass.sql
psql -d module_test -f fuzzystrmatch.sql
psql -d module_test -f isn.sql
psql -d module_test -f hstore.sql

接下来，我们来看看每个模块是如何使用的。
 
使用 chkpass

chkpass 模块引入一个新的数据类型 “chkpass” 这个类型用来存储一个加密的字段，例如密码。使用方法可以从下面的 SQL 里看到，存入 chkpass 字段的字符串会自动进行加密：

CREATE TABLE accounts (username varchar (100), password chkpass);
INSERT INTO accounts(username, "password" ) VALUES ( 'user1' , 'pass1' );
INSERT INTO accounts(username, "password" ) VALUES ( 'user2' , 'pass2' );

然后我们可以通过下面的SQL进行身份认证：

SELECT count (*) from accounts where username= 'user1' and password = 'pass1'

其中 = 操作符使用了 eq(column_name, text) 方法，该方法由 chkpass 模块提供用于测试是否相等。chkpass 使用 Unix 的 crypt() 函数，因此加密效果比较弱，该函数只对字符串的前8位进行加密，只要前8位相同的字符串就被认为是相等的。因此不建议在实际生产环境中使用 chkpass 模块，建议使用 pgcrypto 模块。
 
使用 fuzzystrmatch

该模块提供的函数包括：soundx(), difference(), levenshtein() 和 metaphone() 。soundx() 和 metaphone() 是语音算法，将文本字符串转成基于发音的代码字符串。而 difference() 和 levenshtein() 则返回数值代表两个输入字符串的相似度。

让我们先看看 levenshtein() 和 metaphone() 函数：

SELECT levenshtein( 'foodlets' , 'booklets' );

该查询返回 2，很显然。

metaphone() 函数需要两个参数，一个是文本字符串，另外一个是输出代码的最大长度的：

SELECT metaphone( 'foodlets' , 6);
SELECT metaphone( 'fudlets' , 6);

如果你试图获取两个字符串的 Levenshtein 距离，那将返回0：

SELECT levenshtein( 'FTLTS' , 'FTLTS' );

这意味着两个字符串发音类似。

fuzzystrmatch 用在网站实现搜索功能上是非常有用的，而且可用于实现拼写检查和错误关键字纠正，相当于是 Google 上的“Did you mean...”

使用 isn

该模块提供了存储国际标准数值的数据类型，例如 International Standard Book Numbers (ISBN), International Standard Music Numbers (ISMN), International Standard Serial Numbers (ISSN), Universal Product Codes (UPC), 等等。同时提供了校验函数、类型转换函数等等。

让我们来测试存储图书信息：

CREATE TABLE books(number isbn13, title varchar (100))
INSERT INTO books( "number" , title) VALUES ( '978-03' , 'Rework' );

INSERT 语句将会执行错误，因为输入的字符串不是一个有效的 ISBN 号，而下面的语句就可以正确执行：

INSERT INTO books( "number" , title) VALUES ( '978-0307463746' , 'Rework' )

如果要将一个10位的 ISBN 转成 13 位的，可以使用 isbn13() 函数：

INSERT INTO books( "number" , title) VALUES (isbn13( '0307463745' ), 'Rework' )

使用 hstore

你肯定已经听到很多关于 NoSQL 以及 key-value 数据库的介绍，使用 hstore 模块可以让 PostgreSQL 具备 key-value 存储的功能。

想象你正在处理一个表格，你对表格力列头的名称和类型处理没有任何思路，而 hstore 就可以解决你的问题，hstore 将 key 和 value 都作为文本存储，值可以为 NULL，但 key 不允许。

我们来创建一个使用 hstore 类型的表，并往表插入一些数据：

CREATE TABLE kv_data( id integer , data hstore)
INSERT into kv_data values
(1, hstore( 'name' , 'amit' ) || hstore( 'city' , 'bangalore' )),
(2, hstore( 'name' , 'raghu' ) || hstore( 'age' , '26' )),
(3, hstore( 'name' , 'ram' ) || hstore( 'age' , '28' ));

你可以创建你自己的 key ，例如 “height”, “favourite_book” 等等，而 || 运算符用于级联并列操作。

现在我们已经有一个表和少量测试数据，接下来看看怎么做查询、更改和删除操作，假设我们要查询 city 为 bangalore 的数据，可以使用如下SQL语句：

SELECT * from kv_data where data-> 'city' = 'bangalore'

要获取表中的平均年龄可以使用如下语句：

SELECT avg ((data-> 'age' ):: integer ) age from kv_data;

这里的 ::integer 用于将文本数据转成整数，以便可以使用数学函数。

要根据姓名进行排序，方法是：

SELECT * from kv_data order by data-> 'name' desc

将所有城市更改为 delhi ：

UPDATE kv_data SET data = data || ( 'city' => 'delhi' );

然后删除 age 这个键：

UPDATE kv_data set data = delete (data, 'age' )

或者删除名为 amit 的记录：

DELETE from kv_data where data-> 'name' = 'amit'

虽然这不是一个真正的 key-value 存储服务器，但 hstore 还是提供了做为 NoSQL 数据库足够的灵活性。

其他有用的模块

这里还有几个你可能会用到的模块：

• Pgcrypto 提供了哈希和加密函数，支持 SHA, MD5, Blowfish, AES 等算法
• Citext 增加了大小写敏感的文本数据类型，数据使用小写存储
• Uuid-ossp 提供了全局唯一标示符的函数
• Pg_trgm 增加了基于 trigram 匹配的字符串查找函数