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