PostgreSQL CREATE FUNCTION

2021-09-09 10:57 更新

CREATE FUNCTION — 定義一個(gè)新函數(shù)

大綱

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

描述

CREATE FUNCTION定義一個(gè)新函數(shù)。CREATE OR REPLACE FUNCTION將創(chuàng)建一個(gè)新函數(shù)或者替換一個(gè)現(xiàn)有的函數(shù)。要定義一個(gè)函數(shù)，用戶必須具有該語(yǔ)言上的USAGE特權(quán)。

如果包括了一個(gè)模式名，那么該函數(shù)會(huì)被創(chuàng)建在指定的模式中。否則，它會(huì)被創(chuàng)建在當(dāng)前模式中。新函數(shù)的名稱(chēng)不能匹配同一個(gè)模式中具有相同輸入?yún)?shù)類(lèi)型的任何現(xiàn)有函數(shù)或過(guò)程。不過(guò)，不同參數(shù)類(lèi)型的函數(shù)和過(guò)程能夠共享一個(gè)名字（這被稱(chēng)作重載）。

要替換一個(gè)現(xiàn)有函數(shù)的當(dāng)前定義，可以使用CREATE OR REPLACE FUNCTION。但不能用這種方式更改函數(shù)的名稱(chēng)或者參數(shù)類(lèi)型（如果嘗試這樣做，實(shí)際上就會(huì)創(chuàng)建一個(gè)新的不同的函數(shù)）。還有，CREATE OR REPLACE FUNCTION將不會(huì)讓你更改一個(gè)現(xiàn)有函數(shù)的返回類(lèi)型。要這樣做，你必須先刪除再重建該函數(shù)（在使用OUT參數(shù)時(shí)，這意味著除了刪除函數(shù)之外無(wú)法更改任何 OUT參數(shù)的類(lèi)型）。

當(dāng)CREATE OR REPLACE FUNCTION被用來(lái)替換一個(gè)現(xiàn)有的函數(shù)，該函數(shù)的擁有權(quán)和權(quán)限不會(huì)改變。所有其他的函數(shù)屬性會(huì)按照該命令中所指定的或者隱含的來(lái)賦值。必須擁有（包括成為擁有角色的成員）該函數(shù)才能替換它。

如果你刪除并且重建一個(gè)函數(shù)，新函數(shù)將和舊的不一樣，你將必須刪掉引用舊函數(shù)的現(xiàn)有規(guī)則、視圖、觸發(fā)器等。使用CREATE OR REPLACE FUNCTION更改一個(gè)函數(shù)定義不會(huì)破壞引用該函數(shù)的對(duì)象。還有，ALTER FUNCTION可以被用來(lái)更改一個(gè)現(xiàn)有函數(shù)的大部分輔助屬性。

創(chuàng)建該函數(shù)的用戶將成為該函數(shù)的擁有者。

要?jiǎng)?chuàng)建一個(gè)函數(shù)，你必須擁有參數(shù)類(lèi)型和返回類(lèi)型上的USAGE特權(quán)。

參數(shù)

name

要?jiǎng)?chuàng)建的函數(shù)的名稱(chēng)（可以被模式限定）。

argmode

一個(gè)參數(shù)的模式：IN、OUT、INOUT或者VARIADIC。如果省略，默認(rèn)為IN。只有OUT參數(shù)能跟在一個(gè)VARIADIC參數(shù)后面。還有， OUT和INOUT參數(shù)不能和RETURNS TABLE符號(hào)一起使用。

argname

