from:http://blog.csdn.net/johnny_83/archive/2008/03/27/2223147.aspx
PL/pgSQL是 PostgreSQL 數據庫系統的一個可裝載的過程語言。 PL/pgSQL的設計目標是創建一種可裝載的過程語言，可以
· 可用于創建函數和觸發器過程；
· 為 SQL 語言增加控制結構；
· 可以執行復雜的計算；
· 繼承所有用戶定義類型，函數和操作符；
· 可以定義為被服務器信任（的語言）；
· 容易使用。
除了用于用戶定義類型的輸入/輸出轉換和計算函數以外， 任何可以在 C 語言函數里定義的東西都可以在 PL/pgSQL里使用。比如，我們可以創建復雜的條件計算函數， 并隨后將之用于定義操作符或者用于函數索引中。
1 概述
PL/pgSQL 函數第一次(在任何一個服務器進程內部)被調用時，PL/pgSQL 的調用句柄分析函數源文本生成二進制指令樹。該指令樹完全轉換了 PL/pgSQL 語句結構，但是在函數內使用到的獨立的SQL 表達式和SQL 命令并未立即轉換。
在每個函數中用到的表達式和 SQL 命令在函數里首次使用的時候，PL/pgSQL 解釋器創建一個準備好的執行規劃（使用 SPI 管理器的SPI_prepare和SPI_saveplan 函數）。 隨后對該表達式或者命令的訪問都將使用已準備好的規劃。因此，一個在條件代碼中有許多語句，可能需要執行規劃的函數，只需要準備和保存那些真正在數據庫聯接期間真正使用到的規劃。這樣可以有效地減少為 PL/pgSQL 函數里的語句生成分析和執行規劃的總時間。 不過有個缺點是在特定表達式或者命令中的錯誤可能要到函數中的那部分執行到的時候才能發現。
一旦 PL/pgSQL 在函數里為一個命令制定了執行計劃，那么它將在該次數據庫聯接的生命期內復用該規劃。 這么做在性能上通常會更好一些，但是如果你動態地修改你的數據庫模式，那么就可能有問題。 比如：
CREATE FUNCTION populate() RETURNS integer AS
$$
DECLARE
-- 聲明段
BEGIN
PERFORM my_function();
END;
$$
LANGUAGE plpgsql;
如果你執行上面的函數，那么它將在為PERFORM語句生成的執行計劃中中引用 my_function() 的 OID。 然后，如果你刪除然后重新創建 my_function()， 那么 populate() 就會再也找不到 my_function()。 這時候你只能重新創建 populate()， 或者至少是重新開始一個新的數據庫會話，好讓該函數能重新編譯一次。 另外一個避免這種問題的方法是在更新my_function 的定義的時候 使用 CREATE OR REPLACE FUNCTION （如果一個函數被"替換"，那么它的 OID 將不會變化）。
因為Pl/pgSQL用這種方法保存執行規劃， 所以那些在PL/pgSQL里直接出現的 SQL 命令必須在每次執行的時候引用相同的表和字段； 也就是說，你不能拿一個參數用做 SQL 命令中的表或者字段的名稱。 要繞開這個限制，你可以用 PL/pgSQL 的 EXECUTE語句動態地構造命令 — 代價是每次執行的時候都構造一個新的命令計劃。
注意: PL/pgSQL 的EXECUTE語句和 PostgreSQL 服務器支持的EXECUTE語句（執行一個準備好的查詢）沒有關系。 服務器的EXECUTE語句不能在 PL/pgSQL 函數中使用（而且也沒必要）。
服務器中的EXECUTE語句：EXECUTE plan_name [ (parameter [, ...] ) ]
EXECUTE 用戶執行一個前面準備好的語句。因為一個準備好的查詢只在會話的生命期里存在，那么準備好的查詢必須是在當前會話的前些時候用 PREPARE 語句執行的。
如果創建語句的PREPARE 語句聲明了一些參數， 那么傳遞給 EXECUTE 語句的必須是一個兼容的參數集， 否則就會生成一個錯誤。請注意（和函數不同），準備好的語句不會基于參數的類型或者個數重載：在一次數據庫會話過程中，準備好的語句的名字必須是唯一的。
PREPARE -- 創建一個準備好的查詢，語法如下：
PREPARE plan_name [ (datatype [, ...] ) ] AS statement
在使用完PREPARE創建的查詢之后，可以使用DEALLOCATE（刪除一個準備好的查詢）,語法如：DEALLOCATE [ PREPARE ] plan_name。
1.1使用PL/pgSQL的優點
SQL 是PostgreSQL 和大多數其它關系型數據庫用做命令語言的語言。 它是可以移植的，并且容易學習使用。但是所有 SQL 語句都必須由數據庫服務器獨立地執行。
這就意味著你的客戶端應用必須把每條命令發送到數據庫服務器，等待它處理這個命令，接收結果，做一些運算，然后給服務器發送另外一條命令。 所有這些東西都會產生進程間通訊，并且如果你的客戶端在另外一臺機器上甚至還會導致網絡開銷。
如果使用了PL/pgSQL，那么你可以把一塊運算和一系列命令在數據庫服務器里面組成一個塊，這樣就擁有了過程語言的力量并且簡化 SQL 的使用，因而節約了大量的時間，因為你用不著付出客戶端/服務器通訊的過熱。 這樣可能產生明顯的性能提升。
同樣，在 PL/pgSQL 里，你可以使用 SQL 的所有數據類型，操作符和函數。
1.2所支持的參數和結果數據類型
它們還可以接受或者返回任意用名字聲明的復合類型（行類型）。還可以聲明一個 PL/pgSQL 函數為返回record的函數， 意思是結果是一個行類型，這個行的字段是在調用它的查詢中指定。
PL/pgSQL 函數還可以聲明為接受并返回多態的類型anyelement和anyarray。一個多態的函數實際操作的數據類型可以在不同的調用環境中變化。關于多態類型，詳細見下“多態類型”。
PL/pgSQL 還可以聲明為返回一個它們可以返回的任何單個實例的"集（set）"，或者表。 這樣的函數通過為結果集每個需要返回的元素執行一個 RETURN NEXT 生成它的輸出。
最后，PL/pgSQL 函數可以聲明為返回 void，如果它沒啥有用的東西可以返回的話。
PL/pgSQL 目前還不是完全支持域類型：它看待域類型和下層的標量類型是一樣的。 這就意味著與域關聯的約束將不會被強制。對于函數參數，這不是什么問題， 但是如果你把 PL/pgSQL 函數聲明為返回一個域類型，那么就有危險。
多態類型
兩種特別有趣的偽類型是 anyelement 和 anyarray， 它們在一起稱作多態類型。任何用這些類型定義 的函數就叫做多態函數。一種多態函數可以在許多不同的數據類型上操作，它們判斷具體類型的方法是在一次調用中，使用實際傳遞進來的數據類型 來判斷。
多態參數和結果是相互綁定，并且在分析查詢調用的函數時解析成特定的數據類型。每個聲明成 anyelement 的位置（參數或者返回類型）都允許擁有 一個特定的實際數據類型，但是在任何給定的調用過程中，它們都必須 是同樣的類型。每個聲明為 anyarray 的位置都可以是任何數組數據類型，但是，類似的，它們也不許都是同樣的類型。如果有些 位置聲明為 anyarray 而其它的位置聲明為 anyelement， 那么在 anyarray 位置上的類型必須是元素類型與那些出現在 anyelement 位置上的同類型的數組。
因此，如果多于一個參數位置聲明為一個多態類型，其實際效果是只允許某些實際參數類型的組合出現。比如，一個函數聲明為 equal(anyelement, anyelement) 將接受任何兩個輸入值，只要它們的數據類型相同。
如果一個函數的的返回值聲明為多態類型，那么至少有一個參數位置也是多態的，并且提供給參數的類型決定該詞調用實際返回的類型。比如，如果沒有數組下標 機制，那么我們可以定義一個函數實現下標的函數，像 subscript(anyarray, integer) returns anyelement。 這個聲明約束實際上的第一個參數是一個數組類型，并且允許分析器從第一個參數的實際類型里推導出正確的返回類型。
2 開發 PL/pgSQL 的一些提示
用PL/pgSQL 做開發的一個好方法是簡單地使用你喜歡的文本編輯器創建你的函數，然后在另外一個控制臺里，用 psql 裝載這些函數。如果你用這種方法， 那么用 CREATE OR REPLACE FUNCTION 寫函數是個好主意。這樣，你就可以重載文件以更新函數定義。比如：
CREATE OR REPLACE FUNCTION testfunc(integer)
RETURNS integer AS
$$
DECLARE
-- 變量聲明部分
BEGIN
....
END;
$$
LANGUAGE plpgsql;
在運行psql的時候，你可以用下面命令裝載或者重載這樣的函數定義文件：\i filename.sql；然后馬上發出 SQL 命令測試該函數。
另外一個開發PL/pgSQL程序的好方法是用一種GUI的數據庫訪問工具，并且是實現了過程語言開發設施的那種。 這種工具中的一種就是 pgaccess，當然還有其他的。這些工具通常提供了一些很有用的功能，比如逃逸單引號，令重建和調試函數更簡單等。
注：PL/pgSQL 函數的代碼都是在 CREATE FUNCTION 里以一個字串文本的方式聲明的。 如果你用兩邊包圍單引號的常規方式寫字串文本，那么任何函數體內的單引號都必須寫雙份；類似的是反斜杠也必須雙份。雙份引號非常乏味，在更復雜的場合下，代碼可能會讓人難以理解， 因為你很容易發現自己需要半打甚至更多相連的引號。 我們建議你用"美元符包圍"的字串文本來寫函數體。
引號處理：
· 單引號(‘) 函數中，如果字符串中有單引號出現，則使用2個單引號表示單引號；
· 雙引號(‘”) 如果是雙引號，則用4個單引號表示。
3 PL/pgSQL結構
PL/pgSQL是一種塊結構的語言。函數定義的所有文本都必須是一個塊。 一個塊用下面的方法定義：
[ <<label>> ]
[ DECLARE
declarations]
BEGIN
Statements
END;
塊中的每個聲明和每條語句都是用一個分號終止的，如果一個子塊在另外一個塊里，那么 END 后面必須有個分號，如上所述；不過結束函數體的最后的 END 可以不要這個分號。
所有關鍵字和標識符都可以用混和大小寫的方式來寫。標識符被隱含地轉換成小寫字符，除非被雙引號包圍。
在 PL/pgSQL 里有兩種類型地注釋。一個雙破折號（--） 引出一個擴展到該行結尾的注釋。一個 /* 引出一個塊注釋，一直擴展到下一次 */ 的出現。 塊注釋不能嵌套，但是雙破折號注釋可以包圍在塊注釋里面，并且雙破折號可以隱藏一個塊注釋分隔符 /* 和 */。
在一個塊的語句段里的任何語句都可以是一個子塊。子塊可以用于邏輯分組或者把變量局部化為作用于一個比較小的語句組。
在語句塊前面的聲明段（declarations section）里定義的變量在每次進入語句塊時都初始化為它們的缺省值， 而不是每次函數調用時初始化一次。比如：
CREATE FUNCTION somefunc()
RETURNS integer AS
$$
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 在這里的數量是 30
quantity := 50;
--
-- 創建一個子塊
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- 在這里的數量是 80
END;
RAISE NOTICE 'Quantity here is %', quantity; -- 在這里的數量是 50
RETURN quantity;
END;
$$
LANGUAGE plpgsql;
我們一定不要把PL/pgSQL里用于語句分組的 BEGIN/END 和用于事務控制的數據庫命令搞混了。 PL/pgSQL的 BEGIN/END 只是用于分組（譯注∶象 C 里的 {}）； 它們不會開始和結束一個事務。 函數和觸發器過程總是在一個由外層命令建立起來的事務里執行 — 它們無法開始或者提交事務，因為 PostgreSQL 沒有嵌套事務。 不過，一個包含 EXCEPTION 子句的塊實際上形成一個子事務，它可以在不影響外層事務的情況下回滾。
4 聲明
所有在塊里使用的變量都必須在一個塊的聲明段里聲明。（唯一的例外是一個FOR循環里的循環變量是在一個整數范圍內迭代的，被自動聲明為整數變量。）
PL/pgSQL變量可以用任意的 SQL 數據類型，比如integer，varchar和char。
下面是一些變量聲明的例子：
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.fieldname%TYPE;
arow RECORD;
一個變量聲明的一般性語法是：
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression];
如果給出了DEFAULT子句，那么它聲明了在進入該塊的時候賦予該變量的初始值。 如果沒有給出DEFAULT子句，那么該變量初始化為 SQL 空值。 CONSTANT選項避免了該變量被賦值，這樣其數值在該塊的范圍內保持常量。如果聲明了NOT NULL，那么賦予NULL數值將導致一個運行時錯誤。 所以所有聲明為NOT NULL的變量還必須聲明一個非空的缺省值。
缺省值是在每次進入該塊的時候計算的。因此，如果把 now() 賦予一個類型為 timestamp 的變量會令變量擁有函數實際調用的時間，而不是函數預編譯的時間。
例子∶
quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;
4.1函數參數的別名
傳遞給函數的參數都是用 $1，$2，等等這樣的標識符。 為了增加可讀性，我們可以為 $n 參數名聲明別名。 然后別名或者數字標識符都可以指向參數值。
有兩種創建別名的方法，比較好的是在 CREATE FUNCTION 命令里給出參數名， 比如：
CREATE FUNCTION sales_tax(subtotal real)
RETURNS real AS
$$
BEGIN
RETURN subtotal * 0.06;
END;
$
LANGUAGE plpgsql;
另外一個方法，是PostgreSQL 8.0以前的唯一的方法，是明確地聲明為別名，使用聲明語法: name ALIAS FOR $n;
這個風格的同一個例子看起來像下面這樣:
CREATE FUNCTION sales_tax(REAL)
RETURNS real AS
$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$
LANGUAGE plpgsql;
注：如果一個PL/pgSQL函數的返回類型聲明為一個多態類型(anyelement或anyarray)，那么就會創建一個特殊的參數：$0。我們就可以對這個參數進行操作。
例子：
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS
$$
DECLARE
result ALIAS FOR $0;
BEGIN
result := v1 + v2 + v3;
RETURN result;
END;
$$
LANGUAGE plpgsql;
4.2拷貝類型
variable%TYPE
%TYPE 提供一個變量或者表字段的數據類型。 你可以用這個聲明將要保存數據庫數值的變量。比如，假如你在 users 表里面有一個字段叫 user_id。要聲明一個和 users.user_id 類型相同的變量，你可以寫：user_id users.user_id%TYPE;
通過使用 %TYPE，你必須知道你引用的結構的數據類型， 并且，最重要的是，如果被引用項的數據類型在將來變化了（比如：你把 user_id 的類型從 integer 改成 real），你也不需要修改你的函數定義。
%TYPE 對多態的函數特別有用，因為內部變量的數據類型可能在不同調用中是不一樣的。我們可以通過給函數的參數或者結果占位符附加 %TYPE 的方法來創建合適的變量。
4.3行類型
name table_name%ROWTYPE;
name composite_type_name;
一個復合類型變量叫做行變量（或者row-typeSELECT或者 FOR命令結果的完整一行，只要命令的字段集匹配該變量聲明的類型。行數值的獨立的字段是使用常用的點表示法訪問的，比如 rowvar.field。變量）。 這樣的一個變量可以保存一次
一個行變量可以聲明為和一個現有的表或者視圖的行類型相同，方法是使用 table_name%ROWTYPE 表示法； 或者你也可以聲明它的類型是一個復合類型的名字。（因為每個表都有一個相關聯的同名數據類型，在 PostgreSQL 里實在是無所謂你寫不寫 %ROWTYPE。但是有 %ROWTYPE 的形式移植性更好。）
函數的參數可以是復合類型（表的完整行）。這個時候，對應的標識符 $n 將是一個行變量，并且可以從中選取字段，比如 $1.user_id。
在一個行類型的變量中，只可以訪問用戶定義的表中行的屬性，不包括 OID 或者其他系統屬性（因為該行可能來自一個視圖）。 該行類型的數據域繼承表中象 char(n) 這種類型字段的尺寸和精度。
這里是一個使用復合類型的例子：
CREATE FUNCTION merge_fields(t_row tablename)
RETURNS text AS
$$
DECLARE
t2_row table2name%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2name WHERE ... ;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$
LANGUAGE plpgsql;
SELECT merge_fields(t.*) FROM tablename t WHERE ... ;
4.4記錄類型
name RECORD;
紀錄變量類似行類型變量，但是它們沒有預定義的結構。它們在SELECT或者FOR命令中獲取實際的行結構。 一個行變量的子結構可以在每次賦值的時候改變。這樣做的一個結果是：在一個記錄變量被賦予數值之前，它沒有子結構， 并且任何對其中的數據域進行訪問的企圖都將產生一個運行時錯誤。
請注意 RECORD 不是真正的數據類型，只是一個占位符。 我們還應該意識到在把一個 PL/pgSQL 函數聲明為返回record類型的時候， 它和一個記錄變量的概念并不完全相同，即使這個函數可能使用一個記錄變量保存它的結果也如此。 在兩種情況下，在書寫函數的時候，實際的行結構都是不知道的，但是對于返回 record 的函數來說， 實際的結構是在調用它的查詢被分析的時候決定的，而行變量可以在運行中改變其行結構。
4.5RENAME
RENAME oldname TO newname;
你可以用 RENAME 聲明修改一個變量，記錄或者行的名字。 如果 NEW 或者 OLD 在個觸發器過程里被另外一個名字引用， 那么這個東西就很有用。又見 ALIAS。
例子：
RENAME id TO user_id;
RENAME this_var TO that_var;
注意: RENAME 在PostgreSQL7.3 里好像有問題。修補這個毛病的優先級比較低， 因為 ALIAS 覆蓋了大多數 RENAME 的實際用途。
5 表達式
所有在PL/pgSQL 語句里使用的表達式都是用服務器的普通SQL執行器進行處理的。 實際上，類似下面的查詢：SELECT expression。是使用 SPI 管理器執行的。 在計算之前，出現PL/pgSQL變量標識符的地方先被參數代替， 然后變量的實際值放在參數數組里傳遞給執行器。 這樣就允許SELECT的執行計劃只需要準備一次，并且在隨后的計算中復用。
PostgreSQL 的主分析器做的類型檢查對常量數值的代換有一些副作用。 詳細說來就是下面這兩個函數做的事情有些區別：
CREATE FUNCTION logfunc1 (logtxt text) RETURNS timestamp AS $
BEGIN
INSERT INTO logtable VALUES (logtxt, 'now');
RETURN 'now';
END;
$ LANGUAGE plpgsql;
和
CREATE FUNCTION logfunc2 (logtxt text) RETURNS timestamp AS $
DECLARE
curtime timestamp;
BEGIN
curtime := 'now';
INSERT INTO logtable VALUES (logtxt, curtime);
RETURN curtime;
END;
$ LANGUAGE plpgsql;
在 logfunc1() 的實例里， PostgreSQL 的主分析器在為 INSERT 準備執行計劃的時候知道字串 'now' 應該解釋成 timestamp 類型，因為 logtable 的目標字段就是該類型。所以，它會在這個時候從這個字串中計算一個常量， 然后在該服務器的整個生存期中的所有 logfunc1 調用中使用這個常量。不消說，這可不是程序員想要的。
在logfunc2里， PostgreSQL 的主分析器并不知道 now 應該轉換成什么類型， 因此它返回一個包含字符串 now 的類型為 text 的數據值。 在隨后給局部變量curtime賦值時， PL/pgSQL解釋器通過調用 text_out和timestamp_in 把這個字符串轉換成 timestamp 類型的變量。 因此，計算出的時戳就會按照程序員希望的那樣在每次執行的時候都更新。
記錄變量的易變性天性在這種結合上提出了一個問題。 在一個記錄變量在語句或者表達式中使用時， 該字段的數據類型在同一個表達式的不同調用期間不能修改， 因為該表達式準備使用的是運行第一次到達該表達式時出現的數據類型。 在寫處理超過一個表的事件的觸發器過程的時候一定要把這個記住。（必要時可以用EXECUTE繞開這個問題。）
6 基本語句
本節以及隨后的一節里，我們描述所有 PL/pgSQL 明確可以理解的語句類型。任何無法識別為這樣類型的語句將被做為 SQL 命令看待，并且被發送到主數據庫引擎執行（在將語句中用到的任何 PL/pgSQL 變量進行替換之后）。因此，舉例來說，SQL INSERT，UPDATE，和 DELETE 命令可能被認為是 PL/pgSQL 語句，但是它們并未在此明確列出。
6.1賦值
給一個變量或行/記錄賦值用下面方法：identIFier = expression;
如上所述，這樣的語句中的表達式是用一個發送到主數據庫引擎的 SQL SELECT 命令計算的。該表達式必須生成單一的數值。
如果表達式的結果數據類型和變量數據類型不一致，或者變量具有已知的尺寸/精度（象 char(20)）， 結果值將隱含地被PL/pgSQL解釋器用結果類型的輸出函數和變量類型的輸入函數轉換。要注意的是，如果結果數值的字串形式不是輸入函數可以接受的形式， 那么這樣做可能導致類型輸入函數產生的運行時錯誤。
例子：
user_id = 20;
tax = subtotal * 0.06;
6.2SELECT INTO
生成多個列（但只有一行）的SELECT命令的結果可以賦予一個記錄變量， 行類型變量，或者一個標量變量的列表。這是用下面方法實現的：
SELECT INTO target select_expressions FROM ...;
或
SELECT select_expressions INTO target FROM ...; -- 推薦這中方法
這里的 target 可以是一個記錄變量， 行變量，或者一個用逗號分隔的簡單變量和記錄/行字段的列表。select_expressions 和命令的剩余部分和普通 SQL 一樣。
請注意這個構造和 PostgreSQL 普通的SELECT INTO構造的解釋是不一樣的， 后者的INTO目標是一個新創建的表。 （如果你想在 PL/pgSQL 函數里從一個SELECT 的結果中創建一個表，那么使用 CREATE TABLE ... AS SELECT 語法。）
如果將一行或者一個變量列表用做目標，那么選出的數值必需精確匹配目標的結構，否則就會產生運行時錯誤。如果目標是一個記錄變量，那么它自動將自己配置成命令結果列的行類型。
除了INTO子句，剩下的SELECT語句和普通的 SQL SELECT命令完全一樣， 并且你可以使用SELECT的全部能力。
INTO 子句幾乎可以出現在 SELECT 語句的任何地方。 習慣上它是跟在 SELECT 后面，就像上面寫的那樣， 或者就在 FROM 之前 — 也就是說，在 select_expressions 列表之前或者之后。
如果命令返回零行，則給目標賦與空值。 如果命令返回多行，那么將第一行賦與目標并拋棄其它的行。（請注意：除非你用了ORDER BY，否則"第一行"是不明確的。）
INTO子句可以出現在SELECT命令里的幾乎任何地方。
在一個 SELECT INTO 語句之后，你可以檢查特殊變量 FOUND來判斷一個賦值是否成功， 也就是說，查詢至少返回一行。例如：
SELECT INTO myrec * FROM emp WHERE empname = myname;
IF NOT FOUND THEN
RAISE EXCEPTION 'employee % not found', myname;
END IF;
要測試一個記錄/行結果是否為空，你可以使用 IS NULL 條件。不過，這個時候沒有任何辦法來判斷是否有額外的行被拋棄。下面是一個例子，處理沒有返回行的情況：
DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;
IF users_rec.homepage IS NULL THEN
RETURN 'http://';
END IF;
END;
6.3執行一個沒有結果的表達式或者命令
有時候我們希望計算一個表達式或者一個命令，但是卻丟棄其結果（通常因為我們經常調用一些存在有用的副作用但是不存在有用結果值的函數）。要在 PL/pgSQL 里干這件事， 你可以使用PERFORM語句：PERFORM query;
這條語句執行一個 query并且丟棄結果。query 的寫法和你平常寫SQL SELECT命令是一樣的，只是把開頭的關鍵字SELECT替換成PERFORM。PL/pgSQL 的變量和平常一樣代換到命令中。同樣，如果命令生成至少一行，那么特殊的變量 FOUND 設置為真，如果沒有生成行，則為假。
注意: 我們可能希望沒有INTO子句的SELECT也能滿足這樣的需要，但是目前可以接受的唯一的方法是PERFORM。
一個例子： PERFORM create_mv('cs_session_page_requests_mv', my_query);
6.4執行動態命令
你經常會希望在你的PL/pgSQL函數里生成動態命令。 也就是那些每次執行的時候都會涉及不同表或不同數據類型的命令。在這樣的情況下，PL/pgSQL 試圖為命令緩沖執行計劃的一般企圖將不再合適。 為了處理這樣的問題，我們提供了EXECUTE語句：
EXECUTE command-string;
這里的 command-string 是一個生成字串（類型為 text）的表達式，該字串包含要執行的命令。 該字串的文本將被傳遞給 SQL 引擎。
請特別注意在該命令字串里將不會發生任何 PL/pgSQL 變量代換。變量的數值必需在構造命令字串的時候插入該字串。
和所有其它在PL/pgSQL里的命令不同， 一個由EXECUTE語句運行的命令在服務器生命期內并不只準備和保存一次。 相反，在該語句每次運行的時候，命令都準備一次。 命令字串可以在過程里動態地生成以便于對各種不同的表和字段進行操作。
來自SELECT命令的結果被EXECUTE拋棄，并且目前EXECUTE 里面還不支持SELECT INTO。所以我們沒有辦法從一個動態創建的 SELECT 中， 使用簡單的 EXECUTE 命令抽取結果。 但是有其它兩種方法可以實現里抽取結果：一種是是使用FOR-IN-EXECUTE方式，另外一種是和 OPEN-FOR-EXECUTE 一起用游標。
要插入到構造出來的查詢中的動態數值也需要特殊的處理，因為他們自己可能包含引號字符。 一個例子（除了特別說明之外，這里我們都假設你使用了美元符包圍）：
EXECUTE 'UPDATE tbl SET '
|| quote_ident(columnname)
|| ' = '
|| quote_literal(newvalue)
|| ' WHERE ...';
這個例子顯示了函數 quote_ident(text) 和 quote_literal(text) 的使用。 為了安全，包含字段和表標識符的變量應該傳遞給函數 quote_ident。 那些包含數值的變量，如果其值在構造出來態命令字串里應外是文本字串，那么應該傳遞給 quote_literal。 它們倆都會采取合適的步驟把輸入文本包圍在單或雙引號里并且對任何嵌入其中的特殊字符進行合適的逃逸處理。
請注意美元符包圍只對包圍固定文本有用。如果想象下面這樣做上面的例子，那就太糟糕了
EXECUTE 'UPDATE tbl SET '
|| quote_ident(colname)
|| ' = $$'
|| newvalue
|| '$$ WHERE ...';
因為如果 newvalue 的內容碰巧有$$，那么這段代碼就有毛病了。 同樣的問題可能出現在你選用的任何美元符包圍分隔符上。 因此，要想安全地包圍事先不知道地文本，你必須使用 quote_literal。
6.5獲取結果狀態
有好幾種方法可以判斷一條命令的效果。第一個方法是使用 GET DIAGNOSTICS，它的形式如下：
GET DIAGNOSTICS variable = item [ , ... ] ;
這條命令允許我們檢索系統狀態標識符。每個 item 是一個關鍵字，表示一個將要賦予該特定變量的狀態值（該變量應該和要接收的數值類型相同）。當前可用的狀態項有 ROW_COUNT， 最后一個發送給 SQL 引擎的 SQL 命令處理的行的數量，和 RESULT_OID，最后一條 SQL 命令插入的最后一行的 OID。請注意 RESULT_OID 只有在一個INSERT命令之后才有用。
一個例子：GET DIAGNOSTICS var_integer = ROW_COUNT;
另外一個判斷命令效果的方法是一個類型為 boolean 的特殊變量 FOUND。 FOUND在每個 PL/pgSQL 函數里開始都為假。它被下列語句設置：
· 一個SELECT INTO語句如果返回一行則設置 FOUND 為真，如果沒有返回行則設置為假;
· 一個PERFORM語句如果生成（或拋棄）一行，則設置 FOUND 為真，如果沒有生成行則為假;
· 如果至少影響了一行，那么UPDATE，INSERT，和DELETE語句設置 FOUND 為真，如果沒有行受影響則為假
· FETCH語句如果返回行則設置 FOUND 為真， 如果不返回行則為假；
· 一個FOR語句如果迭代了一次或多次，則設置 FOUND 為真，否則為假。這個規律適用于所有FOR語句的三種變體 （整數FOR循環，記錄集的FOR循環，以及動態記錄集FOR循環）。 只有在FOR循環退出的時候才設置 FOUND； 在循環執行的內部，FOUND 不被FOR語句修改， 但是在循環體里它可能被其他語句的執行而修改。
FOUND 是每個 PL/pgSQL 里的局部變量；它的任何修改只影響當前的函數。
7 控制結構
控制結構可能是 PL/pgSQL 中最有用的（以及最重要）的部分了。利用 PL/pgSQL 的控制結構， 你可以以非常靈活而且強大的方法操縱 PostgreSQL 的數據。
7.1從函數返回
有兩個命令可以用來從函數中返回數據：RETURN 和 RETURN NEXT。
· RETURN
RETURN expression;
帶表達式的 RETURN 是用于終止函數, 然后 expression 的值返回給調用者。
如果返回標量類型，那么可以使用任何表達式。表達式的類型將被自動轉換成函數的返回類型， 就像我們在賦值中描述的那樣。 要返回一個復合（行）數值，你必須寫一個記錄或者行變量做 expression。
一個函數的返回值不能是未定義。如果控制到達了函數的最頂層的塊而沒有碰到一個 RETURN 語句， 那么它就會發生一個錯誤。
請注意如果你聲明了該函數返回 void，那么仍然必須聲明 RETURN 語句；但是，跟在 RETURN 后面的表達式是可選的，并且在任何情況下都會被忽略。
· RETURN NEXT
RETURN NEXT expression;
如果一個 PL/pgSQL 函數聲明為返回 SETOF sometype， 那么遵循的過程則略有不同。在這種情況下，要返回的獨立的項是在 RETURN NEXT 命令里聲明的，然后最后有一個不帶參數的 RETURN 命令用于告訴我們這個函數已經完成執行了。 RETURN NEXT 可以用于標量和復合數據類型；對于后者，將返回一個完整的結果"表"。
使用 RETURN NEXT 的函數應該按照下面的風格調用：SELECT * FROM some_func();
也就是說，這個函數是用做FROM子句里面的一個表數據源的。
RETURN NEXT 實際上并不從函數中返回； 它只是簡單地把表達式的值保存起來。 然后執行繼續執行 PL/pgSQL 函數里的下一條語句。 隨著后繼的 RETURN NEXT 命令的執行， 結果集就建立起來了。最后的一個不需要參數的 RETURN， 導致控制退出該函數。
注意: 目前的 PL/pgSQL 的 RETURN NEXT 實現在從函數返回之前把整個結果集都保存起來，就象上面描述的那樣。 這意味著如果一個 PL/pgSQL 函數生成一個非常大的結果集， 性能可能會很差：數據將被寫到磁盤上以避免內存耗盡， 但是函數在完成整個結果集的生成之前不會退出。將來的 PL/pgSQL 版本可能會允許用戶定義沒有這樣限制的返回集合的函數。 目前，數據開始向磁盤里寫的時刻是由配置變量 work_mem 控制的。 擁有足夠內存的管理員如果想在內存里存儲更大的結果集， 則可以考慮把這個參數增大一些。
7.2條件
IF 語句讓你可以根據某種條件執行命令。 PL/pgSQL有五種形式的IF：
· IF ... THEN
· IF ... THEN ... ELSE
· IF ... THEN ... ELSE IF
· IF ... THEN ... ELSIF ... THEN ... ELSE
· IF ... THEN ... ELSEIF ... THEN ... ELSE
1） IF-THEN
IF boolean-expression THEN
Statements
END IF;
IF-THEN語句是IF的最簡單形式。如果條件為真， 在THEN和END IF之間的語句將被執行。 否則，將忽略它們。
例子：
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
2） IF ... THEN ... ELSE
IF boolean-expression THEN
Statements
ELSE
Statements
END IF;
IF-THEN-ELSE語句增加了IF-THEN的分支， 讓你可以聲明在條件計算結果為假的時候執行的語句。
例子：
IF parentid IS NULL OR parentid = '' THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;
3） IF ... THEN ... ELSE IF
IF語句可以嵌套并且在下面的例子中：
IF demo_row.sex = 'm' THEN
pretty_sex := 'man';
ELSE
IF demo_row.sex = 'f' THEN
pretty_sex := 'woman';
END IF;
END IF;
如果你使用這種形式，那么你實際上就是在另外一個IF語句的ELSE 部分嵌套了一個IF語句．因此你需要一個END IF語句 給每個嵌套的IF，另外還要一個給父IF-ELSE用． 這么干是可以的，但是如果我們有太多候選項需要檢查，那么就會變得很乏味．因此有下面的形式。
4） IF ... THEN ... ELSIF ... THEN ... ELSE
IF boolean-expression THEN
Statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements]
END IF;
IF-THEN-ELSIF-ELSE提供了一種更方便的方法用于在一條語句中檢查許多候選條件。 形式上它和嵌套的IF-THEN-ELSE-IF-THEN命令相同， 但是只需要一個END IF。
這里是一個例子：
IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- 另外一個唯一的可能是它是空值
result := 'NULL';
END IF;
5） IF ... THEN ... ELSEIF ... THEN ... ELSE
ELSEIF 是 ELSIF 的別名。
7.3簡單循環
使用LOOP，WHILE，FOR 和 EXIT 語句，你可以控制你的 PL/pgSQL 函數重復一系列命令。
（1） LOOP
[<<label>>]
LOOP
Statements
END LOOP;
LOOP 定義一個無條件的循環，無限循環，直到由EXIT或者RETURN語句終止。 可選的標簽可以由EXIT語句使用，用于在嵌套循環中聲明應該結束哪一層循環。
（2） EXIT
EXIT [label] [ WHEN expression];
如果沒有給出 label， 那么退出最內層的循環，然后執行跟在END LOOP后面的語句。 如果給出 label， 那么它必須是當前或者更高層的嵌套循環塊或者塊的標簽。 然后該命名塊或者循環就會終止，而控制落到對應循環/塊的 END 語句后面的語句上。
如果出現了WHEN，循環退出只發生在聲明的條件為真的時候， 否則控制會落到EXIT后面的語句上。
EXIT 可以用于在所有的循環類型中提前退出； 它并不僅限于在無條件循環中使用。
例子：
LOOP
-- 一些計算
IF count > 0 THEN
EXIT; -- exit loop
END IF;
END LOOP;
BEGIN
-- 一些計算
IF stocks > 100000 THEN
EXIT; -- 導致從 BEGIN 塊里退出
END IF;
END;
（3） WHILE
[<<label>>]
WHILE expression LOOP
Statements
END LOOP;
只要條件表達式為真，WHILE語句就會不停在一系列語句上進行循環. 條件是在每次進入循環體的時候檢查的.
比如:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- 可以在這里做些計算
END LOOP;
（4） FOR
[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
Statements
END LOOP;
這種形式的FOR對一定范圍的整數數值進行迭代的循環。 變量name 會自動定義為integer類型并且只在循環里存在。 給出范圍上下界的兩個表達式在進入循環的時候計算一次。 迭代步進值總是為 1，但如果聲明了REVERSE就是 -1。
一些整數FOR循環的例子∶
FOR i IN 1..10 LOOP
-- 這里可以放一些表達式
RAISE NOTICE 'i IS %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
-- 這里可以放一些表達式
END LOOP;
如果下界大于上界（或者是在 REVERSE 情況下是小于），那么循環體將完全不被執行。 而且不會拋出任何錯誤。
7.4遍歷命令結構
使用不同類型的FOR循環，你可以遍歷一個命令的結果并且相應的操作哪些數據。語法是：
[<<label>>]
FOR record_or_row IN query LOOP
Statements
END LOOP;
這里的記錄或者行變量將相繼被賦予所有來自query（必須是一條 SELECT 命令）的行， 并且循環體將為每行執行一次。下面是一個例子：
CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized views...');
FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key
LOOP
-- 現在 "mviews" 里有了一條來自 cs_materialized_views 的記錄
PERFORM cs_log('Refreshing materialized view '
|| quote_ident(mviews.mv_name) || ' ...');
EXECUTE 'TRUNCATE TABLE ' || quote_ident(mviews.mv_name);
EXECUTE 'INSERT INTO ' || quote_ident(mview.mv_name) || ' ' || mview.mv_query;
END LOOP;
PERFORM cs_log('Done refreshing materialized views.');
RETURN 1;
END;
$ LANGUAGE plpgsql;
如果循環是用一個EXIT語句終止的，那么在循環之后你仍然可以訪問最后賦值的行。
FOR-IN-EXECUTE語句是遍歷所有行的另外一種方法：
[<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP
Statements
END LOOP;
這個例子類似前面的形式，只不過源SELECT語句聲明為了一個字串表達式， 這樣它在每次進入FOR循環的時候都會重新計算和生成執行計劃。 這樣就允許程序員在一個預先規劃好了的命令所獲得的速度，和一個動態命令所獲得的靈活性（就象一個簡單的EXECUTE語句那樣）之間進行選擇。
注意: PL/pgSQL 分析器目前區分兩種類型的FOR循環（整數或者返回記錄的）： 方法是檢查是否有任何 .. 出現在 IN 和 LOOP 之間的圓括弧之外。 如果沒有看到 ..，那么這個循環就是在數據行上的循環。 如果誤敲了 .. 就很可能會導致像下面這樣的錯誤信息： "loop variable of loop over rows must be a record or row variable"， 而不是我們以為會看到的簡單的語法錯誤。
7.5捕獲錯誤
缺省時，一個在 PL/pgSQL 函數里發生的錯誤退出函數的執行，并且實際上是其周圍的事務也會退出。你可以使用一個帶有 EXCEPTION 子句的 BEGIN 塊捕獲錯誤并且從中恢復。其語法是正常的 BEGIN 塊語法的一個擴展：
[ <<label>> ]
[ DECLARE
declarations]
BEGIN
Statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
[ WHEN condition [ OR condition ... ] THEN
handler_statements
... ]
END;
如果沒有發生錯誤，這種形式的塊只是簡單地執行所有 statements， 但是如果在 statements 里發生了一個錯誤， 則對 statements 的進一步處理將廢棄，控制傳遞到了 EXCEPTION 列表。 系統搜索這個列表，尋找匹配發生的錯誤的第一個元素。如果找到匹配，則執行對應的 handler_statements，然后控制傳遞到 END 之后的下一個語句。 如果沒有找到匹配，該錯誤就會廣播出去，就好像根本沒有 EXCEPTION 子句一樣： 該錯誤可以被一個包圍塊用 EXCEPTION 捕獲，如果沒有包圍塊，則退出函數的處理。
condition 名字可以是【附錄A】里顯示的任何名字。 一個范疇名匹配任意該范疇里的錯誤。特殊的條件名 OTHERS 匹配除了 QUERY_CANCELED 之外的所有錯誤類型。（我們可以用名字捕獲 QUERY_CANCELED，不過通常是不明智的。）條件名是大小寫無關的。
如果在選中的 handler_statements 里發生了新錯誤， 那么它不能被這個 EXCEPTION 子句捕獲，而是傳播出去。 一個外層的 EXCEPTION 子句可以捕獲它。
如果一個錯誤被 EXCEPTION 捕獲，PL/pgSQL 函數的局部變量保持錯誤發生的時候的原值，但是所有該塊中想固化在數據庫中的狀態都回滾。作為一個例子，讓我們看看下面片斷：
INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
當控制到達給 y 賦值的地方的時候，它會帶著一個 division_by_zero 錯誤失敗。 這個錯誤將被 EXCEPTION 子句波獲。而在 RETURN 語句里返回的數值將是 x 的增量值。 但是，在該塊之前的 INSERT 將不會回滾，因此最終的結果是數據庫包含 Tom Jones 而 不是 Joe Jones。
提示: 進入和退出一個包含 EXCEPTION 子句的塊要比不包含的塊開銷大的多。 因此，不必要的時候不要使用 EXCEPTION.
8 游標
如果不想一次執行整個命令，我們可以設置一個封裝該命令的 游標，然后每次讀取幾行命令結果。 這么干的一個原因是在結果包含數量非常大的行時避免內存耗盡。 （當然，PL/pgSQL 用戶通常不必擔心這個，因為 FOR 循環自動在內部使用一個游標以避免內存問題。） 一個更有趣的用法是某個函數可以返回一個它創建的游標的引用，這樣就允許調用者讀取各行。這就提供了一種從函數返回一個結果集的手段。
8.1聲明游標變量
所有在 PL/pgSQL 里對游標的訪問都是通過游標變量實現的，它總是特殊的數據類型 refcursor。 創建一個游標變量的一個方法是把它聲明為一個類型為 refcursor 的變量。 另外一個方法是使用游標聲明語法，通常是下面這樣：
name CURSOR [ ( arguments ) ] FOR query ;
如果有 arguments，那么它是一個逗號分隔的name datatype配對的列表，它們定義那些將會用參數值替換掉的所給出命令中的名字。 實際用于代換這些名字的數值將在后面聲明，在游標打開之后。
幾個例子∶
DECLARE
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
所有這三個變量都是類型為 refcursor， 但是第一個可以用于任何命令，而第二個已經綁定 了一個聲明完整的命令，最后一個是綁定了一個帶參數的命令。 （key 將在游標打開的時候被代換成一個整數。） 變量 curs1 可以稱之為未綁定的， 因為它沒有和任何查詢相綁定。
8.2打開游標
在你使用游標檢索行之前，你必需憲打開它。（這是和 SQL 命令 DECLARE CURSOR 相等的操作。） PL/pgSQL 有三種形式的OPEN語句， 兩種用于未綁定的游標變量，另外一種用于綁定的游標變量。
· OPEN FOR SELECT
OPEN unbound_cursor FOR SELECT ...;
該游標變量打開，并且執行給出的查詢。游標不能是已經打開的，并且它必需是聲明為一個未綁定的游標（也就事說，聲明為一個簡單的 refcursor 變量）。 SELECT 命令是和其它在 PL/pgSQL 里的 SELECT 命令平等對待的：先代換 PL/pgSQL 的變量名，而且執行計劃為將來可能的復用緩存起來。
例子：OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
· OPEN FOR EXECUTE
OPEN unbound_cursor FOR EXECUTE query-string;
打開游標變量并且執行給出的查詢。游標不能是已打開的，并且必須聲明為一個未綁定的游標（也就是說，是一個簡單的 refcursor 變量）。命令是用和那些用于 EXECUTE 命令一樣的方法聲明的字串表達式， 這樣，我們就有了命令可以在兩次運行間發生變化的靈活性。
例子：OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
· 打開一個綁定的游標
OPEN bound_cursor [ ( argument_values ) ];
這種形式的OPEN用于打開一個游標變量，該游標變量的命令是在聲明的時候和它綁定在一起的。 游標不能是已經打開的。 當且僅當該游標聲明為接受參數的時候，語句中才必需出現一個實際參數值表達式的列表。這些值將代換到命令中。一個綁定的游標的命令計劃總是認為可緩沖的 -- 這種情況下沒有等效的EXECUTE。
例子：
OPEN curs2;
OPEN curs3(42);
8.3使用游標
一旦你已經打開了一個游標，那么你就可以用這里描述的語句操作它。
這些操作不需要發生在和打開該游標開始操作的同一個函數里。你可以從函數里返回一個 refcursor 數值，然后讓調用者操作該游標。（在內部，refcursor值只是一個包含該游標命令的活躍查詢的信使的字串名。這個名字可以傳來傳去，可以賦予其它refcursor 變量等等，也不用擔心擾亂信使。）
所有信使在事務的結尾都會隱含地關閉。因此一個refcursor值只能在該事務結束前用于引用一個打開的游標。
1> FETCH
FETCH cursor INTO target;
FETCH從游標中檢索下一行到目標中，目標可以是一個行變量，一個記錄變量，或者是一個逗號分隔的普通變量的列表，就象SELECT INTO里一樣。 和SELECT INTO一樣，你可以使用特殊變量FOUND檢查是否檢索出一個行。
例子：
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
2> CLOSE
CLOSE cursor;
CLOSE關閉支撐在一個打開的游標下面的信使。這樣我們就可以在事務結束之前施放資源，或者釋放掉該游標變量，用于稍后再次打開。
例子： CLOSE curs1;
3> 返回游標
PL/pgSQL 函數可以向調用者返回游標。 這個功能用于從函數里返回多行或多列。要想這么做的時候， 該函數打開游標并且把該游標的名字返回給調用者。 調用者然后從游標里FETCH行。 游標可以由調用者關閉，或者是在事務結束的時候自動關閉。
函數返回的游標名可以由調用者聲明或者自動生成。要聲明一個信使的名字，只要再打開游標之前，給 refcursor 變量賦予一個字串就可以了。 refcursor 變量的字串值將被 OPEN 當作下層的信使的名字使用。 不過，如果 refcursor 變量是空，那么 OPEN 將自動生成一個和現有信使不沖突的名字， 然后將它賦予 refcursor 變量。
注意: 一個綁定的游標變量其名字初始化為對應的字串值，因此信使的名字和游標變量名同名，除非程序員再打開游標之前通過賦值覆蓋了這個名字。但是一個未綁定的游標變量初始化的時候缺省是空， 因此它會收到一個自動生成的唯一的名字，除非被覆蓋。
下面的例子顯示了一個調用者聲明游標名字的方法：
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');
CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
下面的例子使用了自動生成的游標名：
CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT col FROM test;
RETURN ref;
END;
' LANGUAGE plpgsql;
BEGIN;
SELECT reffunc2();
<unnamed cursor 1>
(1 row)
FETCH ALL IN "<unnamed cursor 1>";
COMMIT;
下面的例子顯示了從一個函數里返回多個游標的方法：
CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM table_1;
RETURN NEXT $1;
OPEN $2 FOR SELECT * FROM table_2;
RETURN NEXT $2;
RETURN;
END;
$$ LANGUAGE plpgsql;
-- 需要在事務里使用游標。
BEGIN;
SELECT * FROM myfunc('a', 'b');
FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;
9 錯誤和消息
利用RAISE語句報告信息以及拋出錯誤。
RAISE level 'format' [, variable [, ...]];
可能的級別有DEBUG(向服務器日志寫信息)， LOG（向服務器日志寫信息，優先級更高），INFO， NOTICE和WARNING （把信息寫到服務器日志以及轉發到客戶端應用,優先級逐步升高）和 EXCEPTION拋出一個錯誤（通常退出當前事務）。 某個優先級別的信息是報告給客戶端還是寫到服務器日志， 還是兩個都做是由 log_min_messages和 client_min_messages配置變量控制的。
在格式字串里，% 被下一個可選參數的外部表現形式代替。 要發出一個文本的 %，你要寫 %%。 請注意可選的參數必須是簡單的變量，不能是表達式，而且格式必須是一個簡單的字串文本。
在這個例子里，v_job_id的值將代替字串中的%：
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;
這個例子將會帶著給出的錯誤信息退出事務：
RAISE EXCEPTION 'Inexistent ID --> %',user_id;
RAISE EXCEPTION 目前總是生成同樣的 SQLSTATE 代碼，P0001， 不管調用它的信息是什么。我們可以用 EXCEPTION ... WHEN RAISE_EXCEPTION THEN ... 捕獲這樣的例外，但是我們無法從一個 RAISE 里告訴另外一個相關的狀態。
10 觸發器過程
PL/pgSQL 可以用于定義觸發器過程。 一個觸發器過程是用 CREATE FUNCTION 命令創建的， 創建的形式是一個不接受參數并且返回 trigger 類型的函數。 請注意該函數即使在 CREATE TRIGGER 聲明里聲明為準備接受參數， 它也必需聲明為無參數 — 觸發器的參數是通過 TG_ARGV 傳遞的，下面有描述。
在一個 PL/pgSQL 函數當做觸發器調用的時候，系統會在頂層的聲明段里自動創建幾個特殊變量。有如下這些：
· NEW 數據類型是 RECORD； 該變量為INSERT/UPDATE 操作時保存行（ROW）一級的觸發器新的數據庫行。 在語句級別的觸發器里，這個變量是 NULL;
· OLD 數據類型是 RECORD； 該變量為 INSERT/UPDATE 操作時保存行（ROW）一級的觸發器新的數據庫行。 在語句級別的觸發器里，這個變量是 NULL;
· TG_NAME 數據類型是 name；該變量包含實際觸發的觸發器名。 Fired;
· TG_WHEN 數據類型是 text；是一個由觸發器定義決定的字符串，要么是 BEFORE 要么是AFTER。
· TG_LEVEL 數據類型是 text；是一個由觸發器定義決定的字符串，要么是 ROW 要么是 STATEMENT。
· TG_OP 數據類型是 text；是一個說明觸發觸發器的操作的字符串，可以是 INSERT，UPDATE 或者 DELETE。
· TG_RELID 數據類型是 oid；是導致觸發器調用的表的對象標識（OID）。
· TG_RELNAME 數據類型是 name；是激活觸發器調用的表的名稱。
· TG_NARGS 數據類型是 integer； 是在CREATE TRIGGER 語句里面賦予觸發器過程的參數的個數。
· TG_ARGV[] 數據類型是 text 的數組；是 CREATE TRIGGER語句里的參數。 下標從 0 開始記數．非法下標（小于 0 或者大于等于 tg_nargs）導致返回一個 NULL 值。
一個觸發器函數必須返回 NULL 或者是 一個與導致觸發器運行的表的記錄/行完全一樣的結構的數據。
因BEFORE觸發的行級別的的觸發器可以返回一個 NULL，告訴觸發器管理器忽略對該行剩下的操作（也就是說，隨后的觸發器將不再執行，并且不會對該行產生INSERT/UPDATE/DELETE動作）。 如果返回了一個非 NULL 的行，那么將繼續對該行數值進行處理。 請注意，返回一個和原來的NEW不同的行數值將修改那個將插入或更新的行。 我們可能用一個值直接代替NEW里的某個數值并且返回之，或者我們也可以構建一個完全新的記錄/行再返回。
BEFORE 或者 AFTER語句級別的觸發器， 或者一個AFTER 行級別的觸發器的返回值將總是被忽略； 它們也可以返回 NULL 來忽略返回值。不過，任何這種類型的觸發器仍然可以通過拋出一個錯誤來退出整個觸發器操作。
下面的例子觸發器的作用是：任何時候表中插入或更新了行，當前的用戶名和時間都記錄入行中。 并且它保證給出了雇員名稱并且薪水是一個正數。
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp () RETURNS trigger AS $emp_stamp$
BEGIN
-- 檢查是否給出了 empname 和 salary
IF NEW.empname ISNULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- 我們必須付帳給誰？
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- 記住何時何人的薪水被修改了
NEW.last_date := 'now';
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
另外一個向表里記錄變化的方法涉及創建一個新表，然后為后來發生的每次插入、更新或者刪除動作保存一行。這個方法可以當作對一個表的審計。下面顯示了一個 PL/pgSQL 寫的審計觸發器過程的例子。
這個例子觸發器保證了在 emp 表上的任何插入， 更新或者刪除動作都被記錄到了 emp_audit 表里（也就是，審計）。 當前時間和用戶名會被記錄到數據行里，以及還有執行的操作。
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS
emp_audit$
BEGIN
--
-- 在 emp_audit 里創建一行，反映對 emp 的操作，
-- 使用特殊變量 TG_OP 獲取操作類型。
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
RETURN NEW;
END IF;
RETURN NULL; -- 忽略結果，因為它是個 AFTER 觸發器
END;
$emp_audit$ language plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_emp_audit()
觸發器的一個用途是維持另外一個表的概要。生成的概要可以用于在某些查詢中代替原始表 — 通常可以大大縮小運行時間。 這個技巧經常用于數據倉庫，這個時候，需要測量的表（叫事實表）可能會非常巨大。 下面演示了一個 PL/pgSQL 觸發器過程的例子， 它為某個數據倉庫的一個事實表維護一個概要表。
下面的模式有一部分是基于 Ralph Kimball 的The Data Warehouse Toolkit 里面的 Grocery Store 例子。
--
-- 主表 - 時間維以及銷售事實。
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- 摘要表 - 根據時間的銷售。
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- 在 UPDATE，INSERT，DELETE 的時候根新概要字段的函數和觸發器。
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS
$maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- 計算增/減量。
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- 禁止改變 time_key 的更新 -
-- （可能并不是很強制，因為 DELETE + INSERT 是大多數可能
-- 產生的修改）。
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- 用新數值更新概要行。
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
-- There might have been no row with this time_key (e.g new data!).
IF (NOT FOUND) THEN
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXCEPTION
--
-- 捕獲兩個事務維一個新 time_key 增加數據的沖突條件
--
WHEN UNIQUE_VIOLATION THEN
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
END;
END IF;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime
posted on 2008-07-16 18:15
henry1451 閱讀(5581)
評論(0) 編輯 收藏