2007-09-19

PostgreSQL 8.2.5, 8.1.10 小版次更新公告

更新:2007-09-17
對映章節:
http://www.postgresql.org/about/news.862

內容:
PostgreSQL 公告更新本次小版次包括 8.2, 8.1, 8.0, 7.4 與 7.3 版.
本次主要修正大多屬安全性議題的更新.

附加的安全性修正在 dblink 與 pgstattuple,
一個可能存在 vacuum 的 index-corruption 問題,
修正 GIN indexing, 與 logging 改良.
完整的修正與改進清單請見
the release notes.

相關版本如下 :

延伸閱讀(Link):
PostgreSQL 8.2.5 詳細更新完整內容

2007-09-09

PL/pgSQL:呼叫函數、SQL查詢與回傳值處理

更新:2007-09-09
對映章節:

內容:

使用 PL/pgSQL 不外乎就是撰寫資料庫的預儲程序(Stored Procedure)、函數(Function)、觸發器(Trigger) 等。設計這些內部程序,其中除了迴圈、真假值判斷、回傳等等特別需求的語法外,主要的內容還是由各種 SQL 查詢命令或是呼叫其他已存在的函數所構成。由於使用 PL/pgSQL 呼叫其他函數或執行SQL命令時,多半是要等待回傳值、資料列並更進一步處理,所以與平時用前端程式或SQL命令列對函數與SQL指令的操作上,有比較不同的習慣性用法。要了解在 PL/pgSQL 處理各種呼叫查詢,必須從回傳值處理的角度去深入。

以下是幾個常用處理函數和SQL命令回傳值的方法:

  1. 將回傳資料列指向 rs 變數﹝將 rs 定義為 record 類型﹞
    SELECT * INTO rs FROM mytable;

    然後可讀取回傳資料列的各欄位內容:

    rs.id
    rs.name
    rs.address
    ...

    註:此 SELECT INTO 使用方法很特別,並非是你想的那樣,請參考下文說明。


  2. 只回傳單獨欄位內容並指向 addr 變數﹝將 addr 定義成與 address 欄位類型相同﹞

    SELECT address INTO addr FROM mytable;


  3. 拋棄所有查詢和函數的回傳值
    EXECUTE myfunction();
    PERFORM myfunction();

    註:EXECUTE 和 PERFORM 的詳細差異不在本文討論範圍,請參考官方說明文件。


一般來說,在 PL/pgSQL 之中我們還是可以照常使用 SELECT、UPDATE、DELETE 等指令,差別在於執行查詢命令時『有無回傳值』。另外,以下有幾點,是使用 PL/pgSQL 呼叫及執行任何 SQL 命令時該注意的重點:


任何命令所回傳的值不能隨便忽略


在過去使用前端外部程式去執行 SQL 命令,你可以忽略不管回傳值的問題,就算有回傳任何資料,我們也可以省略不處理它;但在 PL/pgSQL 中就有所不同了,尤其在『觸發器(Trigger) 』函數的設計中,更是不可放任回傳值不處理,所以,通常在觸發器中我們如果要拋棄回傳值,請使用 PERFORM 。


SELECT INTO 的不同


在 PL/pgSQL 與平時使用 SELECT INTO 是完全不同的意義,後者是建立一個新的資料表,並將所查尋到的數個資料列寫入此資料表。而在 PL/pgSQL 中只是取得查詢資料列到自訂變數之中,並不會建立新的資料表。

延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
PostgreSQL 觸發器(Trigger) 入門
Stored Procedure 實例:資料分頁

2007-09-04

PostgreSQL 每週新聞摘要 - September 03 2007

更新:2007-09-04
對映章節:
http://people.planetpostgresql.org/dfetter/index.php?/archives/134-guid.html

