PostgreSQL7.0手冊-用戶手冊-10. PL/pgSQL - SQL 過程語言

2019-09-08 23:33:10

字體：大中小

來源：轉載

供稿：網友

第十章.　PL/pgSQL　-　SQL　過程語言

概述　
描述　
例子　

PL/pgSQL　是　Postgres　數據庫系統的一個可裝載的過程語言．　
這個包最初是　Jan　Wieck　寫的．　
概述
PL/pgSQL　的設計目標是創建一種可裝載的過程語言，可以　
可用于創建函數和觸發器過程，　
為　SQL　語言增加控制結構，　

可以執行復雜的計算，　

繼承所有用戶定義類型，函數和操作符，　

可以定義為被服務器信任（的語言），　

容易使用．

PL/pgSQL　函數第一次被后端調用時，PL/pgSQL　的調用控制器分析函數源文本生成二進制指令樹．所生成的字節碼在調用控制器里是用函數的對象標識來標記的．這樣就保證如果使用　DROP/CREATE　序列修改了函數，不需要建立一個新的數據庫聯接就能令修改生效．　
對于函數里用到的所有表達式和　SQL　語句，PL/pgSQL　字節碼解析器使用　SPI　管理器　SPI_prepare()　和　SPI_saveplan()　函數創建一個準備好了的執行規劃．這些動作是在（解析器）第一次處理時完成的，獨立的語句是在　PL/pgSQL　函數里面處理的．因此，一個帶有條件代碼的有許多語句的需要執行規劃的函數，將只準備和保存那些在整個數據庫聯接周期內真正使用到的規劃．　

除了用于用戶定義類型的輸入/輸出轉換和計算函數以外，任何可以在　C　語言函數里定義的東西都可以在　PL/pgSQL　里使用．我們可以創建復雜的條件計算函數，并隨后將之用于定義操作符或者用于函數索引中．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

描述
PL/pgSQL　的結構
PL/pgSQL　語言是大小寫不敏感的語言．所有關鍵字和標識都可以混合大小寫使用．　
PL/pgSQL　是一種面向塊的語言．一個塊象下面這樣定義　

[<>]
[DECLARE
　　　　declarations]
BEGIN
　　　　statements
END;
在一個語句塊（statement　section）里面可以有任意個子語句塊．子語句塊可以用于屏蔽語句塊外面的變量．在語句塊前面的聲明段（declarations　section）里定義的變量在每次進入語句塊時都初始化為它們的缺省值，而不是每次函數調用時初始化一次．　
我們一定不要把　PL/pgSQL　里的分組語句　BEGIN/END　和用于事務控制的數據庫命令搞混了．函數和觸發器不能開始或提交一個事務并且Postgres　也沒有嵌套事務的用法．

注釋
在　PL/pgSQL　里有兩種類型的注釋．雙破折號　'--'　引出到該行行尾的單行注釋．一個　'/*'　開始一個塊注釋，一直延伸到下一個　'*/'出現．塊注釋不能嵌套使用，但是雙破折號注釋可以封裝在一個塊注釋里面并且雙破折號注釋可以隱藏一個塊注釋分隔符　'/*'　和　'*/'．
聲明
在一個語句塊或者它的子語句塊里用到的所有變量，行和記錄都必須在一個語句塊的聲明段里定義－－　除了　FOR　循環里說明循環范圍的整數循環變量以外．賦予一個　PL/pgSQL　函數的參數自動用常用的標識　$n　定義．定義有下面語法：　
name　[　CONSTANT　]　>type>　[　NOT　NULL　]　[　DEFAULT　|　:=　value　];　
　　
　　

定義一個指明了的基本類型的變量．如果變量被定義為　CONSTANT，該變量值就不能修改．如果聲明了　NOT　NULL，則賦予一個　NULL　值給該變量將導致一個運行時錯誤．因為所有變量的缺省值都是　SQL　NULL，因而所有定義為　NOT　NULL　的變量都必須有指明的缺省值．　

