CREATE FUNCTION - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

CREATE FUNCTION

使用 SQL SELECT 子句或 Python 程式,建立新的純量使用者定義函數 (UDF)。

必要的權限

以下是創建函數所需的權限:

  • 對於 CREATE 函數:

    • 超級用户可以使用受信任的語言和不受信任的語言來創建函數。

    • 具有 CREATE [或替換] 函數權限的用户可以使用受信任的語言創建函數。

  • 對於替換函數:

    • 超級使用者

    • 擁有 CREATE [或替代] 函數權限的使用者

    • 函數擁有者

Syntax (語法)

CREATE [ OR REPLACE ] FUNCTION f_function_name ( { [py_arg_name py_arg_data_type | sql_arg_data_type } [ , ... ] ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ { python_program | SELECT_clause } $$ LANGUAGE { plpythonu | sql }

參數

OR REPLACE

指定已有相同名稱和輸入引數資料類型 (或簽章) 的函數存在時,取代現有函數。您可以將函數取代為定義一組相同資料類型的新函數。您必須是超級使用者才能取代函數。

如果您定義的函數與現有函數同名,但簽章不同,則會建立新函數。換言之,函數名稱將會過載。如需詳細資訊,請參閱 多載函數名稱

f_function_name

函數的名稱。如果您指定結構描述名稱 (例如 myschema.myfunction),則會使用指定的結構描述建立函數。否則,函數會在目前結構描述中建立。如需有效名稱的詳細資訊,請參閱 名稱與識別符

建議您在所有 UDF 名稱前綴f_。Amazon Redshift 會保留f_前綴作為 UDF 名稱,因此通過使用f_前綴,您可以確保 UDF 名稱不會與任何現有或 future 的 Amazon Redshift 內建 SQL 函數名稱發生衝突。如需詳細資訊,請參閱 命名 UDF

若輸入引數的資料類型不同,則您可以定義多個擁有相同函數名稱的函數。換言之,函數名稱將會過載。如需詳細資訊,請參閱 多載函數名稱

py_arg_name py_arg_data_type | sql_arg_data type

若是 Python UDF,此為輸入引數名稱和資料類型的清單。若是 SQL UDF,此為資料類型的清單,不包含引數名稱。在 Python UDF 中,使用引數名稱來參考引數。在 SQL UDF 中,根據引數清單中的引數順序,使用 $1、$2 等來參考引數。

若是 SQL UDF,輸入和傳回資料類型可以是任何標準 Amazon Redshift 資料類型。使用 Python UDF 時,輸入及傳回的資料類型可為 SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE 或 TIMESTAMP。此外,Python 使用者定義函數 (UDF) 支援 ANYELEMENT 資料類型。這會根據執行時提供的對應引數資料類型,自動轉換為標準資料類型。如果有多個引數使用 ANYELEMENT,根據清單中的第一個 ANYELEMENT 引數而定,這些引數在執行時都會解析為相同資料類型。如需詳細資訊,請參閱 Python UDF 資料類型資料類型

您最多可以指定 32 個引數。

RETURNS data_type

函數所傳回值的資料類型。退貨資料類型可以是任何標準 Amazon Redshift 資料類型。此外,Python UDF 還可以使用資料類型 ANYELEMENT,該資料類型會根據執行時提供的引數自動轉換為標準資料類型。如果您指定 ANYELEMENT 做為傳回資料類型,則至少有一個引數必須使用 ANYELEMENT。實際的傳回資料類型將與呼叫函數時提供給 ANYELEMENT 引數的資料類型相同。如需詳細資訊,請參閱 Python UDF 資料類型

VOLATILE | STABLE | IMMUTABLE

通知查詢最佳化工具有關函數的波動情形。

如果您將函數標示為最嚴格的有效波動類別,將會得到最理想的最佳化結果。不過,如果類別太嚴格,則最佳化工具可能會錯誤地略過某些呼叫,導致產生不正確的結果集。從最低嚴格程度開始,依嚴格程度排列的波動類別如下所示:

  • VOLATILE

  • STABLE

  • IMMUTABLE

VOLATILE

假設引數相同,即使是針對單一陳述式中的資料列,函數也可能在後續呼叫中傳回不同的結果。查詢最佳化工具無法對波動函數的行為做出任何假設,因此,使用波動函數的查詢必須針對每個輸入資料列重新評估函數。

STABLE

假設引數相同,函數一定會針對單一陳述式內處理的所有資料列傳回相同結果。在不同陳述式中呼叫函數時,函數可能傳回不同結果。此類別可讓最佳化工具將單一陳述式中的多次函數呼叫最佳化,成為陳述式的單一呼叫。

IMMUTABLE

假設引數相同,函數一律傳回相同結果,而且永遠不變。當查詢呼叫具有常數引數的 IMMUTABLE 函數時,最佳化工具會預先評估函數。

AS $$ statement $$

包圍要運行之陳述式的結構描述。常值關鍵字 AS $$$$ 是必要的。

Amazon Redshift 會要求您使用稱為 $ 符號引用的格式包圍函數中的陳述式。包圍範圍當中的任何內容都會原封不動傳遞。您不需要逸出任何特殊字元,因為字串的內容是逐字撰寫。

使用 $ 符號引用時,您會使用一組 $$ 符號配對表示要執行之陳述式的開頭和結尾,如以下範例所示。

$$ my statement $$

您也可以選擇在每組配對的兩個 $ 符號之間指定字串來協助識別陳述式。您在包圍配對的開頭和結尾中使用的字串必須相同。此字串區分大小寫,且遵循與未加引號的識別符相同的限制條件,不過後者不可包含 $ 符號。下列範例使用字串 test

$test$ my statement $test$

如需 $ 符號引用的詳細資訊,請參閱 PostgreSQL 文件中的辭典結構下的「$ 符號引用的字串常數」。

python_program

傳回值的有效可執行 Python 程式。隨函數傳入的陳述式必須符合縮排要求,如 Python 網站上的 Python 程式碼格式指南所述。如需詳細資訊,請參閱 UDF 的 Python 語言支援

SQL_clause

SQL SELECT 子句。

SELECT 子句不可包含下列任何類型的子句:

  • FROM

  • INTO

  • WHERE

  • GROUP BY

  • ORDER BY

  • LIMIT

LANGUAGE { plpythonu | sql }

若是 Python,指定 plpythonu。若是 SQL,指定 sql。您必須具有 SQL 或 plpythonu 的語言使用權許可。如需詳細資訊,請參閱 UDF 安全與權限

使用須知

巢狀函數

您可以從 SQL UDF 內呼叫另一個 SQL 使用者定義的函數 (UDF)。運行「創建函數」命令時,嵌套函數必須存在。Amazon Redshift 不會追蹤 UDF 的相依性,因此,若您捨棄巢狀函數,Amazon Redshift 不會傳回錯誤。不過,若巢狀函數不存在,UDF 將會失敗。例如,以下函數會在 SELECT 子句中呼叫 f_sql_greater 函數。

create function f_sql_commission (float, float ) returns float stable as $$ select f_sql_greater ($1, $2) $$ language sql;

UDF 安全與權限

若要建立 UDF,您必須具有 SQL 或 plpythonu (Python) 的語言使用權許可。根據預設,USAGE ON LANGUAGE SQL 會授予 PUBLIC。不過,您必須將 USAGE ON LANGUAGE PLPYTHONU 明確授予特定使用者或群組。

若要撤銷 SQL 的使用權,請先從 PUBLIC 撤銷使用權。然後僅將 SQL 使用權授予獲得許可建立 SQL UDF 的特定使用者或群組。下列範例會撤銷 PUBLIC 的 SQL 使用權,然後將使用權授予使用者群組 udf_devs

revoke usage on language sql from PUBLIC; grant usage on language sql to group udf_devs;

若要運行 UDF,您必須具有每個函數的執行權限。根據預設,新 UDF 的執行許可會授予 PUBLIC。若要限制使用權,請從 PUBLIC 撤銷函數的執行權限。然後將許可授予特定個人或群組。

下列範例撤銷函數的執行權限f_py_greater然後將使用權授予使用者組udf_devs

revoke execute on function f_py_greater(a float, b float) from PUBLIC; grant execute on function f_py_greater(a float, b float) to group udf_devs;

根據預設,超級使用者具備所有權限。

如需詳細資訊,請參閱 GRANTREVOKE

範例

純量 Python UDF 範例

下列範例會建立比較兩個整數並傳回較大值的 Python UDF。

create function f_py_greater (a float, b float) returns float stable as $$ if a > b: return a return b $$ language plpythonu;

下列範例會查詢 SALES 資料表,並呼叫新的 f_py_greater 函數來查詢 COMMISSION 或 20% 的 PRICEPAID,以較大者為準。

select f_py_greater (commission, pricepaid*0.20) from sales;

純量 SQL UDF 範例

下列範例會建立一個函數,比較兩個數字並傳回較大的值。

create function f_sql_greater (float, float) returns float stable as $$ select case when $1 > $2 then $1 else $2 end $$ language sql;

下列查詢會呼叫新的 f_sql_greater 函數來查詢 SALES 資料表,並傳回 COMMISSION 或 20% 的 PRICEPAID,以較大者為準。

select f_sql_greater (commission, pricepaid*0.20) from sales;