?
本文檔使用 PHP中文網(wǎng)手冊 發(fā)布
CREATE FUNCTION
Name
CREATE FUNCTION?--?定義一個(gè)新函數(shù)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
| 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 定義一個(gè)新的函數(shù)。 CREATE OR REPLACE FUNCTION將要么創(chuàng)建一個(gè)新函數(shù)， 要么替換現(xiàn)有的定義。 為了能夠定義一個(gè)函數(shù)，用戶必須語言有USAGE權(quán)限。
如果包含了一個(gè)模式名，那么函數(shù)就在指定的模式中創(chuàng)建。否則 它會(huì)在當(dāng)前模式中創(chuàng)建。 新函數(shù)的名稱不能通過同一模式中的相同輸入?yún)?shù)類型匹配任何現(xiàn)有功能。 然而，不同參數(shù)類型的功能可以共享一個(gè)名稱(此處名為 overloading)。
為了替換一個(gè)已有函數(shù)的當(dāng)前定義，使用 CREATE OR REPLACE FUNCTION。要這樣改變一個(gè)函數(shù)的 名稱或者參數(shù)類型是不可能的（若你嘗試過，實(shí)際上您會(huì)創(chuàng)建一個(gè)新的不同的函數(shù)）。 另外，CREATE OR REPLACE FUNCTION不會(huì)讓你改變一個(gè)存在 函數(shù)的返回類型。為了做到那些，你必須刪除并重新創(chuàng)建函數(shù)。 （當(dāng)你使用OUT參數(shù)，那意味著您不能更改任何參數(shù)的類型，除了通過 刪除該函數(shù)以外。）
當(dāng)CREATE OR REPLACE FUNCTION 用于替換一個(gè)已有函數(shù)，函數(shù)的 所有權(quán)限和學(xué)科權(quán)限不會(huì)改變。所有其他函數(shù)屬性都指定了在命令中指定或者 隱含的數(shù)值。你必須擁有此函數(shù)來替換它（這包括成為所屬關(guān)系的一員）。
如果你刪除然后重建一個(gè)函數(shù)，新函數(shù)和舊函數(shù)將是不同的實(shí)體； 你就需要?jiǎng)h除現(xiàn)有引用了老函數(shù)的規(guī)則、視圖、觸發(fā)器等等。 使用CREATE OR REPLACE FUNCTION可以在不 破壞引用該函數(shù)的對象的前提下修改函數(shù)定義。 另外，ALTER FUNCTION可以用于更改一個(gè)已有函數(shù)的大多數(shù)輔助屬性。
創(chuàng)建這個(gè)函數(shù)的用戶將成為函數(shù)的所有者。
Parameters
- name
要?jiǎng)?chuàng)建的函數(shù)名字(可以用模式修飾)
- argmode
一個(gè)參數(shù)的模式是：IN, OUT, INOUT,或者VARIADIC. 若被遺漏，缺省為IN。只有OUT參數(shù) 可以遵循一個(gè)VARIADIC。另外， OUT and INOUT參數(shù)不能RETURNS TABLE 符號同時(shí)使用。
- argname
一個(gè)參數(shù)的名字。有些語言(目前只有 PL/pgSQL)允許你在函數(shù)體里使用 參數(shù)名字。對于其他語言，一個(gè)輸入?yún)?shù)的名稱僅僅是額外的文檔， 就函數(shù)本身而言；但是你可以在調(diào)用一個(gè)函數(shù)來提高可讀性時(shí)使用輸入 參數(shù)名稱（參閱Section 4.3）； 總之，一個(gè)輸出參數(shù)的名稱是重要的，因?yàn)樗诮Y(jié)果行類型中定義了列名稱。 (如果你忽略了輸出參數(shù)的名稱，系統(tǒng)將會(huì)選擇一個(gè)缺省列名稱。）
- argtype
-
函數(shù)參數(shù)的數(shù)據(jù)類型（可以有模式修飾），若存在。參數(shù)類型可以是基礎(chǔ)、 符合或者域類型，或者可以引用一個(gè)表列的類型。
基于實(shí)現(xiàn)語言，也可以允許聲明"pseudotypes"，例如cstring. 假類型表明實(shí)際參數(shù)類型要么是不完全指定，要么是在普通SQL數(shù)據(jù)類型組之外。
一列的類型通過寫table_name 來引用。column_name%TYPE。 使用該特性有時(shí)可以幫助使一個(gè)函數(shù)獨(dú)立于表定義的改變。
- default_expr
若未聲明參數(shù)，表達(dá)式可用作缺省值。表達(dá)式必須是對參數(shù)的自變量類型可強(qiáng)制的。 只有輸入（包括INOUT) 參數(shù)可以有一個(gè)默認(rèn)值。所有跟著一個(gè) 有缺省值的參數(shù)的輸入?yún)?shù)也必須有缺省值。
- rettype
-
返回?cái)?shù)據(jù)類型（可以有模式修飾）。返回類型可以是一個(gè)基礎(chǔ)、符合或者域類型， 或者可以參照表列的類型?；趯?shí)現(xiàn)語言，也可以允許聲明"pseudotypes"， 例如cstring。若函數(shù)沒能返回一個(gè)值，則指定void為返回類型。
當(dāng)有OUT或者INOUT參數(shù)時(shí)，RETURNS 子句可以忽略。如果存在，必須與輸出參數(shù)暗示的結(jié)果類型相一致： 若有多個(gè)輸出參數(shù)則為RECORD，或者是單一輸參數(shù)的相同類型。
SETOF修飾詞表示該函數(shù)將返回一個(gè)集合， 而不是單獨(dú)一項(xiàng)。
列的類型是通過寫 table_name.column_name%TYPE 來參照的。
- column_name
在RETURNS TABLE語法中的一個(gè)輸出列的名稱。 這是生命另一個(gè)命名的OUT參數(shù)的有效途徑，除了 RETURNS TABLE也蘊(yùn)含RETURNS SETOF。
- column_type
RETURNS TABLE語法中的一個(gè)輸出列的數(shù)據(jù)類型。
- lang_name
函數(shù)實(shí)施語言的名稱?？梢允?tt class="LITERAL">SQL, C, internal或者是一個(gè)用戶定義的程序語言的名稱。 為了向后兼容，名稱可以通過單引號括起來。
- WINDOW
WINDOW表明函數(shù)是window function 而不是一個(gè)普通的函數(shù)。這目前僅僅對用C寫的函數(shù)有用。 WINDOW屬性可以在替換現(xiàn)有的函數(shù)定義時(shí)更改。
-
IMMUTABLE
STABLE
VOLATILE -
這些屬性告訴系統(tǒng)把對該函數(shù)的多次調(diào)用替換成一次是否安全， 主要用于運(yùn)行時(shí)優(yōu)化。缺省是VOLATILE。
IMMUTABLE表示該函數(shù)不能修改數(shù)據(jù)庫，并且 在給出同樣的參數(shù)值時(shí)總是返回同樣的結(jié)果；也就是說，它不查詢 數(shù)據(jù)庫或者只使用那些沒有出現(xiàn)在參數(shù)列表里的信息。如果給出這 個(gè)選項(xiàng)，那么任何全部使用常數(shù)對該函數(shù)的調(diào)用都將立即替換為該 函數(shù)的值。
STABLE表明函數(shù)不能調(diào)整數(shù)據(jù)庫，并且在一個(gè)獨(dú)立表掃 描中，將會(huì)始終對相同的參數(shù)值返回相同的結(jié)果，但是其結(jié)果會(huì)在SQL語句上 有所改變。這是函數(shù)的恰當(dāng)選擇，而且函數(shù)的結(jié)果取決于數(shù)據(jù)庫查找的、參數(shù)變量 （如當(dāng)前時(shí)區(qū)）等等。（ 希望查詢當(dāng)前命令剛改的行，這對于AFTER 觸發(fā)器來說是不恰當(dāng)?shù)?。）另外還要注意：函數(shù)的
current_timestamp類是穩(wěn)定的，盡管它們的值不會(huì)在事務(wù)內(nèi)改變。VOLATILE表示該函數(shù)值甚至可以在一次表掃描內(nèi)改變， 因此不會(huì)做任何優(yōu)化。只有很少的數(shù)據(jù)庫函數(shù)在這個(gè)概念上是易變的； 一些例子是random(),currval(), timeofday()。請注意任何有副作用的函數(shù)都必需列為易變類， 即使其結(jié)果相當(dāng)有規(guī)律也應(yīng)該這樣，這樣才能避免它被優(yōu)化；一個(gè)例子就是 setval()。
更多細(xì)節(jié)，請參閱節(jié)Section 35.6。
-
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT -
CALLED ON NULL INPUT(缺省)表明該函數(shù)在自己的 某些參數(shù)是 NULL 的時(shí)候還是可以按照正常的方式調(diào)用。函數(shù)的作者必須 負(fù)責(zé)檢查 NULL 以及相應(yīng)地做出反應(yīng)。
RETURNS NULL ON NULL INPUT或 STRICT表明如果它的任何參數(shù)是NULL ， 此函數(shù)總是返回 NULL 。如果聲明了這個(gè)參數(shù)，則如果存在 NULL 參數(shù)時(shí)不會(huì)執(zhí)行該函數(shù)；而只是自動(dòng)假設(shè)一個(gè)NULL結(jié)果。
-
[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER -
SECURITY INVOKER(缺省)表明該函數(shù)將帶 著調(diào)用它的用戶的權(quán)限執(zhí)行。SECURITY DEFINER 聲明該函數(shù)將以創(chuàng)建它的用戶的權(quán)限執(zhí)行。
關(guān)鍵字EXTERNAL的目的是和 SQL 兼容， 但是它是可選的，因?yàn)檫@個(gè)特性適合于所有函數(shù)，而不僅僅外部函數(shù)。
- execution_cost
正值給出函數(shù)的預(yù)計(jì)執(zhí)行成本，以cpu_operator_cost 為單位。如果函數(shù)返回了一個(gè)集，這就是每個(gè)返回行的成本。如果沒有聲明成本， 1單位假定為C語言和內(nèi)部函數(shù)，而100單位為所有其他語言的函數(shù)。較大的數(shù)值 使得規(guī)劃器試著不需要地頻繁的去避免評估函數(shù)。
- result_rows
A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows. 正數(shù)給出了規(guī)劃器希望函數(shù)返回的估計(jì)行數(shù)。這僅僅在函數(shù)聲明返回一個(gè)集時(shí) 是允許的。默認(rèn)假設(shè)為1000行。
-
configuration_parameter
value -
SET子句使特定配置參數(shù)在函數(shù)被輸入時(shí)被設(shè)置為指定的值， 然后當(dāng)函數(shù)退出時(shí)恢復(fù)到之前的值。SET FROM CURRENT保存 參數(shù)的會(huì)話的當(dāng)前值為輸入函數(shù)時(shí)使用的值。
如果一個(gè)SET子句附屬于一個(gè)函數(shù)，那么一個(gè)在函數(shù)內(nèi)為同以變量 執(zhí)行的SET LOCAL命令的影響限制在函數(shù)內(nèi)：配置參數(shù)優(yōu)先值 仍然在函數(shù)退出時(shí)存儲。然而，一個(gè)普通的SET命令（無 LOCAL）重寫SET子句，正如它會(huì)為一個(gè)先前 SET LOCAL命令所做的：這樣一個(gè)命令的影響將會(huì)在函數(shù)退出后繼續(xù)， 除非當(dāng)前事務(wù)回滾。
參閱SET和Chapter 18 獲取更多關(guān)于允許的參數(shù)名稱和數(shù)值的相關(guān)信息。
- definition
-
一個(gè)字符串常量定義函數(shù)；含義依托于語言。可以是一個(gè)內(nèi)部函數(shù)名稱， 對象文件的路徑、一個(gè)SQL命令，或者過程語言的文本。
使用美元引用（參閱Section 4.1.2.4） 來寫函數(shù)定義字符串通常是很有效的，而不是普通的單引號語法。 沒有美元引用，任何函數(shù)定義中的單引號或者反斜杠必須通過對加倍來 使他們逃逸。
- obj_file, link_symbol
-
這個(gè)形式的AS子句用于在函數(shù)的 C 源文件里的名字和 SQL 名字不同時(shí)可動(dòng)態(tài)加載 C 語言函數(shù)。字符串 obj_file是包含可動(dòng)態(tài) 加載對象的文件名，而link_symbol 是函數(shù)的鏈接符號，也就是該函數(shù)在 C 源文件里的名字。如果省略了鏈接符號， 那么就假設(shè)它和被定義的 SQL 函數(shù)同名。
當(dāng)重復(fù)指向相同對象文件的CREATE FUNCTION調(diào)用時(shí)， 該文件僅在每次會(huì)話時(shí)加載一次。為了卸載和重新加載文件（可能在開發(fā)期間）， 開始一個(gè)新會(huì)話。
- attribute
-
指定函數(shù)的可選內(nèi)容的歷史方法。以下屬性可以出現(xiàn)在這里：
- isStrict
等效于STRICT或 RETURNS NULL ON NULL INPUT
- isCachable
isCachable是IMMUTABLE 的過時(shí)的等效物；不過出于向下兼容，仍然接受它。
屬性名是大小寫無關(guān)的。
請參閱節(jié)Section 35.3獲取更多關(guān)于書寫函數(shù)的信息。
Overloading
PostgreSQL允許函數(shù)overloading； 也就是說，只要有不同的輸入?yún)?shù)類型，相同的名稱可以用于不同的函數(shù)。然而，所有函數(shù) 的C名稱必須是不同的，所以你必須給超載的C函數(shù)不同的C名稱（例如，使用參數(shù)類型 作為C名稱的一部分）。
如果兩個(gè)函數(shù)同名，并且input參數(shù)類型也相同，那么 就認(rèn)為這兩個(gè)函數(shù)是一樣的，忽略所有OUT參數(shù)。因此， 下面的聲明是沖突的：
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...
有不同參數(shù)類型列表的函數(shù)在創(chuàng)建時(shí)認(rèn)為是不沖突的，但是如果提供缺省值 可能會(huì)在使用時(shí)沖突。例如，考慮
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int default 42) ...
一個(gè)調(diào)用foo(10)將會(huì)失敗，由于函數(shù)應(yīng)該被調(diào)用的歧義。
Notes
完整的SQL類型語法對輸入?yún)?shù)和返回值來說是允許的。 然而，類型指定的一些詳細(xì)信息（例如：類型numeric的精度域） 是根本函數(shù)執(zhí)行的相應(yīng)度并被CREATE FUNCTION命令悄然 吞下。
當(dāng)用CREATE OR REPLACE FUNCTION替換現(xiàn)有函數(shù)時(shí)，在更改 參數(shù)名稱時(shí)有一些限制。你不能改變這些已經(jīng)分配給所有輸入?yún)?shù)的名稱 （盡管你可以將名稱添加到之前什么都沒有的參數(shù)）。若有多于一個(gè)輸出參數(shù)， 你不能改變輸出參數(shù)的名稱，因?yàn)槟菚?huì)改變描述函數(shù)結(jié)構(gòu)的匿名符合類型的列名稱。 這些限制是用來保證已有函數(shù)調(diào)用在替換時(shí)不停止工作。
If a function is declared STRICT with a VARIADIC argument, the strictness check tests that the variadic array as a whole is non-null. The function will still be called if the array has null elements. 若一個(gè)函數(shù)通過VARIADIC參數(shù)聲明STRICT，嚴(yán)格檢查測試 可變參數(shù)的數(shù)組as a whole是非空的。若數(shù)組有空元素則函數(shù)將仍被 調(diào)用。
Examples
這里是一些簡單的例子，用于幫助你開始掌握這個(gè)命令。更多信息和例子， 參閱節(jié)Section 35.3。
CREATE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
利用參數(shù)名用PL/pgSQL自增一個(gè)整數(shù)：
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
返回一個(gè)包含多個(gè)輸出參數(shù)的記錄：
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);
你可以通過命名明確的復(fù)合類型的訪法冗長地干同樣的事情：
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);
返回多個(gè)列的另一種方法是使用一個(gè)TABLE函數(shù)：
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);
然而，一個(gè)TABLE函數(shù)是不同于前面的例子的，因?yàn)樗鼤?huì)返回一個(gè) 記錄的set，而不僅僅是一個(gè)記錄。
寫SECURITY DEFINER函數(shù)安全性
因?yàn)橐粋€(gè)SECURITY DEFINER函數(shù)執(zhí)行創(chuàng)建它的用戶的權(quán)限， 因此需要謹(jǐn)慎以確保該函數(shù)不被濫用。為了安全，search_path 應(yīng)該被用來排除不可信用戶可寫的任何模式。這可以阻止惡意用戶創(chuàng)建偽裝的 函數(shù)所用的對象。在這方面臨時(shí)表模式是特別重要的，該模式在默認(rèn)情況下是 第一個(gè)被搜索的，并且通常是任何人都可寫的。通過強(qiáng)制臨時(shí)模式最后被查詢可以 得到安全的布置。要這樣做，就要將pg_temp寫作search_path 中的最后一條記錄。下面這個(gè)函數(shù)闡明了安全使用方法：
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
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
SET search_path = admin, pg_temp;
在PostgreSQL8.3版本之前，SET 選項(xiàng)是無法使用的，所以舊函數(shù)可能可能會(huì)包含相當(dāng)復(fù)雜的邏輯來保存、 設(shè)置或者還原search_path。SET選項(xiàng)用于此目的是 非常容易的。
要注意的另一點(diǎn)是：在默認(rèn)情況下，執(zhí)行權(quán)限為新創(chuàng)建的函數(shù)授予PUBLIC （參閱GRANT獲取更多信息）。您常常希望將安全定義函數(shù) 的使用僅僅限制到一部分用戶。要那樣做，您必須撤銷默認(rèn)PUBLIC權(quán)限 然后選擇性地授予執(zhí)行權(quán)限。為了避免有一個(gè)新函數(shù)開放訪問的窗口，需要?jiǎng)?chuàng)建它 并且設(shè)置權(quán)限在單一事務(wù)中。例如：
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;
兼容性
它和 SQL:1999 里的CREATE FUNCTION命令類似但是不兼容。 屬性和可以使用的語言都是不可移植的。
為了和一些其它的數(shù)據(jù)庫系統(tǒng)兼容，argmode 可以在 argname 之前或者之后寫， 但是只有第一種訪法是標(biāo)準(zhǔn)兼容的。
SQL標(biāo)準(zhǔn)不聲明參數(shù)默認(rèn)值。有DEFAULT關(guān)鍵字的語法 來自于Oracle，并且有些本著標(biāo)準(zhǔn)的精神：SQL/PSM將它用于變量默認(rèn)值。 =的語法用于T-SQL和Firebird。