缺省值在每次函數調用時都被計算．所以賦予　'now'　給一個類型為　datetime　的變量將導致變量擁有實際函數調用時的時間，而不是函數被編譯為字節碼的時間．　
　　

name　class%ROWTYPE;　
　　
　　

定義一個帶有指定表的結構的行．表必須是一個數據庫中現存的表或視圖的名稱．行的字段是通過點定義來訪問的．函數的參數可以是復合類型（表里面完整的行）．這時，對應的標識　$n　將是一個行類型（rowtype），但是它必須用下面描述的　ALIAS　命令取個別名．在一行里只有用戶字段可以被訪問，而　OID　或其他系統字段都不可訪問（因為行可能來自一個視圖，而視圖的行并沒有有用的系統字段）．　

行類型（rowtype）字段繼承表中　char()　等數據類型的字段尺寸或精度．　
　　

name　RECORD;　
　　
　　

記錄（Records）類似于行類型（rowtypes），但它們沒有預定義的結構．它們在選擇和　FOR　循環中使用，用于從一個　SELECT　操作中保存一個具體的數據庫的行．該條記錄可以在不同的選擇中使用．當沒有實際的行存在于其中時試圖訪問一條記錄或賦予某個變量的值將導致一個運行時錯誤產生．　

觸發器里的　NEW　和　OLD　行是作為記錄傳給過程的．這一點是必要的，因為　Postgres　里同樣的觸發器過程可以為不同的表控制觸發器事件．　
　　

name　ALIAS　FOR　$n;　
　　
　　

為了讓代碼更具可讀性，我們可以為函數的位置參數定義一個別名．　
　　

當把復合類型做為參數傳遞給一個函數時要求這個別名．在　SQL　函數里的點表示法（$1.salary）在　PL/pgSQL　里是不允許的．　
RENAME　oldname　TO　newname;　
　　
　　

改變變量，記錄或者行的名稱．這一點當　NEW　或　OLD　在觸發器里要被做為另一個名字引用時很有用．

數據類型
變量類型可以是任意數據庫里現有的基本類型．上面聲明段里的　type　是這樣定義的：　
Postgres-基本類型　
variable%TYPE　

class.field%TYPE

variable　是變量名，事先在同一函數里定義，在這一點上可見.　
class　是現存表或視圖，field　是一個字段的名稱．　

使用　class.field%TYPE　導致　PL/pgSQL　在后端的生存期里第一次調用該函數時查看該字段的定義．如果我們有一個表有一個字段定義為　char(20)　而且一些　PL/pgSQL　函數在局部變量里處理該字段的內容．現在我們發現　char(20)　不夠長，把表倒出來，刪除，重新把有問題的字段定義為　char(40)　然后恢復數據．哈－他忘了函數．函數里的運算將把數值裁斷成20個字符．但是如果他們用　class.field%TYPE　做定義，它們將自動控制字段的長度的變化或新表把該字段定義為　text　類型．

表達式
在　PL/pgSQL　語句里的所有表達式都用后端的執行器進行處理．包含約束的表達式可能實際上需要運行時計算（例如．datetime　類型的'now'），所以對于　PL/pgSQL　分析器而言，除了　NULL　關鍵字以外它是不可能識別真正常量的值的．所有表達式都通過用　SPI　管理器在內部運行查詢　
　　　　　　SELECT　expression
來計算．在表達式里出現的變量標識都被參數和放在參數數組里傳入執行器的變量實際值所代替．所有在　PL/pgSQL　函數里用到的表達式都只準備和存儲一次．　
Postgres　的主分析器做的類型檢查對轉換常量有一些副作用．詳細說來，下面的兩個函數有一些區別　

