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

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

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

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

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

如果傳遞相同的自變數並在相同的語句內執行， 穩定 函式就無法修改資料庫並傳回相同的結果。 如果多次呼叫此函式，查詢優化器就可以使用上次呼叫的結果。

不論呼叫資料庫的查詢為何， 不可變 的函式都無法修改資料庫，如果傳遞相同的自變數，就會傳回相同的結果。

函式的波動性對查詢優化器處理它的效率有很大的差異。

創建函式

函式會傳回單一值，而且可以在 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 (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

此函式接受三個輸入參數：

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

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

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