PostgreSQL 사용자 정의 함수 (User-Defined Function)

원래 PostgreSQL의 저장 프로시저(Stored Procedure)와 사용자 정의 함수(User-Defined Function)에 대한 차이점을 설명하려고 했으나, 먼저 사용자 정의 함수와 저장 프로시저에 대한 설명을 먼저 작성해야겠다고 생각했습니다.

먼저 사용자 정의 함수에 대한 글을 작성해봅니다.

 

사용자 정의 함수란? (User-Defined Function)

사용자 정의 함수는 일부 작업을 수행하도록 지정된 임의의 SQL 문 그룹입니다. 보통 “function”이나 “함수”라고 부르고 때로는 “UDF”라고도 합니다. 일반적으로 여러 SQL들 또는 로직을 정의해 일련의 과정으로 데이터를 처리하기 위해 생성됩니다. 함수 내에서 SELECT, INSERT, UPDATE, DELETE 등의 다양한 쿼리 작업을 수행할 수 있습니다.

PostgreSQL에서 사용자 정의 함수는 0개 이상의 입력 인자를 받고 결과를 반환하는 일련의 작업을 수행합니다. 보통 반환 값이 있는 계산을 처리하기 위해 사용합니다.

사용자 정의 함수 사용 예

• 날짜를 특정 포맷의 문자열로 변환
• 암호화, 복호화와 같은 복잡한 계산을 통해 결과를 내는 계산
• 어떤 문자열을 특정 형식으로 만들어 반환하거나, 어떤 문자열에서 특정 단어 및 문자열 추출

 

사용자 정의 함수의 장점

• 캡슐화와 재사용성: 사용자 정의 함수는 자주 사용되는 SQL 및 연산들을 캡슐화하고 사용성을 높입니다. 이는 쿼리 개발의 생산성을 높이고 유지 보수의 장점이 있습니다.
• 성능 최적화: 사용자 정의 함수는 데이터베이스 서버 내부에서 실행됩니다. 그래서 서버와 서버간의 네트워크 왕복 및 데이터 오버헤드를 줄일 수 있습니다.
• 보안: 적절한 권한 및 권한을 정의하여 중요한 데이터 또는 작업에 대한 액세스를 제어할 수 있습니다. 사용자의 권한을 부여하거나 취소해서 인증된 사용자만 함수를 사용할 수 있도록 할 수 있습니다.
• 이식성: 사용자 정의 함수는 다른 RDB 환경에서도 사용하기 때문에, 서비스의 로직 변경 없이 쉽게 마이그레이션 할 수 있는 것이 장점입니다.

기본 문법

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [.. logic]
      RETURN { variable_name | value }
   END; 
LANGUAGE language_name;

• CREATE [OR REPLACE] FUNCTION: 함수를 생성합니다. [OR REPLACE] 는 기존 함수를 업데이트합니다.
• function_name: 함수의 이름을 지정합니다.
• arguments: 함수의 입력 인수를 지정합니다.
• RETURNS return_datatype: 함수의 반환 데이터 유형을 지정합니다.
• DECLARE: 함수에서 사용할 변수를 선언합니다.
• BEGIN, END: 함수의 로직을 포함합니다.
• LANGUAGE language_name: 함수의 언어를 지정합니다. 아래에서 어떤 언어를 지원하고 어떻게 사용하는지 설명드리겠습니다.

 

예시

아래 예시는 현재 timestamp를 가져오는 함수의 예시입니다.

CREATE FUNCTION getTimestamp() RETURNS timestamp AS $$
BEGIN
RETURN CURRENT_TIMESTAMP;
END; $$
LANGUAGE PLPGSQL;

실행

SELECT getTimestamp()

 

RETURN 키워드의 다양한 예

'RETURNS' 키워드에서 다양한 return 타입을 설정할 수 있습니다.

다음 예시는 각 리턴 타입에 따라 다른 함수의 예시입니다.

-- VARCHAR를 RETURN 타입으로 설정한 풀네임 반환 함수
CREATE FUNCTION get_full_name(first_name VARCHAR, last_name VARCHAR)
  RETURNS VARCHAR AS $$
BEGIN
  RETURN first_name || ' ' || last_name;
END;
$$ LANGUAGE plpgsql;

-- DATE를 RETURN 타입으로 설정한 다음 평일을 계산하는 함수
CREATE FUNCTION get_next_weekday(start_date DATE)
  RETURNS DATE AS $$
DECLARE
  next_date DATE;
BEGIN
  next_date := start_date + INTERVAL '1 day';

  WHILE EXTRACT(ISODOW FROM next_date) IN (6, 7) LOOP
    next_date := next_date + INTERVAL '1 day';
  END LOOP;

  RETURN next_date;
END;
$$ LANGUAGE plpgsql;