CREATE　FUNCTION　logfunc1　(text)　RETURNS　datetime　AS　'
　　　　DECLARE
　　　　　　　　logtxt　ALIAS　FOR　$1;
　　　　BEGIN
　　　　　　　　INSERT　INTO　logtable　VALUES　(logtxt,　''now'');
　　　　　　　　RETURN　''now'';
　　　　END;
'　LANGUAGE　'plpgsql';
和　
CREATE　FUNCTION　logfunc2　(text)　RETURNS　datetime　AS　'
　　　　DECLARE
　　　　　　　　logtxt　ALIAS　FOR　$1;
　　　　　　　　curtime　datetime;
　　　　BEGIN
　　　　　　　　curtime　:=　''now'';
　　　　　　　　INSERT　INTO　logtable　VALUES　(logtxt,　curtime);
　　　　　　　　RETURN　curtime;
　　　　END;
'　LANGUAGE　'plpgsql';
在　logfunc1()　里，Postgres　的主分析器在準備　INSERT　的規劃時認為，字符串　'now'　應該解釋為　datetime，因為　logtable　的目標字段是該類型．因此，這時它將從中生成一個常量并且在所有后端生存期內所有對　logfunc1()　的調用時使用該常量．不用說這可不是程序員希望的．　
在　logfunc2()　里，Postgres　的主分析器并不知道　'now'　應該轉換的類型，因此它返回一個包含字符串　'now'　的　text　數據類型．在給局部變量　curtime　賦值時，PL/pgSQL　解釋器通過調用　text_out()和　datetime_in()　把這個字符串轉換成　datetime　類型的變量．　

這個　Postgres　主分析器的類型檢查是在　PL/pgSQL　接近完成的時候實現的．在版本　6.3　和版本　6.4　之間有所不同并且影響所有使用　SPI　管理器的規劃特性的函數．使用上面提到的局部變量的方法是目前能讓　PL/pgSQL　對那些數值正確解釋的唯一方法．　

如果在表達式或語句里用到記錄（record）字段，字段的數據類型在同一個表達式的不同調用中不應該改變．書寫控制多個表的觸發器過程時應該注意這一點．

語句
任何象下面聲明的　PL/pgSQL　分析器不能理解的東西將被放到查詢里并發送給數據庫引擎執行．生成的查詢應該不返回任何數據．　
賦值　
　　
　　

給一個變量或行/記錄賦值用下面方法　

　　　　　　　　　identifier　:=　expression;
如果表達式的結果數據類型和變量數據類型不一致，或者變量具有已知的尺寸/精度（例如　char(20)），結果值將隱含的被　PL/pgSQL　字節碼解釋器用結果類型的輸出函數和變量類型的輸入函數轉換．要注意這樣做可能潛在地導致類型輸入函數生成的運行時錯誤．　
下面方法可以把一個完整的選擇放到一條記錄或者行里　

SELECT　expressions　INTO　target　FROM　...;
target　可以是一條記錄，一行變量或一個逗號分隔的變量列表和記錄/行字段（域）．　
如果一行或者一個變量列表當做目標，選擇的數值必須與目標列的結構完全一樣，否則會產生一條運行時錯誤．FORM　關鍵字可以跟隨著任意可在　SELECT　語句里使用的有效的資格（條件），分組，排序等．　

一個名為　FOUND　的特殊的布爾類型的變量可以在　SELECT　INTO　之后立刻用于檢查賦值是否成功．　

SELECT　*　INTO　myrec　FROM　EMP　WHERE　empname　=　myname;
IF　NOT　FOUND　THEN
　　　　RAISE　EXCEPTION　''employee　%　not　found'',　myname;
END　IF;
如果選擇返回多行，只有第一行被放到目標域里面．其他所有都被悄悄地丟棄掉了．　
　　
調用其他函數　
　　
　　

所有在　Prostgres　數據庫里的函數返回一個值．因此，通常的調用函數的方法是執行一條　SELECT　查詢或者做一個賦值（導致一個　PL/pgSQL　的內部　SELECT　）．但是有時候我們對函數結果并不感興趣．　

PERFORM　query
將在　SPI　管理器上執行一個　'SELECT　query'　然后丟棄結果．象局部變量這樣的標識仍然代入參數．　
從函數返回　
RETURN　expression
函數結束并且表達式　expression　的值將返回給上層執行器．函數的返回值不能取消定義．如果控制到達一個函數體的最頂層而沒有碰到一個　RETURN　語句，將產生一個運行時錯誤．　
表達式的結果將被自動轉換成函數返回類型－－象我們在賦值里描述的那樣．　
　　

