Chapter 35. PL/pgSQL - SQL 過程語言

Table of Contents
35.1. 概述
35.1.1. 使用PL/pgSQL的優點
35.1.2. 所支援的參數和結果資料類型
35.2. 開發 PL/pgSQL 的一些提示
35.3. PL/pgSQL的結構
35.4. 聲明
35.4.1. 函數參數的別名
35.4.2. 拷貝類型
35.4.3. 行類型
35.4.4. 記錄類型
35.4.5. RENAME
35.5. 資料表達式
35.6. 基本語句
35.6.1. 賦值
35.6.2. SELECT INTO
35.6.3. 執行一個沒有結果的資料表達式或者命令
35.6.4. 執行動態命令
35.6.5. 獲取結果狀態
35.7. 控制結構
35.7.1. 從函數返回
35.7.2. 條件
35.7.3. 簡單循環
35.7.4. 遍歷命令結果
35.7.5. 捕獲錯誤
35.8. 游標
35.8.1. 聲明游標變量
35.8.2. 打開游標
35.8.3. 使用游標
35.9. 錯誤和消息
35.10. 觸發器過程
35.11. Oracle的 PL/SQL 移植
35.11.1. 移植樣例
35.11.2. 其它要注意的東西
35.11.3. 附錄

PL/pgSQLPostgreSQL 資料庫系統的一個可裝載的過程語言。 PL/pgSQL的設計目標是建立一種可裝載的過程語言,可以

除了用於用戶定義類型的輸入/輸出轉換和計算函數以外, 任何可以在 C 語言函數里定義的東西都可以在 PL/pgSQL裡使用。 比如,我們可以建立複雜的條件計算函數, 並隨後將之用於定義操作符或者用於函數索引中。

35.1. 概述

PL/pgSQL 函數第一次(在任何一個伺服器進程內部)被調用時, PL/pgSQL 的調用句柄分析函數源文本生成二進制指令樹。 該指令樹完全轉換了 PL/pgSQL 語句結構, 但是在函數內使用到的獨立的 SQL 資料表達式和 SQL 命令並未立即轉換。

在每個函數中用到的資料表達式和 SQL 命令在函數里首次使用的時候, PL/pgSQL 解釋器建立一個準備好的執行規劃(使用 SPI 管理器的 SPI_prepareSPI_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/pgSQLEXECUTE語句動態地構造命令 — 代價是每次執行的時候都構造一個新的命令計劃。

注意: PL/pgSQLEXECUTE語句和 PostgreSQL 伺服器支援的 EXECUTE 語句沒有關係。 伺服器的EXECUTE語句不能在 PL/pgSQL 函數中使用(而且也沒必要)。

35.1.1. 使用PL/pgSQL的優點

SQLPostgreSQL 和大多數其它關係型資料庫用做命令語言的語言。 它是可以移植的,並且容易學習使用。 但是所有 SQL 語句都必須由資料庫伺服器獨立地執行。

這就意味著您的客戶端應用必須把每條命令發送到資料庫伺服器, 等待它處理這個命令,接收結果,做一些運算,然後給伺服器發送另外一條命令。 所有這些東西都會產生進程間通訊,並且如果您的客戶端在另外一台機器上甚至還會導致網絡開銷。

如果使用了PL/pgSQL,那麼您可以把一塊運算和一系列命令在資料庫伺服器裡面組成一個塊, 這樣就擁有了過程語言的力量並且簡化 SQL 的使用,因而節約了大量的時間,因為您用不著付出客戶端/伺服器通訊的過熱。 這樣可能產生明顯的性能提升。

同樣,在 PL/pgSQL 裡,您可以使用 SQL 的所有資料類型,操作符和函數。

35.1.2. 所支援的參數和結果資料類型

並且它們可以返回這種任何這種類型的數值。它們還可以接受或者返回任意用名字聲明的復合類型(行類型)。 我們還可以聲明一個 PL/pgSQL 函數為返回 record 的函數, 意思是結果是一個行類型,這個行的字串是在調用它的查詢中指定的,就像我們在 Section 7.2.1.4 裡討論的那樣。

PL/pgSQL 函數還可以聲明為接受並返回多態的類型 anyelementanyarray。 一個多態的函數實際操作的資料類型可以在不同的調用環境中變化, 如我們在 Section 31.2.5 裡討論的那樣。 一個例子是 Section 35.4.1

PL/pgSQL 還可以聲明為返回一個它們可以返回的任何單個實例的"集(set)",或者資料表。 這樣的函數透過為結果集每個需要返回的元素執行一個 RETURN NEXT 生成它的輸出。

最後,PL/pgSQL 函數可以聲明為返回 void,如果它沒啥有用的東西可以返回的話。

PL/pgSQL 目前還不是完全支援域類型:它看待域類型和下層的標量類型是一樣的。 這就意味著與域關聯的約束將不會被強制。對於函數參數,這不是什麼問題, 但是如果您把 PL/pgSQL 函數聲明為返回一個域類型,那麼就有危險。