一個(gè)參數(shù)的名稱(chēng)。一些語(yǔ)言（包括 SQL 和 PL/pgSQL）讓你在函數(shù)體中使用該名稱(chēng)。對(duì)于其他語(yǔ)言，一個(gè)輸入?yún)?shù)的名字只是額外的文字（就該函數(shù)本身所關(guān)心的來(lái)說(shuō)）。但是你可以在調(diào)用一個(gè)函數(shù)時(shí)使用輸入?yún)?shù)名來(lái)提高可讀性（見(jiàn)第 4.3 節(jié)）。在任何情況下，輸出參數(shù)的名稱(chēng)是有意義的，因?yàn)樗x了結(jié)果行類(lèi)型中的列名（如果忽略一個(gè)輸出參數(shù)的名稱(chēng)，系統(tǒng)將選擇一個(gè)默認(rèn)的列名）。

argtype

該函數(shù)參數(shù)（如果有）的數(shù)據(jù)類(lèi)型（可以是模式限定的）。參數(shù)類(lèi)型可以是基本類(lèi)型、組合類(lèi)型或者域類(lèi)型，或者可以引用一個(gè)表列的類(lèi)型。

根據(jù)實(shí)現(xiàn)語(yǔ)言，也可以允許指定cstring之類(lèi)的“偽類(lèi)型”。偽類(lèi)型表示實(shí)際參數(shù)類(lèi)型沒(méi)有被完整指定或者不屬于普通 SQL 數(shù)據(jù)類(lèi)型集合。

可以寫(xiě)table_name .column_name%TYPE來(lái)引用一列的類(lèi)型。使用這種特性有時(shí)可以幫助創(chuàng)建一個(gè)不受表定義更改影響的函數(shù)。

default_expr

如果參數(shù)沒(méi)有被指定值時(shí)要用作默認(rèn)值的表達(dá)式。該表達(dá)式必須能被強(qiáng)制為該參數(shù)的參數(shù)類(lèi)型。只有輸入（包括INOUT）參數(shù)可以具有默認(rèn)值。所有跟隨在一個(gè)具有默認(rèn)值的參數(shù)之后的輸入?yún)?shù)也必須有默認(rèn)值。

rettype

返回?cái)?shù)據(jù)類(lèi)型（可能被模式限定）。返回類(lèi)型可以是一種基本類(lèi)型、組合類(lèi)型或者域類(lèi)型，也可以引用一個(gè)表列的類(lèi)型。根據(jù)實(shí)現(xiàn)語(yǔ)言，也可以允許指定cstring之類(lèi)的“偽類(lèi)型”。如果該函數(shù)不會(huì)返回一個(gè)值，可以指定返回類(lèi)型為void。

當(dāng)有OUT或者INOUT參數(shù)時(shí)，可以省略RETURNS子句。如果存在，該子句必須和輸出參數(shù)所表示的結(jié)果類(lèi)型一致：如果有多個(gè)輸出參數(shù)，則為RECORD，否則與單個(gè)輸出參數(shù)的類(lèi)型相同。

SETOF修飾符表示該函數(shù)將返回一個(gè)項(xiàng)的集合而不是一個(gè)單一項(xiàng)。

可以寫(xiě)table_name .column_name%TYPE來(lái)引用一列的類(lèi)型。

column_name

RETURNS TABLE語(yǔ)法中一個(gè)輸出列的名稱(chēng)。這實(shí)際上是另一種聲明OUT參數(shù)的方法，不過(guò)RETURNS TABLE也隱含了RETURNS SETOF。

column_type

RETURNS TABLE語(yǔ)法中的輸出列的數(shù)據(jù)類(lèi)型。

lang_name

用以實(shí)現(xiàn)該函數(shù)的語(yǔ)言的名稱(chēng)?？梢允?code class="literal">sql、c、internal或者一個(gè)用戶定義的過(guò)程語(yǔ)言的名稱(chēng)，例如plpgsql。不推薦用單引號(hào)包圍該名稱(chēng)，并且要求區(qū)分大小寫(xiě)。

TRANSFORM { FOR TYPE type_name } [, ... ] }