退出和消息　
　　
　　

象上面的例子指明的那樣，有一個　RAISE　語句可以丟出一條信息到　Postgres　elog　機制里面．　

RAISE　level　''format''　[,　identifier　[...]];
在格式　（　format　）里面，“%”　用做一個后面跟隨的逗號分隔的標識的占位符．可能的級別（level）是　DEBUG　（安靜地停止運行的數據庫），NOTICE　（向數據庫書寫日志并向前端客戶應用發送信息）和　EXCEPTION　（向數據庫書寫日志并且退出事務　）．　
　　
條件　
IF　expression　THEN
　　　　statements
[ELSE
　　　　statements]
END　IF;
表達式　expression　必須返回一個最起碼可以轉換成布爾類型的數值．　
　　
循環　
　　
　　

有許多循環的類型．　

[<>]
LOOP
　　　　statements
END　LOOP;
一個無條件循環必須用一條　EXIT　語句顯式終止．可選的標記　label　可以被　EXIT　用于嵌套循環用于聲明應該結束哪一層循環．　
[<>]
WHILE　expression　LOOP
　　　　statements
END　LOOP;
一個條件循環在表達式　expression　為真時執行．　
[<>]
FOR　name　IN　[　REVERSE　]　expression　..　expression　LOOP
　　　　statements
END　LOOP;
一個在某一整數范圍內執行的循環，變量　name　自動做為整數類型創建，并且只存在于循環內部．兩個表達式給出循環范圍的區間，它們只是在進入循環后才被計算出數值．循環步進值總是　1．　
[<>]
FOR　record　|　row　IN　select_clause　LOOP
　　　　statements
END　LOOP;
記錄或行被賦予了　select　子句來的所有行，并且語句　statement　對每一條記錄/行都運行一次．如果用　EXIT　語句退出循環，最后賦值的行仍然在循環后可以被訪問．　
EXIT　[　label　]　[　WHEN　expression　];
如果沒有標記　label　，最內層的循環將被結束并且　END　LOOP　后面的語句將被接著執行．如果給出了標記　label　，該標記必須是當前或者上層嵌套的循環塊．那么命名的循環或者語句塊被終止并且控制落到循環/語句塊的對應　END　的后面一條語句．
觸發器過程
PL/pgSQL　可以用于定義觸發器過程．它們通過通常的　CREATE　FUNCTION　命令創建為沒有參數并且返回　OPAQUE　類型的函數．　
做為觸發器過程的函數有一些　Postgres　特有的細節說明．　
首先他們有一些在頂層的聲明段里自動定義的特殊變量．有如下這些　
　　
NEW　
　　
數據類型是　RECORD；該變量保存著行（ROW）一級的觸發器在　INSERT/UPDATE　操作時的新的數據庫行．　
OLD　
　　
數據類型是　RECORD；該變量保存著行（ROW）一級的觸發器在　INSERT/UPDATE　操作時的舊的數據庫行。　
TG_NAME　
　　
數據類型是　name；該變量包含實際觸發的觸發器名．　
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　或者是一個與導致觸發器運行的表的記錄/行完全一樣的結構的數據．AFTER　類型的觸發器可以總是返回一個沒有意義的　NULL　值．BEFORE　類的觸發器如果返回一個　NULL，將發送一個信號給觸發器管理器忽略對實際行的操作．否則，返回的記錄/行將代替插入/更新操作中的行．我們可能用一個值直接代替　NEW　里的數值并且返回之或者我們也可以構建一個完全新的返回記錄/行．
例外
Postgres　不具有一個很好的例外處理模塊．當分析器，規劃器（調度器）/優化器或者執行器認為一個語句不能在處理下去了，整個事務都退出并且系統跳回主循環等待從客戶應用過來的下一個查詢．　
我們可以'鉤'在錯誤機制上來提示這種情況的發生．但是目前我們沒有能力告訴（用戶）是什么導致了退出（輸入/輸出轉換錯誤，浮點數錯誤，分析錯誤）．并且此時的數據庫后端可能處在一種不連貫的狀態，所以退回到上層執行器或執行更多的命令可能摧毀整個數據庫．而且此時事務退出的信息可能已經發送給了客戶端應用，所以繼續操作沒有任何意義．　

因此，PL/pgSQL　在函數或觸發器操作時遇到退出的唯一一項操作是在　DEBUG　級別運行時輸出一些附加的日志信息，報告在哪個函數和在那里（行號和語句類型）出了錯．

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

例子
這里是幾個例子用以說明書寫　PL/pgSQL　函數是多么地容易．對于更復雜的例子，程序員們可以看看用于　PL/pgSQL　回歸測試的程序．　
書寫　PL/pgSQL　的一個很痛苦的細節是單引號的使用．CREATE　FUNCTION　的函數原文本必須是一個語言字符串．在一個語言字符串里的單引號要么是兩個單引號或者是用反斜杠轉意．我們仍然在尋找一種優美的代替物．同時，我們應該象下面例子那樣使用雙引號．將來任何版本的　Postgres　對此的任何解決方法都將保持向下兼容．　

一些簡單的　PL/pgSQL　函數
下面的兩個　PL/pgSQL　函數與　C　語言里討論的對應函數是一樣的．　
CREATE　FUNCTION　add_one　(int4)　RETURNS　int4　AS　'
　　　　BEGIN
　　　　　　　　RETURN　$1　+　1;
　　　　END;
'　LANGUAGE　'plpgsql';
CREATE　FUNCTION　concat_text　(text,　text)　RETURNS　text　AS　'
　　　　BEGIN
　　　　　　　　RETURN　$1　||　$2;
　　　　END;
'　LANGUAGE　'plpgsql';
復合類型的　PL/pgSQL　函數
同樣下面的是等效于　C　函數樣例的　PL/pgSQL　函數．　
CREATE　FUNCTION　c_overpaid　(EMP,　int4)　RETURNS　bool　AS　'
　　　　DECLARE
　　　　　　　　emprec　ALIAS　FOR　$1;
　　　　　　　　sallim　ALIAS　FOR　$2;
　　　　BEGIN
　　　　　　　　IF　emprec.salary　ISNULL　THEN
　　　　　　　　　　　　RETURN　''f'';
　　　　　　　　END　IF;
　　　　　　　　RETURN　emprec.salary　>　sallim;
　　　　END;
'　LANGUAGE　'plpgsql';
PL/pgSQL　觸發器過程
下面的觸發器的作用是：任何時候表中插入或更新了行，當前的用戶名和時間都記錄入行中．并且它保證給出了雇員名稱并且薪水是一個正數．　
CREATE　TABLE　emp　(
　　　　empname　text,
　　　　salary　int4,
　　　　last_date　datetime,
　　　　last_user　name);

CREATE　FUNCTION　emp_stamp　()　RETURNS　OPAQUE　AS　'
　　　　BEGIN
　　　　　　　　--　檢查是否給出了　empname　和　salary
　　　　　　　　IF　NEW.empname　ISNULL　THEN
　　　　　　　　　　　　RAISE　EXCEPTION　''empname　cannot　be　NULL　value'';
　　　　　　　　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　:=　getpgusername();
　　　　　　　　RETURN　NEW;
　　　　END;
'　LANGUAGE　'plpgsql';

CREATE　TRIGGER　emp_stamp　BEFORE　INSERT　OR　UPDATE　ON　emp
　　　　FOR　EACH　ROW　EXECUTE　PROCEDURE　emp_stamp();

--------------------------------------------------------------------------------

上一篇：PostgreSQL7.0手冊-用戶手冊-11. PL/Tcl - TCL 過程語言

下一篇：PostgreSQL7.0手冊-用戶手冊-9. 繼承