-- RETURN 타입을 BOOLEAN으로 설정한 짝수 여부를 반환하는 함수
CREATE FUNCTION is_even_number(number INTEGER)
  RETURNS BOOLEAN AS $$
BEGIN
  RETURN number % 2 = 0;
END;
$$ LANGUAGE plpgsql;

 

 

DECLARE 사용의 예

아래의 SQL은 DECLARE를 사용하여 다양한 타입의 변수를 선언하는 예시입니다.

입력받은 정수 배열들의 평균을 구하는 함수입니다.

함수에서는 변수로 총합, 배열의 수, 평균 값 등을 구하여 변수에 저장하는 모습을 볼 수 있습니다.

CREATE FUNCTION calculate_average(numbers INTEGER[])
  RETURNS NUMERIC AS $$
DECLARE
  sum NUMERIC := 0;
  count INTEGER := 0;
  average NUMERIC;
BEGIN
  FOREACH num IN ARRAY numbers LOOP
    sum := sum + num;
    count := count + 1;
  END LOOP;

  average := sum / count;
  RETURN average;
END;
$$ LANGUAGE plpgsql;

 

 

LANGUAGE 키워드의 다양한 언어 지원

PostgreSQL은 사용자 정의 함수에 다양한 언어를 제공하고 있습니다. 일반적으로 사용되는 언어는 다음과 같습니다.

• PL/pgSQL: PL/pgSQL은 PostgreSQL에서 사용자 정의 함수를 작성하기 위해 특별히 설계되었는데, 이 언어는 SQL과 유사하고, 제어 구조, 변수 및 예외 처리가 있는 함수를 작성하는 데 적합합니다.
• SQL: 표준 SQL 문 및 표현식으로 함수를 작성할 수 있습니다. 간단한 계산 및 데이터 조작에 사용하기 좋습니다.
• PL/Python: Python으로 함수를 작성할 때 이 PL/Python 키워드를 사용합니다. 파이썬의 기능과 파이썬 라이브러리를 사용할 수 있습니다.
• PL/v8(JavaScript): PL/v8을 사용하면 JavaScript를 사용하여 함수를 작성할 수 있습니다. PostgreSQL 환경 내에서 JavaScript의 라이브러리를 활용할 수 있습니다.
• 기타 언어: 그 외 PostgreSQL에서 PL/Perl, PL/Tcl, PL/Java 등과 같은 추가 언어도 지원합니다.

LANGUAGE 키워드 예시

PL/pgSQL 예시는 위에 있으므로, 다른 언어의 예시를 보여드리겠습니다.

SQL, PL/Python, PL/v8(JavaScript)의 예시를 준비해봤습니다.

 

SQL

CREATE FUNCTION get_product_price(product_id INTEGER)
  RETURNS NUMERIC AS $$
SELECT price FROM products WHERE id = product_id;
$$ LANGUAGE sql;

 

파이썬

CREATE FUNCTION calculate_factorial(n INTEGER)
  RETURNS INTEGER AS $$
DECLARE
  result INTEGER;
BEGIN
  EXECUTE 'SELECT factorial(' || n || ')'
  INTO result;

  RETURN result;
END;
$$ LANGUAGE plpythonu;

 

자바스크립트

자바스크립트 예시를 사용하려면 먼저 plv8 extension이 있어야 합니다.

CREATE EXTENSION IF NOT EXISTS plv8; 를 먼저 사용하여 plv8이 없으면 설치하여 자바스크립트로 사용자 정의 함수를 만들어 보도록 하겠습니다.

CREATE EXTENSION IF NOT EXISTS plv8;

CREATE OR REPLACE FUNCTION calculate_total_amount(items JSONB)
  RETURNS NUMERIC AS $$
var totalAmount = 0;

for (var i = 0; i < items.length; i++) {
  var item = items[i];

  // Perform complex calculation based on item properties
  var quantity = item.quantity;
  var price = item.price;
  var discount = item.discount || 0;

  var itemTotal = quantity * price * (1 - discount);
  totalAmount += itemTotal;
}

return totalAmount;
$$ LANGUAGE plv8;

SELECT calculate_total_amount('[{"quantity": 2, "price": 10, "discount": 0.1}, {"quantity": 3, "price": 15}]');
-- Returns 43.5

 

요약하자면 PostgreSQL에서 사용자 정의 함수는 복잡한 로직을 캡슐화 하고 재사용성을 높이는데 좋은 장점이 있습니다. 그리고 함수 접근 권한을 사용자마다 다르게 줄 수 있는 장점도 있습니다.

자주 사용하는 쿼리에 변수 선언과 여러 SQL문을 일괄 처리하는데도 좋습니다.

마지막으로 다양한 언어를 지원하여 익숙한 언어를 사용할 수 있다는 장점도 가지고 있습니다.