CREATE FUNCTION · PostgreSQL 中文文檔 9.3

# CREATE FUNCTION ## Name CREATE FUNCTION?--?定義一個新函數 ## Synopsis ``` CREATE [ OR REPLACE ] FUNCTION _name_ ( [ [ _argmode_ ] [ _argname_ ] _argtype_ [ { DEFAULT | = } _default_expr_ ] [, ...] ] ) [ RETURNS _rettype_ | RETURNS TABLE ( _column_name_ _column_type_ [, ...] ) ] { LANGUAGE _lang_name_ | WINDOW | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST _execution_cost_ | ROWS _result_rows_ | SET _configuration_parameter_ { TO _value_ | = _value_ | FROM CURRENT } | AS '_definition_' | AS '_obj_file_', '_link_symbol_' } ... [ WITH ( _attribute_ [, ...] ) ] ``` ## 描述 `CREATE FUNCTION`定義一個新的函數。 `CREATE OR REPLACE FUNCTION` 如果函數不存在就創建一個新函數，否則替換現有的定義。用戶必須有定義該函數所用語言的 `USAGE` 權限才能定義新函數。如果包含了一個模式名，那么函數就在指定的模式中創建。否則它會在當前模式中創建。新函數的名字不能和同一個模式中的任何帶有同樣參數類型的函數同名。不過，參數類型不同的函數可以同名(這叫做_重載_)。使用 `CREATE OR REPLACE FUNCTION` 替換一個現有函數的定義。不能用這個方法修改一個函數的名字或者參數類型，否則就會創建一個新的函數。同樣 `CREATE OR REPLACE FUNCTION` 也不會允許你修改一個現有函數的返回類型。要做這些事情，你必須刪除并重新創建函數。如果使用 `OUT` 參數，那就意味著除了刪除函數之外，你不能修改任何 `OUT` 參數的類型或者名字。當使用 `CREATE OR REPLACE FUNCTION` 替換現有函數的定義時，不會改變函數的屬主和權限。函數其他的屬性會被賦予命令中給定的值或默認值。只有函數的屬主才可以替換函數（也可以是屬主角色的成員）。如果你刪除然后重建一個函數，新函數和舊函數將是不同的實體；你就需要刪除現有引用了老函數的規則、視圖、觸發器等等。使用`CREATE OR REPLACE FUNCTION` 可以在不破壞引用該函數的對象的前提下修改函數定義。并且，使用`ALTER FUNCTION` 能修改一個已有函數的大多數屬性。創建這個函數的用戶將成為函數的所有者。你必須擁有要創建函數的參數類型和返回值類型的 `USAGE` 權限，才能創建該函數。 ## 參數 `_name_` 要創建的函數名字(可以用模式修飾) `_argmode_` 參數的模式： `IN`，`OUT`，`INOUT`, 或 `VARIADIC`。缺省值是`IN`。只有 `OUT` 模式的參數后面能跟`VARIADIC`。并且`OUT` 和 `INOUT` 模式的參數不能用在 `RETURNS TABLE` 的函數定義中。 `_argname_` 一個參數的名字。有些語言(包括 SQL 和 PL/pgSQL)允許你在函數體里使用參數名字。對于其它語言，輸入參數名字只是額外的文檔，這只是就函數定義本身而言的；在調用函數時你可以使用輸入參數名字來提高可讀性。（見 [Section 4.3](#calibre_link-733)）。無論如何，輸出參數的名字是非常重要的，因為它定義了結果行類型的列名。（如果你省略了輸出參數的名字，那么系統會自動選擇一個缺省的列名。） `_argtype_` 該函數的數據類型(可以有模式修飾)，如果有的話。可以是基本類型，也可以是復合類型、域類型、或者可以引用一個現有字段相同的類型。根據實現語言的不同，還可以在這上面聲明"偽類型" (比如 `cstring`)。偽類型表示實際的參數類型要么是沒有完整地聲明，要么是在普通的 SQL 數據類型之外。一個字段的類型是用 `_table_name_`.`_column_name_`%TYPE 表示的；使用這個特性有時候可以幫助創建一個不受表定義變化影響的函數。 `_default_expr_` 當參數值沒有指定時作為參數默認值的表達式。該表達式的類型必須可轉化為參數的類型。只有輸入(也包括 `INOUT`)參數才能有默認值。具有默認值參數的輸入參數必須在參數列表的最后。 `_rettype_` 返回值的數據類型。可以聲明為一個基本類型、復合類型、域類型、或者引用一個表的現有字段類型。根據實現語言的不同，還可以在這上面聲明"偽類型"(比如 `cstring`)。如果不打算返回任何值可以指定 `void` 作為返回類型。如果存在 `OUT` 或 `INOUT` 參數，那么可以省略 `RETURNS` 子句。如果出現了，那么它必須隱含的和輸出參數結果類型兼容：如果有多個輸出參數，則必須是 `RECORD` ，如果只有一個輸出參數，則與其相同。 `SETOF` 修飾詞表示該函數將返回一個集合，而不是單獨一條。一個字段的類型是通過 `_table_name_`.`_column_name_`%TYPE 引用的。 `_column_name_` `RETURNS TABLE`的語法中輸出字段名。這是另一種有效的聲名帶名字的 `OUT` 類型參數的方式，而且`RETURNS TABLE` 隱含 `RETURNS SETOF`。 `_column_type_` `RETURNS TABLE`語法中輸出字段的數據類型。 `_lang_name_` 用以實現函數的語言的名字。可以是 `SQL`, `C`, `internal`，或者是用戶定義的過程語言名字。該名字可以用單引號包圍。 `WINDOW` `WINDOW` 表示該函數不是普通函數而是一個_窗口函數_。這個屬性當前僅對用C寫的函數起作用。當替換已有函數定義時不能改變函數的`WINDOW`屬性。 `IMMUTABLE``STABLE` `VOLATILE` 這些屬性是在查詢優化器中用來優化函數的調用的，只能指定一個。缺省值是 `VOLATILE`。 `IMMUTABLE` 表示該函數不能修改數據庫，并且在給出同樣的參數值時總是返回同樣的結果；也就是說，它不查詢數據庫或者只使用那些沒有出現在參數列表里的信息。如果給出這個選項，那么任何全部使用常數對該函數的調用都將立即替換為該函數的值。 `STABLE` 表示該函數不能修改數據庫，對相同參數值，在同一次表掃描里，該函數的返回值不變，但是返回值可能在不同 SQL 語句之間變化。這個選項對那些結果依賴數據庫查找、參數變量(比如當前時區)之類的函數很合適。（但對于那些希望查詢當前命令修改的行的 `AFTER` 類型的觸發器是不合適的。）還要注意 `current_timestamp` 函數族是穩定的，因為它們的值在一次事務中不會變化。 `VOLATILE` 表示該函數值甚至可以在一次表掃描內改變，因此不會做任何優化。只有很少的數據庫函數在這個概念上是易變的；一些例子是 `random()`, `currval()`, `timeofday()`。請注意任何有副作用的函數都必需列為易變類，即使其結果相當有規律也應該這樣，這樣才能避免它被優化；一個例子就是 `setval()`。更多細節，請參閱[Section 35.6](#calibre_link-734)。 `LEAKPROOF` `LEAKPROOF` 表示該函數沒有涉密方面的副作用。它除了返回值外，不會泄露它的參數的任何信息。例如，一個函數拋出了一個參數不正確的錯誤，但錯誤消息里包含了參數值的信息，那這個函數就是不保密的(leakproof)。查詢計劃生成器可以把保密的函數放到用 `security_barrier` 選項生成的視圖中，而不保密的函數則不可以。參見 [CREATE VIEW](#calibre_link-473) 和 [Section 38.5](#calibre_link-480)。這個選項只能由超級用戶設置。 `CALLED ON NULL INPUT` `RETURNS NULL ON NULL INPUT` `STRICT` `CALLED ON NULL INPUT` (缺省)表明該函數在自己的某些參數是 NULL 的時候還是可以按照正常的方式調用。函數的作者必須負責檢查 NULL 以及進行相應地處理。 `RETURNS NULL ON NULL INPUT` 或 `STRICT` 表明如果它的任何參數是 NULL，此函數總是返回 NULL。如果聲明了這個參數，則如果存在 NULL 參數時不會執行該函數；而只是自動假設一個 NULL 結果。 `[EXTERNAL] SECURITY INVOKER` `[EXTERNAL] SECURITY DEFINER` `SECURITY INVOKER` (缺省)表明該函數將使用調用它的用戶權限執行。 `SECURITY DEFINER` 聲明該函數將以創建它的用戶的權限執行。關鍵字 `EXTERNAL` 的目的是和 SQL 兼容，它是可選的，因為這個特性適用于所有函數，而不僅僅在SQL中的外部函數。 The key word is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not only external ones. `_execution_cost_` 一個正數，表示以 [cpu_operator_cost](#calibre_link-735) 為單位的該函數的估算的執行代價。如果函數返回的是一個結果集，它表示的是每行結果的代價。如果這個選項沒有指定，對于用C語言函數和內部函數缺省值為 1，而其他語言定義的函數缺省值為 100。較大的值會使查詢計劃生成器在不必要的情況下盡量避免調用該函數。 `_result_rows_` 一個正數給出了查詢計劃生成器預期的該函數返回的估算的結果集行數，它僅允許返回結果集的函數指定。缺省值為 1000。 `_configuration_parameter_` `_value_` `SET` 語句能在進入函數時將某個配置參數設置成指定的值，并且當函數返回時恢復成之前的值。`SET FROM CURRENT` 使用會話當前的該參數值做為進入函數時的該參數的值。如果一個函數創建時帶有某個配置參數的`SET` 語句，那么在函數內對于同一參數的`SET LOCAL` 語句的作用域僅限于該函數：執行函數之前的該參數的值會在函數返回后恢復。然而一個一般的同一參數的 `SET` 語句（不帶 `LOCAL`）的作用效果將在函數返回后繼續保持，直到當前的事務回滾。關于允許設置的參數和值的進一步信息請參考 [SET](#calibre_link-675) 和 [Chapter 18](#calibre_link-500)。 `_definition_` 一個定義函數的字符串常量，含義取決于語言。它可以是一個內部函數名字、一個指向某個目標文件的路徑、一個 SQL 查詢、一個過程語言文本。在寫函數體字符串時經常使用美元符引用語法（見[Section 4.1.2.4](#calibre_link-736)），而不通常的單引號語法，這是因為如果不使用美元符引用語法，在函數定義中出現的任何單引號和反斜線都需要前面再加一個相應的相同字符來轉義。 `_obj_file_`, `_link_symbol_` 這個形式的 `AS` 子句用于在函數的 C 源文件里的名字和 SQL 名字不同時可動態加載 C 語言函數。字符串 `_obj_file_` 是包含可動態加載對象的文件名，而 `_link_symbol_` 是函數的鏈接符號，也就是該函數在 C 源文件里的名字。如果省略了鏈接符號，那么就假設它和被定義的 SQL 函數同名。當使用同一個可動態加載文件重復執行命令 `CREATE FUNCTION` 生成函數時，在一個全會話里該文件只會被加載一次。若想卸載或重新加載該文件（通常在開發過程中使用），可以開啟一個新的會話。 `_attribute_` 歷史遺留的函數可選信息。下面的屬性可以在此出現： `isStrict` 等效于 `STRICT` 或 `RETURNS NULL ON NULL INPUT`。 `isCachable` `isCachable` 是 `IMMUTABLE` 的過時的等效語法；不過出于向下兼容，仍然接受它。屬性名是大小寫無關的。請參閱[Section 35.3](#calibre_link-737)獲取更多關于書寫函數的信息。 ## 重載 PostgreSQL 允許函數_重載_；也就是只要輸入參數不同，幾個不同的函數可以同名。不過，所有函數的 C 名字必須不同，也就是說你必須給予重載的 C 函數不同的 C 名字(比如，使用參數類型作為 C 名字的一部分)。如果兩個函數同名，并且_輸入_參數類型也相同，那么就認為這兩個函數是一樣的，忽略所有 `OUT` 參數。因此，下面的聲明是沖突的： ``` CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ... ``` 生成同名的但有不同的參數個數的函數是沒有問題的，但是如果定義了默認值在調用時就有可能產生沖突，例如，考慮下面定義 ``` CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ... ``` 函數調用`foo(10)`將會失敗，因為系統不知道該調用哪一個函數版本。 ## 注意允許你將完整的 SQL 類型語法用于輸入參數和返回值。不過，有些類型聲明的細節(比如 `numeric` 類型的精度域)是由下層函數實現負責的，并且會被 `CREATE FUNCTION` 命令悄悄地吞掉(也就是不再被識別或強制)。在使用 `CREATE OR REPLACE FUNCTION` 替換一個已存在函數的定義時，改變函數的參數名字有一些限制。你不能對已使用了名字的輸入參數改名（但是你可以給沒有使用名字的輸入參數加一個名字）。如果該函數有超過一個輸出參數，你也不能改變輸出參數的名字，因為改名會改變用來描述函數返回結果的匿名復合類型的列名。這些限制都是為了使在替換函數的定義后對該函數的已有調用還能正常工作。如果一個函數聲名為 `STRICT`，并帶有一個 `VARIADIC` 模式的參數，參數的嚴格性檢查將可變數組_整體_看作非空的（non-null）。所以當調用時參數數組有空（null）元素時，該函數仍然會被調用。 ## 例子這里是一些簡單的例子，用于幫助你開始掌握這個命令。更多信息和例子，參閱[Section 35.3](#calibre_link-737)。 ``` CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; ``` 利用參數名用 PL/pgSQL 自增一個整數： ``` CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; END; $$ LANGUAGE plpgsql; ``` 返回一個包含多個輸出參數的記錄： ``` CREATE FUNCTION dup(in int, out f1 int, out f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); ``` 你可以通過命名明確的復合類型的方法冗長地干同樣的事情： ``` CREATE TYPE dup_result AS (f1 int, f2 text); CREATE FUNCTION dup(int) RETURNS dup_result AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); ``` 另一個返回多列的方法是用 `TABLE` 函數： ``` CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text) AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$ LANGUAGE SQL; SELECT * FROM dup(42); ``` 然而，`TABLE`是與前面的例子完全不同的，因為它實際上返回的是一個結果_集_，而不僅僅是一條記錄。 ## 編寫安全的 `SECURITY DEFINER` 函數因為`SECURITY DEFINER`函數是以創建它的用戶的權限執行的，所以一定確保這樣的函數不被濫用。為了安全，應該設置 [search_path](#calibre_link-738) 排除可以被任何不信任用戶更改的模式。這能避免惡意用戶用生成的對象來替代函數中用到的對象的攻擊方法。特別應該注意的是臨時表模式，該模式默認在搜索路徑中排在第一個，并且對任何用戶可寫。為安全考慮可以強制臨時表模式最后被搜索。為了達到這個目的，可以將 `pg_temp` 放到`search_path`的最后。下面是安全定義函數的一個例子： ``` CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; BEGIN SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; RETURN passed; END; $$ LANGUAGE plpgsql SECURITY DEFINER -- 設置安全的 search_path: 信任的模式, 然后是 'pg_temp' 模式 SET search_path = admin, pg_temp; ``` 因為 PostgreSQL 8.3 版本沒有 `SET` 選項，所以老函數可以包含比較復雜的邏輯去存儲，設置和恢復 `search_path` 參數，而使用 `SET` 選項則簡單的多。另一個需要注意的是：默認新創建的函數的執行權限被授予了 `PUBLIC`，（更多信息見[GRANT](#calibre_link-19)）。更常見的是你希望限制安全函數只給某些用戶使用。為了實現這個目的，你必須收回默認的給 `PUBLIC`的權限，并且單獨給選定用戶分配權限。為消除新函數能被所有人執行的時間窗口，可以在一個事務中創建和設置函數權限，例如： ``` BEGIN; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins; COMMIT; ``` ## 兼容性 PostgreSQL 里的版本和 SQL:1999 里的 `CREATE FUNCTION` 命令類似但是不完全兼容。屬性和可以使用的語言都是不可移植的。為了和一些其它的數據庫系統兼容， `_argmode_` 可以在 `_argname_` 之前或者之后寫，但是只有第一種寫法是與標準兼容的。對于默認參數，SQL標準僅僅規定了使用 `DEFAULT` 關鍵字的語法。在T-SQL 和 Firebird 中，也使用 `=` 的語法。 ## 參見 [ALTER FUNCTION](#calibre_link-588), [DROP FUNCTION](#calibre_link-587), [GRANT](#calibre_link-19), [LOAD](#calibre_link-739), [REVOKE](#calibre_link-20), [createlang](#calibre_link-47)