在適用於 PostgreSQL 的 Azure 資料庫中建立並使用函式

• 5 分鐘

我們已經了解 PostgreSQL 支援不同的語言。 函式可以分類為四種不同的類型：

• 以 SQL 撰寫的函式。
• 程序性語言函式，以支援的程式語言撰寫，例如 PL.pgSQL。
• 內部函式。
• C 語言函式。

此外，函式的用途也可以分類為 volatile、immutable 或 stable。

volatile (預設值) 函式可以修改資料庫，且每次使用相同輸入參數不一定會傳回相同結果。 因此每次呼叫此函式時，都必須重新評估。

stable 函式無法修改資料庫，且如果傳遞相同引數，並在相同陳述式內執行，則會傳回相同結果。 如果多次呼叫此函式，查詢最佳化工具就可以使用上次呼叫的結果。

immutable 函式無法修改資料庫，如果傳遞相同引數，無論呼叫該函式的查詢為何，都會傳回相同結果。

函式的穩定性對查詢最佳化工具的處理效率有很大的差異。

建立函式

函式會傳回單一值，且可以在 SELECT 陳述式內使用。

建立函式的語法為：

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

如同預存程序，$$ 符號用於啟動和結束字串。

函式採用下列參數：

• name - 選擇性地包含結構描述名稱。
• argmode - 引數的模式。 可以是 IN、OUT、INOUT 或 VARIADIC。 預設值為 IN。 VARDIAC 是相同類型的未定義輸入引數數目，且「後面必須接著 OUT 引數」。 OUT 和 INOUT 引數不能與 RETURNS TABLE 標記法一起使用。
• argname - 引數名稱。
• argtype - 引數資料類型。 可以是基底、複合或網域類型，或參考資料表資料行類型。 資料行類型可撰寫為 table_name.column_name%TYPE。 此資料類型有助於讓函式與資料表定義變更無關。
• t_expr - 如果未指定參數時，則會使用此 (相同類型的) 預設值。 只有 IN 和 INOUT 參數有預設值。 在具有預設值的參數之後的輸入參數也必須具有預設值。
• rettype - 傳回資料類型，可以是基底、複合或網域類型，或參考資料表資料行類型。 如果函式未傳回值，請將傳回類型指定為 void。 當有 OUT 或 INOUT 參數時，可以省略 RETURNS 子句。 如果有的話，它必須同意輸出參數所隱含的結果類型：如果有多個輸出參數，則為 RECORD，或與單一輸出參數相同的類型。 SETOF 修飾元表示函式會傳回一組項目，而不是單一項目。 資料行類型是藉由寫入 table_name 來參考。
• column_name - RETURNS TABLE 語法中的輸出資料行名稱。 此參數宣告具名 OUT 參數，但 RETURNS TABLE 也表示 RETURNS SETOF。
• column_type - RETURNS TABLE 語法中輸出資料行的資料類型。
• lang_name - 用來寫入程序的語言。 如果已指定 sql_body，則預設值為 sql。 可以是 sql、c、internal 或使用者定義程序性語言的名稱，例如 plpgsql。

使用關鍵字 IMMUTABLE、STABLE 或 VOLATILE 作為關於函式之查詢最佳化工具的提示。 VOLATILE 為預設值。

呼叫函式

可以透過將任何相關參數傳遞給查詢來使用此函式。 例如：

SELECT myfunction(3), CatID, CatName
    FROM myCats

內建函式

PostgreSQL 包含許多可在查詢中使用的內建函式。 這些函式涵蓋進行比較、彙總資料、數學函式等。如需 PostgreSQL 函式的完整清單，請參閱線上文件。

內建字串函式的範例為 substring。

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

此函式採用三個輸入參數：

• 字串 (型別文字)
• FROM 開始 (型別整數)
• FOR 計數 (整數類型)

子字串會傳回輸入文字的一部分，從 start 字元開始，並在 count 字元之後停止。 例如：

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

此函式與 substr 相同：

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph