CREATE FUNCTION - Amazon Redshift
필수 권한구문파라미터사용 노트예시

CREATE FUNCTION

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는 UDF 이름에 대해 f_ 접두사를 예약하므로, f_ 접두사를 사용하면 UDF 이름이 기존 또는 향후의 Amazon Redshift 내장 SQL 함수 이름과 충돌하지 않을 것입니다. 자세한 내용은 UDF 이름 충돌 방지 섹션을 참조하세요.

입력 인수에 대한 데이터 형식이 서로 다른 경우 함수 이름이 동일한 함수를 2개 이상 정의할 수 있습니다. 즉, 함수 이름이 오버로드됩니다. 자세한 내용은 함수 이름 오버로드 섹션을 참조하세요.

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 코드 스타일 가이드에 지정된 들여쓰기 요구 사항을 준수해야 합니다. 자세한 내용은 UDF에 대한 Python 언어 지원 섹션을 참조하세요.

SQL 절

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 UDF 생성이 허용된 사용자 또는 그룹에게만 SQL에 대한 사용 권한을 허용합니다. 다음은 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 테이블을 쿼리하고 COMMISSION 또는 PRICEPAID의 20% 중 큰 값을 반환하는 새 f_py_greater 함수를 호출합니다.

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

스칼라 SQL UDF 예시

다음은 2개의 수를 비교하여 더 큰 값을 반환하는 함수의 생성 예입니다.

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 또는 PRICEPAID의 20% 중에서 더 큰 값을 반환하는 쿼리입니다.

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