在PostgreSQL上安裝並使用擴充模組的教程_資料庫其它

安裝模組

注意: 我的運行環境是 Ubuntu 10.04 和 PostgreSQL 8.4

首先安裝 postgresql-contrib 包並重啟資料庫伺服器，然後檢查 contrib 目錄看是否包含一些可用模組：
 

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

然後我們建立一個名為 module_test 的資料庫：
 

su postgrescreatedb module_test

然後我們將模組 chkpass, fuzzystrmatch, isn 和 hstore 應用到 module_test 資料庫，執行下面命令即可：
 

psql -d module_test -f chkpass.sqlpsql -d module_test -f fuzzystrmatch.sqlpsql -d module_test -f isn.sqlpsql -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 匹配的字串尋找函數