CREATE FUNCTION

PDFRSS

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

如需詳細資訊和範例，請參閱 Amazon Redshift 中的使用者定義函數。

所需權限

您必須具有下列其中一種執行 CREATE OR REPLACE FUNCTION 的許可：

• 對於 CREATE FUNCTION：

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

  • 具有 CREATE [ OR REPLACE ] FUNCTION 權限的使用者可以使用信任語言建立函數。

• 對於 REPLACE FUNCTION：

  • 超級使用者

  • 具有 CREATE [ OR REPLACE ] FUNCTION 權限的使用者

  • 函數擁有者

語法

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 名稱不會與任何現有或未來的 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

函數所傳回值的資料類型。RETURNS 資料類型可以是任何標準 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 程式碼格式指南》https://www.python.org/dev/peps/pep-0008/#indentation所述。如需詳細資訊，請參閱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)。當您執行 CREATE FUNCTION 命令時，巢狀函數必須存在。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 撤銷函數的執行許可。然後將許可授予特定個人或群組。

下列範例會撤銷 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;

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

如需詳細資訊，請參閱 GRANT 和 REVOKE。

範例

純量 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;