內容:

  1. Npgsql 的使用執照更改為 BSD 授權.
    (Npgsql 是 PostgreSQL 作為提供 .Net 資料的提供者.
    主要目的是允許任何 .Net framework 程序開發能存取資料庫伺服器.
    100% 應用在 C# code. 並且工作在 Postgresql 7.x 與 8.x.)
    專案站點:
    http://pgfoundry.org/projects/npgsql/

  2. Slony-I 1.2.11 發佈.
    (Slony-I 是一個
    主對多從式"master to multiple slaves"複寫(replication)系統.
    支持串聯效應"cascading" (例如. 一個節點"node"A能抄寫到另一個節點 B,
    節點 B 或 A 都能再各自抄寫到其它節點) 與災難復原"failover".
    專案站點:
    http://slony.info/

  3. SE-PostgreSQL 8.2.4-1.0 發佈.
    (SE-PostgreSQL 是安全強化建構在
    Security Enhanced Linux (SELinux)'s 上的擴展版)
    專案站點:
    http://code.google.com/p/sepgsql/

感謝參與 PostgreSQL 一切開發工作與相關活動的參與人們!!!

延伸閱讀(Link):
PostgreSQL 複寫叢集系統 Slony-I(一)
PostgreSQL 複寫叢集系統 Slony-I(二) (簡體)

2007-09-01

Stored Procedure 案例:動態排列資料

在資料整理時,我們常碰到有些資料需要往前移動,有些則要往後移動,尤其在資料的顯示流程規劃上就常常要使用到這種 功能。可是傳統資料庫的排序功能,不外乎就是使用日期、時間、資料編號做為排序依據,然後利用 ORDER BY 命令達成資料排序的目的,除此之外,我們無法動態調整資料列的先後次序。


一種解決辦法是為每個資料列增加新的欄位﹝例子中取名為mytable_after﹞,用來記錄該筆資料列的前一筆資料編號。所以我們在讀取資料時就使用該欄位記錄的值,逐一列出每筆資料。操作流程大致上是:

讀取第一筆資料 Amytable_after 為 0
讀取欄位 mytable_after = A 的第二筆資料 B
重覆操作直到找不到下一筆資料
此方法讀取過程繁複,若交由外部程式去做,將會不停的發送 SELECT 命令給 SQL Server,可想而之,其效能必定差矣。所以,我們可以建立 PostgreSQL 預儲程序,讓 PostgreSQL 在內部操作處理,然後直接回傳已排序完成的資料流。

PostgreSQL 動態排列實作例子:

CREATE OR REPLACE FUNCTION mytable_dynlist()
RETURNS SETOF mytable AS
$BODY$DECLARE
rscount Integer;
rsnext Integer;
rs record;

BEGIN
    select count(*) INTO rscount from mytable;
rsnext = 0;
FOR i IN 1 ..
rscount LOOP
SELECT * INTO rs from mytable where mytable_after = rsnext LIMIT 1;
rsnext = rs.mytable_id;
RETURN NEXT rs;
END LOOP;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION
mytable_dynlist() OWNER TO somebody;

當 資料不多時,這種在資料庫內部處理動態排列很省事,在前端的程式也很好寫,但是,資料量一大,這種方式會造成 SQL Server 相當大的負擔。所以,當資料量多或是在前端程式有做多台機器的負載平衡,還是建議在前端先將所有要排序的資料下載後,在前端的程式做排序處理。



延伸閱讀(Link):
PostgreSQL 的 Stored Procedure 簡易實例
Stored Procedure 實例:資料分頁

PostgreSQL & Google-Analytics Running...

::Planet PostgreSQL::

PostgreSQL Information Page

PostgreSQL日記(日本 石井達夫先生Blog)

PostgreSQL News

黑喵的家 - 資料庫相關

Google 網上論壇
PostgreSQL 8 DBA 專業指南中文版
書籍內容討論與更多下載區(造訪此群組)
目錄下載: PostgreSQL_8 _DBA_Index_zh_TW.pdf (更新:2007-05-18)

全球訪客分佈圖(Google)

全球訪客分佈圖(Google)