# 40.10\. 在后臺下的PL/pgSQL 本節討論PL/pgSQL用戶知道的比較重要的一些實現細節。 ## 40.10.1\. 變量替換 在PL/pgSQL函數內的SQL語句和表達式 可以參考變量和函數的參數。在后臺，PL/pgSQL替代這些參考查詢參數。 參數只會按照句法允許一個參數或列引用的地方被取代。作為一個極端的例子， 考慮不好的編程風格的這個例子： ``` INSERT INTO foo (foo) VALUES (foo); ``` `foo`第一次出現一定在語法上是表名字，所以它不會被取代，即使函數有一個可變名 `foo`。第二次發生必須是表列名稱，所以它也不會被取代。 只有第三次發生是參考函數變量的一個候選。 > **Note:** 9.0之前的PostgreSQL版本可能嘗試所有三種情況中替換變量，導致語法錯誤。 由于變量的名字語法上和表列名字沒有什么不同，參考表的語句中有模糊：它是一個給定的名字意味著引用一個表列，或一個變量？ 讓我們改變以往的例子 ``` INSERT INTO dest (col) SELECT foo + bar FROM src; ``` 在這里，`dest`和`src`必須是表名，并且 `col`必須是`dest`的列，但是`foo` 和`bar`可能是函數變量或者`src`的列。 默認情況下，如果在一個SQL語句中的名字 可以參考一個變量或表列，則PL/pgSQL將報告錯誤。 你可以通過重命名變量或列，或限定不明確的引用，或者告訴PL/pgSQL 說明更喜歡哪個來解決這樣的問題。 最簡單的解決方案是重命名變量或列。一個常見的編碼規則是使用 PL/pgSQL變量的不同命名慣例而不是你使用列名稱。 比如，如果你一貫地命名函數變量`v_``_something_`， 然而你的列沒有以`v_`開頭命名，不會發生沖突。 另外你可以限定含糊的引用以使得它們明確。 在上面的例子中，`src.foo`將是表列的明確參考。 為了創建明確的引用變量， 在標記塊聲明它并且使用塊標簽（參閱[Section 40.2](#calibre_link-859)）。比如， ``` <<block>> DECLARE foo int; BEGIN foo := ...; INSERT INTO dest (col) SELECT block.foo + bar FROM src; ``` 這里`block.foo`意味著變量， 即使在`src`中有一列`foo`。 函數的參數，以及特殊變量如`FOUND`，可以滿足 函數的名字，因為他們在使用函數名標記的外部塊中隱式聲明。 有時修復在PL/pgSQL編碼主體下所有不明確的引用是不切實際的。 在這種情況下，你可以指定PL/pgSQL應該解決不明確的引用，作為變量 （即兼容PostgreSQL 9.0之前的PL/pgSQL的行為）， 或作為表列（與其他一些系統兼容，如Oracle） 要改變在系統范圍基礎上的這種行為，設置配置參數`plpgsql.variable_conflict`為 `error`, `use_variable`或者 `use_column`之一（`error`是出廠缺省值）。 此參數會影響PL/pgSQL函數中語句后續的編譯，但不是在當前會話中已編譯的語句。 由于更改此設置可以導致PL/pgSQL函數行為意想不到的變化，它只能由超級用戶改變。 你也可以在功能分析的基礎上設定行為，通過在函數文本的開始處插入這些特殊的命令之一： ``` #variable_conflict error #variable_conflict use_variable #variable_conflict use_column ``` 這些命令只影響寫入的函數， 并且重寫`plpgsql.variable_conflict`的設置。例如： ``` CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ #variable_conflict use_variable DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = curtime, comment = comment WHERE users.id = id; END; $$ LANGUAGE plpgsql; ``` 在`UPDATE`命令中，`curtime`, `comment`, 和`id`將引用函數的變量和`users`是否具有這些名稱列的參數。 請注意我們必須限定到`WHERE`子句`users.id`的引用以使得它引用表列。 我們沒有必要限定引用到`comment`作為`UPDATE`列表中的目標， 因為語法上必定是`users`的列。 我們可以寫不依賴于這種方式的`variable_conflict`設置的同樣函數： ``` CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$ <<fn>> DECLARE curtime timestamp := now(); BEGIN UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment WHERE users.id = stamp_user.id; END; $$ LANGUAGE plpgsql; ``` 變量代換不會發生在給定`EXECUTE`或者它的變種之一的命令字符串中。 如果你需要插入一個不同的值到這個命令中， 執行它作為構建字符串值的一部分， 或使用`USING`，正如 [Section 40.5.4](#calibre_link-1590)說明的。 變量替換目前只能在`SELECT`,`INSERT`, `UPDATE`和 `DELETE`命令中運行，因為主要的SQL引擎允許這些命令中的查詢參數。 為了使用其他語句類型中非恒定的名稱或值 （一般稱為實用語句），你必須構建實用語句作為字符串并且`EXECUTE`它。 ## 40.10.2\. 計劃緩存 PL/pgSQL解釋器解析函數的源 文本并且第一次函數被調用時（每個會話中）產生一個內部二進制指令樹。 指令樹充分翻譯PL/pgSQL語句結構， 但個別SQL表達式和在函數中使用的SQL命令不是立即翻譯。 在函數中首先執行每個表達式和SQL命令，PL/pgSQL 解釋器解析并且分析命令以創建預備語句，使用SPI管理的 `SPI_prepare`函數。 隨后訪問表達式或命令重新使用事先準備好的語句。 因此，帶有條件編碼路徑的函數很少被訪問將不會產生分析不在當前會話中執行的命令的開銷， 一個缺點是在一個特定的表達或命令中的錯誤 不能被檢測到直到執行達到函數部分 （瑣碎的語法錯誤在初步解析傳遞期間將被檢測到，但是任何更深的東西將不會被檢測到直到執行為止。） PL/pgSQL（或者更準確的說，SPI管理者）可以 嘗試與任何特別已準備語句相關的緩存執行計劃。 如果沒有使用緩存計劃， 那么在每次訪問語句中產生一個新的執行計劃，并且 當前的參數值（即，PL/pgSQL 變量值）可以用來優化選擇方案。 如果語句沒有參數，或是執行多次，SPI管理者 將考慮創建_generic_計劃不依賴于特定的參數值，并且緩存再利用。 只有執行計劃對PL/pgSQL變量中引用的值不太敏感時，往往會發生。 如果是，每次生成一個計劃都是凈贏。 參閱[PREPARE](#calibre_link-625)獲得更多有關預備語句行為信息。 因為PL/pgSQL保存已預備好語句并且有時以這種方式執行計劃， 直接出現在PL/pgSQL函數中的SQL命令必須查閱相同表和每個執行列； 也就是說，你不能使用參數作為SQL命令的表或列的名字。 為了應對這個限制， 你可以使用PL/pgSQL `EXECUTE` 語句構建動態命令；以執行新的解析分析和每個執行上構建新的執行計劃為代價。 記錄變量的可變性質提出連接中的另一個問題。 當在表達式或語句中使用記錄變量字段時， 該字段數據類型必須不能從函數的一個調用到下一個改變， 因為當表達式第一個到達時使用目前數據類型分析每個表達式。 `EXECUTE`必要時可以用于解決這個問題。 如果相同函數作為多個表的觸發器使用， PL/pgSQL為了每個表獨立地準備并且緩存聲明； 即，有一個觸發器函數和表組合的高速緩存，而不只是為每個函數。 這解決了一些數據類型不同的問題；例如，一個觸發器函數可以使用命名`key`的列 成功運行，即使發生不同的表中有不同的類型。 同樣，具有多態性參數類型的函數 有一個他們被調用的實際參數類型的每個組合的單獨聲明緩存， 所以該數據類型差異不會導致意外失敗。 語句緩存有時會對時間敏感值的解釋有令人驚訝的影響。 例如在這兩個函數要做的內容之間有區別： ``` CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$ BEGIN INSERT INTO logtable VALUES (logtxt, 'now'); END; $$ LANGUAGE plpgsql; ``` 和 ``` CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$ DECLARE curtime timestamp; BEGIN curtime := 'now'; INSERT INTO logtable VALUES (logtxt, curtime); END; $$ LANGUAGE plpgsql; ``` 在`logfunc1`的情況下，該 PostgreSQL主解析器知道當 分析`INSERT`時 字符串`'now'`應解釋為`時間戳`， 因為`logtable`目標列是那種類型。 因此，當分析`INSERT`時， `'now'`將被轉換為`timestamp`常量， 然后在會話的整個生命周期中用于`logfunc1`的所有調用。 不用說，這不是程序員希望的。 一個更好的辦法是使用`now()`或者`current_timestamp`函數。 在`logfunc2`的情況下， PostgreSQL主解析器并不知道 `'now'`應該成為什么類型，因此它返回包含字符串`now` 類型`text`的數據值。 隨后分配給局部變量`curtime`期間， PL/pgSQL解析器通過調用`text_out`和 `timestamp_in`轉換函數將這個字符串轉換為`timestamp`類型， 因此，作為編程期望每次執行時更新計算時間戳。 盡管這正如預期的那樣發生，這不是非常有效的， 所以`now()`函數的使用仍然會是一個更好的主意。