用PL/pgSQL寫postgreSQL的預存程序

今天學會了用 PL/pgSQL 寫 postgreSQL 的預存程序，網上資料實在少得可憐，唯一能搜到的一些還是抄來抄去的；還是翻postgresql的文檔吧，把今天解決的問題說一下吧，希望對其他人有協助。
問題是這樣的，有一張message表：
CREATE TABLE message
(
id int8 NOT NULL,
receiveuserid int8,
senduserid int8,
receivedelete bool DEFAULT false,
senddelete bool DEFAULT false,
……
CONSTRAINT usermessage_pkey PRIMARY KEY (id)
)
略去其他欄位，senduserid是發資訊的使用者id，senddelete如為true則表示這條訊息被發信人所刪除；至於receive我就不用說了。一條資訊只有被發信人和收信人都刪除，才能真正從表裡刪除（這是顯然的，否則收信人刪了一條訊息後，發信人的“寄件匣”裡就會找不到這條訊息）。
所以刪除訊息（可能是多條訊息）的時候要進行各種判斷（是否是發信人？是否是收信人？是否真正刪除？），用單條SQL陳述式完成這個工作顯然有些困難（當然用迴圈嵌套select不考慮效率的話也是可以實現的），只好寫預存程序了。
順便介紹常用的PL/pgSQL結構和文法吧：

• 結構

PL/pgSQL是一種塊結構的語言，比較方便的是用pgAdmin III建立Function，填入一些參數就可以了。基本上是這樣的：

CREATE OR REPLACE FUNCTION 函數名(參數1，[整型 int4, 整型數組 _int4, ...])
RETURNS 傳回值類型 AS
$BODY$
DECLARE
變數聲明
BEGIN
函數體
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

• 變數類型

除了postgresql內建的變數類型外，常用的還有 RECORD ，表示一條記錄。

• 賦值

賦值和Pascal有點像：“變數 := 運算式;”
有些奇怪的是連接字串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;

• 判斷

判斷又和VB有些像：
IF 條件 THEN
…
ELSEIF 條件 THEN
…
ELSE
…
END IF;

• 迴圈

迴圈有好幾種寫法：
WHILE expression LOOP
statements
END LOOP;
還有常用的一種是：（從1迴圈到9可以寫成FOR i IN 1..9 LOOP）
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP;

• 其他

還有幾個常用的函數：
SELECT INTO record …; 表示將select的結果賦給record變數（RECORD類型）
PERFORM query; 表示執行query並丟棄結果
EXECUTE sql; 表示執行sql語句，這條可以動態執行sql語句（特別是由參數傳入構造sql語句的時候特別有用）

最後，貼出解決上面這個問題的預存程序吧：

1. CREATE OR REPLACE FUNCTION message_deletes(ids "varchar", userid int8)
2.   RETURNS int4 AS
3. $BODY$
4. DECLARE
5.   r RECORD;
6.   del bool;
7.   num int4 := 0;
8.   sql "varchar";
9. BEGIN
10.   sql := 'select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in (' || ids || ')';
11.   FOR r IN EXECUTE sql LOOP
12.     del := false;
13.     IF r.receiveuserid=userid and r.senduserid=userid THEN
14.       del := true;
15.     ELSEIF r.receiveuserid=userid THEN
16.       IF r.senddelete=false THEN
17.         update message set receivedelete=true where id = r.id;
18.       ELSE
19.         del := true;
20.       END IF;
21.     ELSEIF r.senduserid=userid THEN
22.       IF r.receivedelete=false THEN
23.         update message set senddelete=true where id = r.id;
24.       ELSE
25.         del := true;
26.       END IF;
27.     END IF;
28.     IF del THEN
29.       delete from message where id = r.id;
30.       num := num + 1;
31.     END IF;
32.   END LOOP;
33.   return num;
34. END;
35. $BODY$
36.   LANGUAGE 'plpgsql' VOLATILE;

測試使用：