최근 고도화 프로젝트에서 아래처럼 REPLACE문을 거의 1n중으로 감싼 하드코딩 쿼리를 발견했다.
이 프로젝트는 기간이 굉장히 타이트해서 계속 야근하느라 빨리 구현해야 됐었다.
이 쿼리 또한 AS-IS 프로젝트의 쿼리 로직을 그대로 사용해도 되는거였으나 차마 그냥 쓰기가 싫어서 함수로 만들었다.
공통 테이블 기반의 함수들은 만들어두면 다른 개발자들도 사용할 수 있어서 유용하다.
간단한 예시로 포스팅하고자 한다.
레쓰고
SELECT REPLACE(REPLACE(REPLACE(your_column_name, 'APPLE', '애플'), 'BANANA', '바나나'), 'MANGO', '망고') AS FRUITS
FROM comm_cd이 쿼리는 `APPLE,BANANA,MANGO` 이렇게 저장된 문자열의 값을 '애플,바나나,망고' 로 치환시켜주는 쿼리다.
이런 쿼리는 남이 보기에 이해하기도 힘들뿐더러, 대체 닫는 괄호가 어디를 닫는건지도 파악하기 힘들다.
또 나중에 과일 종류가 추가된다면 개수마다 REPLACE문을 추가해줘야 한다.
정말 생산성이 떨어진다..
간단한 예시로 이 값들을 공통 테이블에 넣어 함수로 치환해 보자.
TABLE
공통 테이블 `COMM_CD`
데이터
조회 테이블 `TB_FRUITS`
데이터
공통 테이블과 조회 테이블이 이렇게 있다고 가정했을 때 함수는 이렇게 만들 수 있다.
아래에서 하나씩 이해해 보자.
CREATE OR REPLACE FUNCTION fn_get_comm_code_nm(p_code text, p_code_id text)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_result text := ''; -- 결과값
v_delimiter text := ','; -- 구분자
v_split_cnt int := 0; -- 낱개 갯수
BEGIN
-- 하나의 컬럼에 구분자(',')로 묶여져 있는 코드를 공통코드에서 찾아서 코드명으로 변경
-- ex) 'APPLE,BANANA' → '사과, 망고'
-- p_code : 입력텍스트
-- p_code_id : 그룹코드
IF p_code IS NULL OR TRIM(p_code) = ''
THEN
RETURN '';
ELSE
SELECT COUNT(CODE_NM) INTO v_split_cnt
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter));
IF v_split_cnt > 0
THEN
SELECT replace(
array_to_string(
array_agg(
array(
SELECT CODE_NM
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter))
)
), v_delimiter
), v_delimiter, ', '
) INTO v_result;
END IF;
END IF;
RETURN v_result;
END;
$function$
과정
IF p_code IS NULL OR TRIM(p_code) = ''
THEN
RETURN '';
2. 공백이 아닐 경우, 공통 코드 테이블(COMM_CD)에서 주어진 `p_code`와 `p_code_id`를 사용하여 코드명을 검색한다.
3. `COUNT(CODE_NM)`를 통해 코드의 수를 확인하고 그 값을 `v_split_cnt` 변수에 저장한다.
SELECT COUNT(CODE_NM) INTO v_split_cnt
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter));
4. `v_split_cnt`가 1 이상이면 `p_code`로 들어온 'APPLE,BANANA,MANGO'의 값이 `code`에 있고, `p_code_id`로 들어온 'FRIUT'인 코드가 공통 테이블에 있는지 조회한다.
SELECT CODE_NM
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter))| 사과 |
| 바나나 |
| 망고 |
5.`array`함수를 사용하여 데이터를 배열 형태로 그룹화한다.
array(
SELECT CODE_NM
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter))
){사과, 바나나, 망고}
6. `array_agg`함수를 사용하여 코드명들을 하나의 문자열로 결합하고,`v_delimiter` 즉 ',' 로 구분된 `APPLE,BANANA,MANGO`를 배열로 만든 다음 `array_to_string`함수를 사용해 문자열로 바꾼다.
SELECT replace(
array_to_string(
array_agg(
array(
SELECT CODE_NM
FROM COMM_CD
WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))
AND CODE = ANY(string_to_array(p_code, v_delimiter))
)
), v_delimiter
), v_delimiter, ', '
) INTO v_result;
결과
SELECT fn_get_comm_code_nm('FRUIT',fruit_nm)
FROM TB_FRUITS;-> '사과, 바나나, 망고'
참고로 공통 그룹코드를 조회할 때도 `WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))`
파라미터를 전부 조회하기 때문에 FRUIT외에도 다른 그룹코드의 코드도 조회할 수 있다.
ex)
SELECT fn_get_comm_code_nm('FRUIT,FOOD,FLOWERS',code)
FROM TB_FRUITS;