一個(gè)由轉(zhuǎn)換構(gòu)成的列表，對(duì)該函數(shù)的調(diào)用適用于它們。轉(zhuǎn)換在 SQL 類(lèi)型和語(yǔ)言相關(guān)的數(shù)據(jù)類(lèi)型之間進(jìn)行變換，詳見(jiàn)CREATE TRANSFORM。過(guò)程語(yǔ)言實(shí)現(xiàn)通常把有關(guān)內(nèi)建類(lèi)型的知識(shí)硬編碼在代碼中，因此那些不需要列舉在這里。如果一種過(guò)程語(yǔ)言實(shí)現(xiàn)不知道如何處理一種類(lèi)型并且沒(méi)有轉(zhuǎn)換被提供，它將回退到一種默認(rèn)的行為來(lái)轉(zhuǎn)換數(shù)據(jù)類(lèi)型，但是這取決于具體實(shí)現(xiàn)。

WINDOW

WINDOW表示該函數(shù)是一個(gè)窗口函數(shù)而不是一個(gè)普通函數(shù)。當(dāng)前只用于用 C 編寫(xiě)的函數(shù)。在替換一個(gè)現(xiàn)有函數(shù)定義時(shí)，不能更改WINDOW屬性。

IMMUTABLE
STABLE
VOLATILE

這些屬性告知查詢優(yōu)化器該函數(shù)的行為。最多只能指定其中一個(gè)。如果這些都不出現(xiàn)，則會(huì)默認(rèn)為VOLATILE。

IMMUTABLE表示該函數(shù)不能修改數(shù)據(jù)庫(kù)并且對(duì)于給定的參數(shù)值總是會(huì)返回相同的值。也就是說(shuō)，它不會(huì)做數(shù)據(jù)庫(kù)查找或者使用沒(méi)有在其參數(shù)列表中直接出現(xiàn)的信息。如果給定合格選項(xiàng)，任何用全常量參數(shù)對(duì)該函數(shù)的額調(diào)用可以立刻用該函數(shù)值替換。

STABLE表示該函數(shù)不能修改數(shù)據(jù)庫(kù)，并且對(duì)于相同的參數(shù)值，它在一次表掃描中將返回相同的結(jié)果。但是這種結(jié)果在不同的 SQL 語(yǔ)句執(zhí)行期間可能會(huì)變化。對(duì)于那些結(jié)果依賴(lài)于數(shù)據(jù)庫(kù)查找、參數(shù)變量（例如當(dāng)前時(shí)區(qū)）等的函數(shù)來(lái)說(shuō)，這是合適的（對(duì)希望查詢被當(dāng)前命令修改的行的AFTER觸發(fā)器不適合）。還要注意current_timestamp函數(shù)族適合被標(biāo)記為穩(wěn)定，因?yàn)樗鼈兊闹翟谝粋€(gè)事務(wù)內(nèi)不會(huì)改變。

VOLATILE表示該函數(shù)的值在一次表掃描中都有可能改變，因此不能做優(yōu)化。在這種意義上，相對(duì)較少的數(shù)據(jù)庫(kù)函數(shù)是不穩(wěn)定的，一些例子是random()、currval()、timeofday()。但是注意任何有副作用的函數(shù)都必須被分類(lèi)為不穩(wěn)定的，即便其結(jié)果是可以預(yù)測(cè)的，這是為了調(diào)用被優(yōu)化掉。一個(gè)例子是 setval()。

更多細(xì)節(jié)可見(jiàn)第 37.7 節(jié)。

LEAKPROOF

LEAKPROOF表示該函數(shù)沒(méi)有副作用。它不會(huì)泄露有關(guān)其參數(shù)的信息（除了通過(guò)返回值）。例如，一個(gè)只對(duì)某些參數(shù)值拋出錯(cuò)誤消息而對(duì)另外一些卻不拋出錯(cuò)誤的函數(shù)不是防泄漏的，一個(gè)把參數(shù)值包括在任何錯(cuò)誤消息中的函數(shù)也不是防泄漏的。這會(huì)影響系統(tǒng)如何執(zhí)行在使用security_barrier選項(xiàng)創(chuàng)建的視圖或者開(kāi)啟了行級(jí)安全性的表上執(zhí)行查詢。對(duì)于包含有非防泄漏函數(shù)的查詢，系統(tǒng)將在任何來(lái)自查詢本身的用戶提供條件之前強(qiáng)制來(lái)自安全策略或者安全屏障的條件，防止無(wú)意中的數(shù)據(jù)暴露。被標(biāo)記為防泄漏的函數(shù)和操作符被假定是可信的，并且可以在安全性策略和安全性屏障視圖的條件之前被執(zhí)行。此外，沒(méi)有參數(shù)的函數(shù)或者不從安全屏障視圖或表傳遞任何參數(shù)的函數(shù)不一定要被標(biāo)記為防泄漏的。詳見(jiàn) CREATE VIEW 和第 40.5 節(jié)。這個(gè)選項(xiàng)只能由超級(jí)用戶設(shè)置。

CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT

CALLED ON NULL INPUT（默認(rèn)）表示在某些參數(shù)為空值時(shí)應(yīng)正常調(diào)用該函數(shù)。如果有必要，函數(shù)的作者應(yīng)該負(fù)責(zé)檢查空值并且做出適當(dāng)?shù)南鄳?yīng)。

RETURNS NULL ON NULL INPUT或STRICT表示只要其任意參數(shù)為空值，該函數(shù)就會(huì)返回空值。如果指定了這個(gè)參數(shù)，當(dāng)有空值參數(shù)時(shí)該函數(shù)不會(huì)被執(zhí)行，而是自動(dòng)返回一個(gè)空值結(jié)果。

[EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER表示要用調(diào)用該函數(shù)的用戶的特權(quán)來(lái)執(zhí)行它。這是默認(rèn)值。SECURITY DEFINER指定要用擁有該函數(shù)的用戶的特權(quán)來(lái)執(zhí)行該函數(shù)。

為了符合 SQL，允許使用關(guān)鍵詞EXTERNAL。但是它是可選的，因?yàn)榕c SQL 中不同，這個(gè)特性適用于所有函數(shù)而不僅是那些外部函數(shù)。

PARALLEL

PARALLEL UNSAFE表示該函數(shù)不能在并行模式中運(yùn)行并且 SQL 語(yǔ)句中存在一個(gè)這樣的函數(shù)會(huì)強(qiáng)制使用順序執(zhí)行計(jì)劃。這是默認(rèn)選項(xiàng)。PARALLEL RESTRICTED表示該函數(shù)能在并行模式中運(yùn)行，但是其執(zhí)行被限制在并行組的領(lǐng)導(dǎo)者中。PARALLEL SAFE表示該函數(shù)對(duì)于在并行模式中運(yùn)行是安全的并且不受限制。

如果函數(shù)修改任何數(shù)據(jù)庫(kù)狀態(tài)、會(huì)使用子事務(wù)之類(lèi)的方式改變事務(wù)、訪問(wèn)序列或者對(duì)設(shè)置（如setval）做出持久性的更改，它們就應(yīng)該被標(biāo)記為并行不安全。如果它們?cè)L問(wèn)臨時(shí)表、客戶端連接狀態(tài)、游標(biāo)、預(yù)備語(yǔ)句或者系統(tǒng)無(wú)法在并行模式中同步的本地后端狀態(tài)（例如setseed只能在組領(lǐng)導(dǎo)者中執(zhí)行，因?yàn)榱硪粋€(gè)進(jìn)程所作的更改不會(huì)在領(lǐng)導(dǎo)者中被反映出來(lái)），它們應(yīng)該被標(biāo)為并行受限。通常，如果一個(gè)函數(shù)是受限的或者不安全的卻被標(biāo)成了安全，或者它本來(lái)是不安全的卻被標(biāo)成了受限，在并行查詢中執(zhí)行時(shí)它可能會(huì)拋出錯(cuò)誤或者產(chǎn)生錯(cuò)誤的答案。如果被錯(cuò)誤的標(biāo)記， C 語(yǔ)言函數(shù)理論上可能展現(xiàn)出完全無(wú)法定義的行為，因?yàn)橄到y(tǒng)沒(méi)有辦法保護(hù)自己不受任意的 C 代碼影響，但是在大部分情況下其結(jié)果也不會(huì)比任何其他函數(shù)差到哪里去。如果有疑問(wèn)，函數(shù)應(yīng)該被標(biāo)為UNSAFE，這也是默認(rèn)值。

COST execution_cost

一個(gè)給出該函數(shù)的估計(jì)執(zhí)行代價(jià)的正數(shù)，單位是cpu_operator_cost。如果該函數(shù)返回一個(gè)集合，這就是每個(gè)被返回行的代價(jià)。如果沒(méi)有指定代價(jià)，對(duì) C 語(yǔ)言和內(nèi)部函數(shù)會(huì)指定為 1 個(gè)單位，對(duì)其他語(yǔ)言的函數(shù)則會(huì)指定為 100 單位。更大的值會(huì)導(dǎo)致規(guī)劃器嘗試避免對(duì)該函數(shù)的不必要的過(guò)多計(jì)算。

ROWS result_rows

一個(gè)正數(shù)，它給出規(guī)劃器期望該函數(shù)返回的行數(shù)估計(jì)。只有當(dāng)該函數(shù)被聲明為返回一個(gè)集合時(shí)才允許這個(gè)參數(shù)。默認(rèn)假設(shè)為 1000 行。

SUPPORT support_function

用于此函數(shù)的planner support function的名稱(chēng)（可選的模式限定）。 詳請(qǐng)參見(jiàn)第 37.11 節(jié)。你必須是超級(jí)用戶才能使用此選項(xiàng)。

configuration_parameter
value

SET子句導(dǎo)致進(jìn)入該函數(shù)時(shí)指定配置參數(shù)將被設(shè)置為指定值。并且在該函數(shù)退出時(shí)恢復(fù)到該參數(shù)之前的值。SET FROM CURRENT會(huì)把CREATE FUNCTION被執(zhí)行時(shí)該參數(shù)的當(dāng)前值保存為進(jìn)入該函數(shù)時(shí)將被應(yīng)用的值。

如果一個(gè)SET子句被附加到一個(gè)函數(shù)，那么在該函數(shù)內(nèi)為同一個(gè)變量執(zhí)行的SET LOCAL命令會(huì)被限制于該函數(shù)：在函數(shù)退出時(shí)該配置參數(shù)之前的值仍會(huì)被恢復(fù)。不過(guò)，一個(gè)普通的SET命令（沒(méi)有LOCAL）會(huì)覆蓋SET子句，更像一個(gè)之前的 SET LOCAL命令所做的那樣：這種命令的效果在函數(shù)退出后將會(huì)持續(xù)，除非當(dāng)前事務(wù)被回滾。

更多有關(guān)允許的參數(shù)名和參數(shù)值的信息請(qǐng)見(jiàn)SET和第 19 章。

definition

一個(gè)定義該函數(shù)的字符串常量，其含義取決于語(yǔ)言。它可以是一個(gè)內(nèi)部函數(shù)名、一個(gè)對(duì)象文件的路徑、一個(gè) SQL 命令或者用一種過(guò)程語(yǔ)言編寫(xiě)的文本。

美元引用第 4.1.2.4 節(jié)通常對(duì)書(shū)寫(xiě)函數(shù)定義字符串有所幫助，而普通單引號(hào)語(yǔ)法則不會(huì)有用。如果沒(méi)有美元引用，函數(shù)定義中的任何單引號(hào)或者反斜線必須用雙寫(xiě)來(lái)轉(zhuǎn)義。

obj_file, link_symbol

當(dāng) C 語(yǔ)言源代碼中該函數(shù)的名稱(chēng)與 SQL 函數(shù)的名稱(chēng)不同時(shí)，這種形式的AS子句被用于動(dòng)態(tài)可載入 C 語(yǔ)言函數(shù)。字符串obj_file是包含編譯好的C函數(shù)的動(dòng)態(tài)庫(kù)文件的名稱(chēng)，它會(huì)由LOAD命令解析。字符串 link_symbol 是該函數(shù)的鏈接符號(hào)，也就是該函數(shù)在 C 語(yǔ)言源代碼中的名稱(chēng)。如果省略鏈接符號(hào)，它將被假定為要定義的 SQL 函數(shù)的名稱(chēng)。所有函數(shù)的C名稱(chēng)都必須不同，因此必須為重載的C函數(shù)給出不同的C名稱(chēng)（例如把參數(shù)類(lèi)型作為C名稱(chēng)的一部分）。

在重復(fù)調(diào)用引用同一對(duì)象文件的CREATE FUNCTION時(shí)，對(duì)每個(gè)會(huì)話該文件只會(huì)被載入一次。要卸載并且重新裝載該文件（可能是在開(kāi)發(fā)期間），需要開(kāi)始一個(gè)新會(huì)話。

編寫(xiě)函數(shù)的進(jìn)一步信息可以參考第 37.3 節(jié)。

重載

PostgreSQL允許函數(shù)重載，也就是說(shuō)同一個(gè)名稱(chēng)可以被用于多個(gè)不同的函數(shù)，只要它們具有可區(qū)分的輸入?yún)?shù)類(lèi)型。不管是否使用它，在有些用戶不信任另一些用戶的數(shù)據(jù)庫(kù)中調(diào)用函數(shù)時(shí)，這種兼容性需要安全性的預(yù)防措施，請(qǐng)參考第 10.3 節(jié)。

如果兩個(gè)函數(shù)具有相同的名稱(chēng)和輸入參數(shù)類(lèi)型，它們被認(rèn)為相同（不考慮任何OUT參數(shù)）。因此這些聲明會(huì)沖突：

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, out text) ...

具有不同參數(shù)類(lèi)型列表的函數(shù)在創(chuàng)建時(shí)將不會(huì)被認(rèn)為是沖突的，但是如果默認(rèn)值被提供，在使用時(shí)它們有可能會(huì)沖突。例如，考慮

CREATE FUNCTION foo(int) ...
CREATE FUNCTION foo(int, int default 42) ...

調(diào)用foo(10)將會(huì)失敗，因?yàn)樵谝獩Q定應(yīng)該調(diào)用哪個(gè)函數(shù)時(shí)會(huì)有歧義。

注解

允許把完整的SQL類(lèi)型語(yǔ)法用于聲明一個(gè)函數(shù)的參數(shù)和返回值。不過(guò)，CREATE FUNCTION會(huì)拋棄帶圓括號(hào)的類(lèi)型修飾符（例如類(lèi)型numeric的精度域）。例如CREATE FUNCTION foo (varchar(10)) ...和CREATE FUNCTION foo (varchar) ...完全一樣。

在用CREATE OR REPLACE FUNCTION替換一個(gè)現(xiàn)有函數(shù)時(shí)，對(duì)于更改參數(shù)名是有限制的。不能更改已經(jīng)分配給任何輸入?yún)?shù)的名稱(chēng)（不過(guò)可以給之前沒(méi)有名稱(chēng)的參數(shù)增加名稱(chēng)）。如果有多于一個(gè)輸出參數(shù)，不能更改輸出參數(shù)的名稱(chēng)，因?yàn)榭赡軙?huì)改變描述函數(shù)結(jié)果的匿名組合類(lèi)型的列名。這些限制是為了確保函數(shù)被替換時(shí)，已有的對(duì)該函數(shù)的調(diào)用不會(huì)停止工作。

如果一個(gè)被聲明為STRICT的函數(shù)帶有一個(gè)VARIADIC參數(shù)，會(huì)嚴(yán)格檢查該可變數(shù)組作為一個(gè)整體是否為非空。如果該數(shù)組有空值元素，該函數(shù)仍將被調(diào)用。

示例

這里是一些小例子，它們可以幫你了解函數(shù)創(chuàng)建。更多信息和例子可見(jiàn) 第 37.3 節(jié)。

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

在PL/pgSQL中，使用一個(gè)參數(shù)名稱(chēng)增加一個(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ù)雜的方式（用一個(gè)顯式命名的組合類(lèi)型）來(lái)做同樣的事情：

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è)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);

不過(guò)，TABLE函數(shù)與之前的例子不同，因?yàn)樗鼘?shí)際返回了一個(gè)記錄集合而不只是一個(gè)記錄。

安全地編寫(xiě) SECURITY DEFINER函數(shù)

因?yàn)橐粋€(gè)SECURITY DEFINER函數(shù)會(huì)被以創(chuàng)建它的用戶的特權(quán)來(lái)執(zhí)行，需要小心地確保該函數(shù)不會(huì)被誤用。為了安全，search_path應(yīng)該被設(shè)置為排除任何不可信用戶可寫(xiě)的模式。這可以阻止惡意用戶創(chuàng)建對(duì)象（例如表、函數(shù)以及操作符）來(lái)掩飾該函數(shù)所要用到的對(duì)象。在這方面特別重要的是臨時(shí)表模式，默認(rèn)情況下它會(huì)第一個(gè)被搜索并且通常對(duì)任何用戶都是可寫(xiě)的?？梢酝ㄟ^(guò)強(qiáng)制最后搜索臨時(shí)模式來(lái)得到一種安全的布局。要這樣做，把 pg_temp 寫(xiě)成search_path中的最后一項(xiàng)。這個(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
    -- 設(shè)置一個(gè)安全的 search_path：受信的模式，然后是 'pg_temp'。
    SET search_path = admin, pg_temp;

這個(gè)函數(shù)的目的是為了訪問(wèn)表admin.pwds。但是如果沒(méi)有SET子句或者帶有SET子句卻只提到admin，該函數(shù)會(huì)變成創(chuàng)建一個(gè)名為pwds的臨時(shí)表。

在PostgreSQL 版本 8.3 之前，SET子句不可用，因而較老的函數(shù)可能包含相當(dāng)復(fù)雜的邏輯來(lái)保存、設(shè)置以及恢復(fù)search_path。對(duì)于這種目的，SET子句更容易。

另一點(diǎn)要記住的是默認(rèn)情況下，會(huì)為新創(chuàng)建的函數(shù)給PUBLIC授予執(zhí)行特權(quán)（詳見(jiàn)第 5.7 節(jié)）。你常常會(huì)希望把安全定義器函數(shù)的使用限制在某些用戶中。要這樣做，你必須收回默認(rèn)的PUBLIC特權(quán)，然后選擇性地授予執(zhí)行特權(quán)。為了避免出現(xiàn)新函數(shù)能被所有人訪問(wèn)的時(shí)間窗口，應(yīng)在一個(gè)事務(wù)中創(chuàng)建它并且設(shè)置特權(quán)。例如：

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標(biāo)準(zhǔn)中定義了CREATE FUNCTION命令。PostgreSQL的版本與之類(lèi)似但不完全兼容。屬性是不可移植的，不同的可用語(yǔ)言也是不能移植的。

對(duì)于和一些其他數(shù)據(jù)庫(kù)系統(tǒng)的兼容性，argmode可以被寫(xiě)在argname之前或者之后。但只有第一種方式是兼容標(biāo)準(zhǔn)的。

對(duì)于參數(shù)默認(rèn)值，SQL 標(biāo)準(zhǔn)只指定帶有DEFAULT關(guān)鍵詞的語(yǔ)法。帶有=的語(yǔ)法被用在 T-SQL 和 Firebird 中。


以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)