一、概述：
PL/pgSQL函數在第一次被調用時，其函數內的源代碼(文本)將被解析為二進制指令樹，但是函數內的表達式和SQL命令只有在首次用到它們的時候，PL/pgSQL解釋器才會為其創建一個準備好的執行規劃，隨后對該表達式或SQL命令的訪問都將使用該規劃。如果在一個條件語句中，有部分SQL命令或表達式沒有被用到，那么PL/pgSQL解釋器在本次調用中將不會為其準備執行規劃，這樣的好處是可以有效地減少為PL/pgSQL函數里的語句生成分析和執行規劃的總時間，然而缺點是某些表達式或SQL命令中的錯誤只有在其被執行到的時候才能發現。
由于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函數的OID。要解決該問題，可以選擇重建populate函數，或者重新登錄建立新的會話，以使PostgreSQL重新編譯該函數。要想規避此類問題的發生，在重建my_function時可以使用CREATE OR REPLACE FUNCTION命令。
鑒于以上規則，在PL/pgSQL里直接出現的SQL命令必須在每次執行時均引用相同的表和字段，換句話說，不能將函數的參數用作SQL命令的表名或字段名。如果想繞開該限制，可以考慮使用PL/pgSQL中的EXECUTE語句動態地構造命令，由此換來的代價是每次執行時都要構造一個新的命令計劃。
使用PL/pgSQL函數的一個非常重要的優勢是可以提高程序的執行效率，由于原有的SQL調用不得不在客戶端與服務器之間反復傳遞數據，這樣不僅增加了進程間通訊所產生的開銷，而且也會大大增加網絡IO的開銷。
二、PL/pgSQL的結構：
PL/pgSQL是一種塊結構語言，函數定義的所有文本都必須在一個塊內，其中塊中的每個聲明和每條語句都是以分號結束，如果某一子塊在另外一個塊內，那么該子塊的END關鍵字后面必須以分號結束，不過對于函數體的最后一個END關鍵字，分號可以省略，如：
復制代碼 代碼如下:
[ label>> ]
[ DECLARE declarations ]
BEGIN
statements
END [ label ];
在PL/pgSQL中有兩種注釋類型，雙破折號(--)表示單行注釋。/* */表示多行注釋，該注釋類型的規則等同于C語言中的多行注釋。
在語句塊前面的聲明段中定義的變量在每次進入語句塊(BEGIN)時都會將聲明的變量初始化為它們的缺省值，而不是每次函數調用時初始化一次。如：
復制代碼 代碼如下:
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;
#執行該函數以進一步觀察其執行的結果。
postgres=# select somefunc();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Quantity here is 50
somefunc
----------
50
(1 row)
最后需要說明的是，目前版本的PostgreSQL并不支持嵌套事務，函數中的事物總是由外層命令(函數的調用者)來控制的，它們本身無法開始或提交事務。
三、聲明：
所有在塊里使用的變量都必須在塊的聲明段里先進行聲明，唯一的例外是FOR循環里的循環計數變量，該變量被自動聲明為整型。變量聲明的語法如下：
復制代碼 代碼如下:
variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
1). SQL中的數據類型均可作為PL/pgSQL變量的數據類型，如integer、varchar和char等。
2). 如果給出了DEFAULT子句，該變量在進入BEGIN塊時將被初始化為該缺省值，否則被初始化為SQL空值。缺省值是在每次進入該塊時進行計算的。因此，如果把now()賦予一個類型為timestamp的變量，那么該變量的缺省值將為函數實際調用時的時間，而不是函數預編譯時的時間。
3). CONSTANT選項是為了避免該變量在進入BEGIN塊后被重新賦值，以保證該變量為常量。
4). 如果聲明了NOT NULL，那么賦予NULL數值給該變量將導致一個運行時錯誤。因此所有聲明為NOT NULL的變量也必須在聲明時定義一個非空的缺省值。
1. 函數參數的別名：
傳遞給函數的參數都是用$1、$2這樣的標識符來表示的。為了增加可讀性，我們可以為其聲明別名。之后別名和數字標識符均可指向該參數值，見如下示例：
1). 在函數聲明的同時給出參數變量名。
復制代碼 代碼如下:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
2). 在聲明段中為參數變量定義別名。
復制代碼 代碼如下:
CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
3). 對于輸出參數而言，我們仍然可以遵守1)和2)中的規則。
復制代碼 代碼如下:
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
4). 如果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;
2. 拷貝類型：
見如下形式的變量聲明：
復制代碼 代碼如下:
variable%TYPE
%TYPE表示一個變量或表字段的數據類型，PL/pgSQL允許通過該方式聲明一個變量，其類型等同于variable或表字段的數據類型，見如下示例：
復制代碼 代碼如下:
user_id users.user_id%TYPE;
在上面的例子中，變量user_id的數據類型等同于users表中user_id字段的類型。
通過使用%TYPE，一旦引用的變量類型今后發生改變，我們也無需修改該變量的類型聲明。最后需要說明的是，我們可以在函數的參數和返回值中使用該方式的類型聲明。
3. 行類型：
見如下形式的變量聲明：
復制代碼 代碼如下:
name table_name%ROWTYPE;
name composite_type_name;
table_name%ROWTYPE表示指定表的行類型，我們在創建一個表的時候，PostgreSQL也會隨之創建出一個與之相應的復合類型，該類型名等同于表名，因此，我們可以通過以上兩種方式來聲明行類型的變量。由此方式聲明的變量，可以保存SELECT返回結果中的一行。如果要訪問變量中的某個域字段，可以使用點表示法，如rowvar.field，但是行類型的變量只能訪問自定義字段，無法訪問系統提供的隱含字段，如OID等。對于函數的參數，我們只能使用復合類型標識變量的數據類型。最后需要說明的是，推薦使用%ROWTYPE的聲明方式，這樣可以具有更好的可移植性，因為在Oracle的PL/SQL中也存在相同的概念，其聲明方式也為%ROWTYPE。見如下示例：
復制代碼 代碼如下:
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
t2_row table2%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;
4. 記錄類型：
見如下形式的變量聲明：
復制代碼 代碼如下:
name RECORD;
記錄變量類似于行類型變量，但是它們沒有預定義的結構，只能通過SELECT或FOR命令來獲取實際的行結構，因此記錄變量在被初始化之前無法訪問，否則將引發運行時錯誤。
注：RECORD不是真正的數據類型，只是一個占位符。
四、基本語句：
1. 賦值：
PL/pgSQL中賦值語句的形式為：identIFier := expression，等號兩端的變量和表達式的類型或者一致，或者可以通過PostgreSQL的轉換規則進行轉換，否則將會導致運行時錯誤，見如下示例：
復制代碼 代碼如下:
user_id := 20;
tax := subtotal * 0.06;
2. SELECT INTO：
通過該語句可以為記錄變量或行類型變量進行賦值，其表現形式為：SELECT INTO target select_expressions FROM ...，該賦值方式一次只能賦值一個變量。表達式中的target可以表示為是一個記錄變量、行變量，或者是一組用逗號分隔的簡單變量和記錄/行字段的列表。select_expressions以及剩余部分和普通SQL一樣。
如果將一行或者一個變量列表用做目標，那么選出的數值必需精確匹配目標的結構，否則就會產生運行時錯誤。如果目標是一個記錄變量，那么它自動將自己構造成命令結果列的行類型。如果命令返回零行，目標被賦予空值。如果命令返回多行，那么將只有第一行被賦予目標，其它行將被忽略。在執行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;
3. 執行一個沒有結果的表達式或者命令：
在調用一個表達式或執行一個命令時，如果對其返回的結果不感興趣，可以考慮使用PERFORM語句：PERFORM query，該語句將執行PERFORM之后的命令并忽略其返回的結果。其中query的寫法和普通的SQL SELECT命令是一樣的，只是把開頭的關鍵字SELECT替換成PERFORM，如：
復制代碼 代碼如下:
PERFORM create_mv('cs_session_page_requests_mv', my_query);
4. 執行動態命令：
如果在PL/pgSQL函數中操作的表或數據類型在每次調用該函數時都可能會發生變化，在這樣的情況下，可以考慮使用PL/pgSQL提供的EXECUTE語句：EXECUTE command-string [ INTO target ]，其中command-string是用一段文本表示的表達式，它包含要執行的命令。而target是一個記錄變量、行變量或者一組用逗號分隔的簡單變量和記錄/行域的列表。這里需要特別注意的是，該命令字符串將不會發生任何PL/pgSQL變量代換，變量的數值必需在構造命令字符串時插入到該字符串中。
和所有其它PL/pgSQL命令不同的是，一個由EXECUTE語句運行的命令在服務器內并不會只prepare和保存一次。相反，該語句在每次運行的時候，命令都會prepare一次。因此命令字符串可以在函數里動態的生成以便于對各種不同的表和字段進行操作，從而提高函數的靈活性。然而由此換來的卻是性能上的折損。見如下示例：
復制代碼 代碼如下:
EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);
五、控制結構：
1. 函數返回：
1). RETURN expression
該表達式用于終止當前的函數，然后再將expression的值返回給調用者。如果返回簡單類型，那么可以使用任何表達式，同時表達式的類型也將被自動轉換成函數的返回類型，就像我們在賦值中描述的那樣。如果要返回一個復合類型的數值，則必須讓表達式返回記錄或者匹配的行變量。
2). RETURN NEXT expression
如果PL/pgSQL函數聲明為返回SETOF sometype，其行記錄是通過RETURN NEXT命令進行填充的，直到執行到不帶參數的RETURN時才表示該函數結束。因此對于RETURN NEXT而言，它實際上并不從函數中返回，只是簡單地把表達式的值保存起來，然后繼續執行PL/pgSQL函數里的下一條語句。隨著RETURN NEXT命令的迭代執行，結果集最終被建立起來。該類函數的調用方式如下：
SELECT * FROM some_func();
它被放在FROM子句中作為數據源使用。最后需要指出的是，如果結果集數量很大，那么通過該種方式來構建結果集將會導致極大的性能損失。
2. 條件：
在PL/pgSQL中有以下三種形式的條件語句。
1). IF-THEN
復制代碼 代碼如下:
IF boolean-expression THEN
statements
END IF;
2). IF-THEN-ELSE
復制代碼 代碼如下:
IF boolean-expression THEN
statements
ELSE
statements
END IF;
3). IF-THEN-ELSIF-ELSE
復制代碼 代碼如下:
IF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSIF boolean-expression THEN
statements
ELSE
statements
END IF;
關于條件語句，這里就不在做過多的贅述了。
3. 循環：
1). LOOP
復制代碼 代碼如下:
LOOP
statements
END LOOP [ label ];
LOOP定義一個無條件的循環，直到由EXIT或者RETURN語句終止?？蛇x的label可以由EXIT和CONTINUE語句使用，用于在嵌套循環中聲明應該應用于哪一層循環。
2). EXIT
復制代碼 代碼如下:
EXIT [ label ] [ WHEN expression ];
如果沒有給出label，就退出最內層的循環，然后執行跟在END LOOP后面的語句。如果給出label，它必須是當前或更高層的嵌套循環塊或語句塊的標簽。之后該命名塊或循環就會終止，而控制則直接轉到對應循環/塊的END語句后面的語句上。
如果聲明了WHEN，EXIT命令只有在expression為真時才被執行，否則將直接執行EXIT后面的語句。見如下示例：
復制代碼 代碼如下:
LOOP
-- do something
EXIT WHEN count > 0;
END LOOP;
3). CONTINUE
復制代碼 代碼如下:
CONTINUE [ label ] [ WHEN expression ];
如果沒有給出label，CONTINUE就會跳到最內層循環的開始處，重新進行判斷，以決定是否繼續執行循環內的語句。如果指定label，則跳到該label所在的循環開始處。如果聲明了WHEN，CONTINUE命令只有在expression為真時才被執行，否則將直接執行CONTINUE后面的語句。見如下示例：
復制代碼 代碼如下:
LOOP
-- do something
EXIT WHEN count > 100;
CONTINUE WHEN count 50;
END LOOP;
4). WHILE
復制代碼 代碼如下:
[ label>> ]
WHILE expression LOOP
statements
END LOOP [ label ];
只要條件表達式為真，其塊內的語句就會被循環執行。條件是在每次進入循環體時進行判斷的。見如下示例：
復制代碼 代碼如下:
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
--do something
END LOOP;
5). FOR
復制代碼 代碼如下:
[ label>> ]
FOR name IN [ REVERSE ] expression .. expression LOOP
statements
END LOOP [ label ];
變量name自動被定義為integer類型，其作用域僅為FOR循環的塊內。表示范圍上下界的兩個表達式只在進入循環時計算一次。每次迭代name值自增1，但如果聲明了REVERSE，name變量在每次迭代中將自減1，見如下示例：
復制代碼 代碼如下:
FOR i IN 1..10 LOOP
--do something
RAISE NOTICE 'i IS %', i;
END LOOP;
FOR i IN REVERSE 10..1 LOOP
--do something
END LOOP;
4. 遍歷命令結果：
復制代碼 代碼如下:
[ label>> ]
FOR record_or_row IN query LOOP
statements
END LOOP [ label ];
這是另外一種形式的FOR循環，在該循環中可以遍歷命令的結果并操作相應的數據，見如下示例：
復制代碼 代碼如下:
FOR rec IN SELECT * FROM some_table LOOP
PERFORM some_func(rec.one_col);
END LOOP;
PL/pgSQL還提供了另外一種遍歷命令結果的方式，和上面的方式相比，唯一的差別是該方式將SELECT語句存于字符串文本中，然后再交由EXECUTE命令動態的執行。和前一種方式相比，該方式的靈活性更高，但是效率較低。
復制代碼 代碼如下:
[ label>> ]
FOR record_or_row IN EXECUTE text_expression LOOP
statements
END LOOP [ label ];
5. 異常捕獲：
在PL/pgSQL函數中，如果沒有異常捕獲，函數會在發生錯誤時直接退出，與其相關的事物也會隨之回滾。我們可以通過使用帶有EXCEPTION子句的BEGIN塊來捕獲異常并使其從中恢復。見如下聲明形式：
復制代碼 代碼如下:
[ label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN condition [ OR condition ... ] THEN
handler_statements
WHEN condition [ OR condition ... ] THEN
handler_statements
END;
如果沒有錯誤發生，只有BEGIN塊中的statements會被正常執行，然而一旦這些語句中有任意一條發生錯誤，其后的語句都將被跳過，直接跳轉到EXCEPTION塊的開始處。此時系統將搜索異常條件列表，尋找匹配該異常的第一個條件，如果找到匹配，則執行相應的handler_statements，之后再執行END的下一條語句。如果沒有找到匹配，該錯誤就會被繼續向外拋出，其結果與沒有EXCEPTION子句完全等同。如果此時handler_statements中的語句發生新錯誤，它將不能被該EXCEPTION子句捕獲，而是繼續向外傳播，交由其外層的EXCEPTION子句捕獲并處理。見如下示例：
復制代碼 代碼如下:
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 := x / 0語句時，將會引發一個異常錯誤，代碼將跳轉到EXCEPTION塊的開始處，之后系統會尋找匹配的異常捕捉條件，此時division_by_zero完全匹配，這樣該條件內的代碼將會被繼續執行。需要說明的是，RETURN語句中返回的x值為x := x + 1執行后的新值，但是在除零之前的update語句將會被回滾，BEGIN之前的insert語句將仍然生效。
六、游標：
1. 聲明游標變量：
在PL/pgSQL中對游標的訪問都是通過游標變量實現的，其數據類型為refcursor。 創建游標變量的方法有以下兩種：
1). 和聲明其他類型的變量一樣，直接聲明一個游標類型的變量即可。
2). 使用游標專有的聲明語法，如：
復制代碼 代碼如下:
name CURSOR [ ( arguments ) ] FOR query;
其中arguments為一組逗號分隔的name datatype列表，見如下示例：
復制代碼 代碼如下:
curs1 refcursor;
curs2 CURSOR FOR SELECT * FROM tenk1;
curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
在上面三個例子中，只有第一個是未綁定游標，剩下兩個游標均已被綁定。
2. 打開游標：
游標在使用之前必須先被打開，在PL/pgSQL中有三種形式的OPEN語句，其中兩種用于未綁定的游標變量，另外一種用于綁定的游標變量。
1). OPEN FOR:
其聲明形式為：
復制代碼 代碼如下:
OPEN unbound_cursor FOR query;
該形式只能用于未綁定的游標變量，其查詢語句必須是SELECT，或其他返回記錄行的語句，如EXPLAIN。在PostgreSQL中，該查詢和普通的SQL命令平等對待，即先替換變量名，同時也將該查詢的執行計劃緩存起來，以供后用。見如下示例：
復制代碼 代碼如下:
OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
2). OPEN FOR EXECUTE
其聲明形式為：
復制代碼 代碼如下:
OPEN unbound_cursor FOR EXECUTE query-string;
和上面的形式一樣，該形式也僅適用于未綁定的游標變量。EXECUTE將動態執行其后以文本形式表示的查詢字符串。
復制代碼 代碼如下:
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
3). 打開一個綁定的游標
其聲明形式為：
復制代碼 代碼如下:
OPEN bound_cursor [ ( argument_values ) ];
該形式僅適用于綁定的游標變量，只有當該變量在聲明時包含接收參數，才能以傳遞參數的形式打開該游標，這些參數將被實際代入到游標聲明的查詢語句中，見如下示例：
復制代碼 代碼如下:
OPEN curs2;
OPEN curs3(42);
3. 使用游標：
游標一旦打開，就可以按照以下方式進行讀取。然而需要說明的是，游標的打開和讀取必須在同一個事物內，因為在PostgreSQL中，如果事物結束，事物內打開的游標將會被隱含的關閉。
1). FETCH
其聲明形式為：
復制代碼 代碼如下:
FETCH cursor INTO target;
FETCH命令從游標中讀取下一行記錄的數據到目標中，其中目標可以是行變量、記錄變量，或者是一組逗號分隔的普通變量的列表，讀取成功與否，可通過PL/pgSQL內置變量FOUND來判斷，其規則等同于SELECT INTO。見如下示例：
復制代碼 代碼如下:
FETCH curs1 INTO rowvar; --rowvar為行變量
FETCH curs2 INTO foo, bar, baz;
2). CLOSE
其聲明形式為：
復制代碼 代碼如下:
CLOSE cursor;
關閉當前已經打開的游標，以釋放其占有的系統資源，見如下示例：
復制代碼 代碼如下:
CLOSE curs1;
七、錯誤和消息：
在PostgreSQL中可以利用RAISE語句報告信息和拋出錯誤，其聲明形式為：
復制代碼 代碼如下:
RAISE level 'format' [, expression [, ...]];
這里包含的級別有DEBUG(向服務器日志寫信息)、LOG(向服務器日志寫信息，優先級更高)、INFO、NOTICE和WARNING(把信息寫到服務器日志以及轉發到客戶端應用，優先級逐步升高)和EXCEPTION拋出一個錯誤(通常退出當前事務)。某個優先級別的信息是報告給客戶端還是寫到服務器日志，還是兩個均有，是由log_min_messages和client_min_messages這兩個系統初始化參數控制的。
在format部分中，%表示為占位符，其實際值僅在RAISE命令執行時由后面的變量替換，如果要在format中表示%自身，可以使用%%的形式表示，見如下示例：
復制代碼 代碼如下:
RAISE NOTICE 'Calling cs_create_job(%)',v_job_id; --v_job_id變量的值將替換format中的%。
RAISE EXCEPTION 'Inexistent ID --> %',user_id;
您可能感興趣的文章:- pgsql查詢優化之模糊查詢實例詳解
- C#使用EF連接PGSql數據庫的完整步驟
- 簡單的pgsql pdo php操作類實現代碼
- MySQL鎖阻塞的深入分析
- mysql查看死鎖與去除死鎖示例詳解
- Mysql查看死鎖與解除死鎖的深入講解
- mysql插入前判斷數據是否存在的操作
- PgSQl臨時表創建及應用實例解析
