Fred's blog

使用 PL/pgSQL 不外乎就是撰寫資料庫的預儲程序(Stored Procedure)、函數(Function)、觸發器(Trigger) 等。設計這些內部程序，其中除了迴圈、真假值判斷、回傳等等特別需求的語法外，主要的內容還是由各種 SQL 查詢命令或是呼叫其他已存在的函數所構成。由於使用 PL/pgSQL 呼叫其他函數或執行SQL命令時，多半是要等待回傳值、資料列並更進一步處理，所以與平時用前端程式或SQL命令列對函數與SQL指令的操作上，有比較不 同的習慣性用法。要了解在 PL/pgSQL 處理各種呼叫查詢，必須從回傳值處理的角度去深入。 以下是幾個常用處理函數和SQL命令回傳值的方法： 將回傳資料列指向 rs 變數﹝將 rs 定義為 record 類型﹞ SELECT * INTO rs FROM mytable; 然後可讀取回傳資料列的各欄位內容： rs.id rs.name rs.address ... 註：此 SELECT INTO 使用方法很特別，並非是你想的那樣，請參考下文說明。 只回傳單獨欄位內容並 指向 addr 變數 ﹝將 addr 定義成與 address 欄位類型相同﹞ SELECT address INTO addr FROM mytable; 拋棄所有查詢和函數的回傳值 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 是完全不同的意義，後者是建立一個新...

增進 SQL 處理效率的一個很重要的方法就是使用 Stored Procedure ，一般商用級以上的資料庫系統都會內建這種功能。 以下是在 PostgreSQL 中建立一個 Stored Procedure 的最簡單例子： --執行結果：執行 select * from mytable where table_id = id 並回傳所有符合的資料列 CREATE OR REPLACE FUNCTION pg_myfunc(id integer) RETURNS SETOF mytable AS $BODY$DECLARE rs RECORD; BEGIN FOR rs IN select * from mytable where table_id = id LOOP RETURN NEXT rs; END LOOP; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION pg_myfunc(id integer) OWNER TO somebody; 如同 Oracle PL/SQL一般，PostgreSQL也有自己的程序語言 PL/pgSQL，其中最常用到的幾種語法使用： IF 判斷式 IF ... THEN ELSIF ... THEN ELSE ENDIF; FOR LOOP迴圈 FOR i IN 1 .. 10 LOOP ... END LOOP; 當找不到任何符合的資料列時 SELECT * FROM mytable WHERE mytable_id = id; IF not found THEN -- 找不到任何符合的資料列時執行這裡的程序 END IF; 全部的 Function 都建立完成了，接著就要開始使用已建立的 Stored Procedure。 執行 Functions 有兩種SQL命令可以使用： Function 沒有 回傳值時使用 SELECT pg_myfunc(); Function 有 回傳值時使用 SELECT * FROM pg_myfunc();

其實一直以來我都有去注意各家資料庫的發展，直到最近因為專案的需要，又有時間與機會研究和使用 PostgreSQL 。對於當初的構想─分散式的資料庫查詢﹝參考 之前發表過的『 對於高負載資料庫的突發奇想 』一文 ﹞ ， 就網友回應和查詢資料後，Oracle和MySQL新版已有這種設計，而在 PostgreSQL 我是採用 contrib/dblink 來實現其 分 散式資料查詢的架構，雖然這種非正規的做法，的確可以實現我對高負載資料庫的設計，但畢竟此做法還是非正規且沒有簡單完整的設計流程，在實行上有著相當的 困難和複雜度。最近在仔細探討和研究 PostgreSQL 等相關專案中，意外在之前被我忽略的專案『pgpool』裡發現了較完整正規的解決辦法。 由於pgpool-I沒有平行化和不支援夠多的資料庫節點，之前一直被我所忽略，甚至沒發現他推出了新的版本─ pgpool-II ，閱讀後官方網站後，發現pgpool-II有以下新特色： uses multiple DB nodes to process search queries in parallel for higher performance compared to a single PostgreSQL server comes with web-based administration tool called "pgpoolAdmin" for easier operation (figure on the right) up to 128 DB nodes can be attached (can be extended by recompilation) can be operated using a port opened in pgpool-II for administration via network 從文中大略上是說有平行化查詢、更好用的Web-based管理工具、支援到128台伺服器節點等。在官方網站有註明它的最後修改日期：『 Last modified: Fri Sep 8 14:15:32 2006 』，看來最晚在去年9月以前它就已經出爐面世了，愚蠢的我居然沒有發現，還自己土法練鋼的去想辦法實現平行化的功能，真是笨！ pgpool-II的官方網站是 http...