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 實例：資料分頁

PostgreSQL 觸發器(Trigger) 入門

Trigger - 觸發器，與交易資料鎖定一樣，是商用資料庫系統不可或缺的重要功能之一。你可曾要插入資料到資料庫的時候，同時又需要連帶更新其他表單的資料？在過去，你一定也是在前端應用程式，使用 INSERT INTO 然後再用 UPDATE，或是用一句句的 INSERT 命令來完成這任務。許多諸如此類的同步操作，假若都在資料庫系統外一步步下達命令完成，不僅是程式設計上繁複，效能上也不彰。觸發器的功能就是為了解決這類問題而設計的，當你更新或查詢某個資料表時會觸動觸發器，觸發器就會照您所設計的流程，同步去插入、更新、刪除其他資料，你不再需要重復下達多次的 SQL命令就能達成一連串資料的同步處理。

深入了解觸發器函數之前，如果能有個情境會比較好了解其中運作，你可以先想像一下有兩個資料表 mytable 和 mytable_counter 還有其中的各項欄位如下：

1. mytable - 客戶清單
   • mytable_id - 新增客戶的月份
2. mytable_counter - 每月份的客戶數量統計表
   • mc_mytable_id - 統計的月份
   • mc_count - 當月客戶總數

如果現在要設計一個機制統計每月客戶數量，你會怎麼做呢？以往傳統做法應該都是在 INSERT INTO mytable 之後，再 UPDATE mytable_counter。可是今天我們要把 UPDATE 的動作寫成觸發器函數，讓日後只要我們 INSERT 資料進去 mytable，PostgreSQL就會自動同步幫我們去 UPDATE mytable_counter 的 mc_count欄位，不需要我們再另外下達 UPDATE 的指令。

以下是一個簡單的觸發器函數例子：

CREATE OR REPLACE FUNCTION pg_mytrigger()
RETURNS "trigger" AS
$BODY$BEGIN
UPDATE mytable_counter SET mc_count = (mc_count + 1) WHERE mc_mytable_id = NEW.mytable_id;
RETURN NEW;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION pg_mytrigger() OWNER TO somebody; 

其中要注意的，NEW.mytable_id 代表新增加到 mytable 的資料列，其 mytable_id 欄位。在這例子中用來找到當月的統計紀錄，並予以統計數量加一。

套用觸發器函數

到此，觸發器函數到這階段已經建立好了，下一步要決定套用在哪一個資料表，也就是我們要設定哪一個資料表被 INSERT 時，PostgreSQL會去更新統計表的記錄。我們可以用以下指令套用觸發器函數到目標資料表上：

CREATE TRIGGER pg_mytrigger
BEFORE INSERT
ON mytable
FOR EACH ROW
EXECUTE PROCEDURE pg_mytrigger();

這 段命令需要解釋的是 BEFORE 這參數，觸發器雖說是『同時』照你的流程執行，但實際上是分為 BEFORE 和 AFTER，差別在於 BEFORE 是在『更新、查詢資料表之前』執行觸發器預訂的函數，而 AFTER 是在『之後』執行。也就是 BEFORE是先執行觸發器再新增或刪除客戶資料，AFTER是先新增、刪除資料再去執行觸發器。

後記

上 面的例子只是為了說明觸發器 (Trigger) 的功用和使用方法，已經簡化了很多內容，實際上還有許多需要考慮的地方，例如當統計資料表找不到當月的統計資料怎麼辦﹝因為該客戶可能是當月第一個客戶 ﹞？所以必須還要修改觸發器函數去判斷修正這一部份。另外一方面，目前只談到 INSERT 時執行觸發器函數，我們也可以在 DELETE 時執行觸發器函數為統計數量減一，這些都是觸發器可以做得到的，以後再花時間進一步討論。