[PostgreSQL] 공통 코드 테이블로 REPLACE 지옥 탈출하기

최근 고도화 프로젝트에서 아래처럼 REPLACE문을 거의 1n중으로 감싼 하드코딩 쿼리를 발견했다.

SELECT REPLACE(REPLACE(REPLACE(your_column_name, 'APPLE', '애플'), 'BANANA', '바나나'), 'MANGO', '망고') AS FRUITS
FROM comm_cd

 

이 쿼리는 한 컬럼에 `APPLE,BANANA,MANGO` 이렇게 저장된 문자열의 값을 '애플,바나나,망고' 로 치환시켜주는 쿼리다.

 

이 프로젝트는 기간이 굉장히 타이트해서 계속 야근하느라 빨리 구현해야 됐었다.

이 쿼리 또한 AS-IS 프로젝트의 쿼리 로직을 그대로 사용해도 되는거였으나.. 너무 불편하고 비효율적이었다.

그래서 공통 코드 테이블 기반으로 치환 함수를 만들어 재사용 가능한 구조로 바꿨고, 간단한 예시로 이 과정을 적어보고자 한다.

---

문제점

• REPLACE()를 계속 중첩해야 해서 쿼리가 가독성/유지보수성 최악
• 남이 보기에 이해하기도 힘들뿐더러, 대체 닫는 괄호가 어디를 닫는건지도 파악하기 힘들다.
• 나중에 과일 종류가 추가된다면 개수마다 REPLACE문을 추가해줘야 한다.
• 비슷한 기능을 할 때마다 REPLACE문 안에 코드, 치환값을 직접 적어줬어야 함(제일 귀찮음)

 

간단한 예시로 이 값들을 공통 테이블에 넣어 함수로 치환해 보자.

 

전제 테이블 구조

COMM_CD (공통 코드 테이블)

컬럼명	설명
code_id	코드유형
code	코드
code_nm	코드명

 

 

예시 데이터

code_id	code	code_nm
FRUIT	APPLE	사과
FRUIT	BANANA	바나나
FRUIT	MANGO	망고

 

 

TB_FRUITS (조회 대상 테이블)

컬럼명	설명
seq	조회테이블 seq
fruit_cd	과일

 

공통코드 기반 사용자 함수 만들기

중복된 REPLACE() 대신, 공통 코드 테이블을 참조해 문자열을 자동 치환해주는 함수를 작성했다.

 

아래에서 하나씩 이해해 보자. 

CREATE OR REPLACE FUNCTION fn_get_comm_code_nm(p_code text, p_code_id text)
 RETURNS text
 LANGUAGE plpgsql
AS $$
DECLARE
    v_result text := '';
    v_delimiter text := ',';     -- 구분자 (콤마)
    v_split_cnt int := 0;        -- 일치 항목 수 저장
BEGIN
    -- 1. 입력값이 비어 있으면 빈 문자열 반환
    IF p_code IS NULL OR TRIM(p_code) = '' THEN
        RETURN '';
    ELSE
        -- 2. 조건 일치하는 공통코드 항목 수 조회
        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));

        -- 3. 일치하는 항목이 있을 경우 → 배열로 코드명 조회 및 문자열 변환
        IF v_split_cnt > 0 THEN
            SELECT array_to_string(
                       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))
                       ),
                       ', '
                   )
            INTO v_result;
        END IF;
    END IF;

    -- 4. 최종 결과 반환
    RETURN v_result;
END;
$$;

 

 

과정

1. 입력값 검증

IF p_code IS NULL OR TRIM(p_code) = ''
    THEN
        RETURN '';

• p_code 값이 NULL이거나 공백 문자열인 경우 빈 문자열을 반환하고 함수 종료

 

2. 공통코드 일치 항목 수 확인

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));

• p_code와 p_code_id를 콤마(,)로 구분된 문자열로 받았기 때문에 string_to_array()로 분리 후 ANY 조건으로 비교
• 예를 들어 'APPLE,BANANA' → {APPLE, BANANA} 로 배열화되어 비교됨
• 조건에 맞는 code_nm의 개수를 v_split_cnt 변수에 저장

 

 

3. 일치하는 항목이 있을 경우 → 코드명 배열화

SELECT array_to_string(
           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))
           ),
           ', '
       )
INTO v_result;

• 일치하는 코드가 하나라도 있으면(v_split_cnt > 0) 실행됨
• SELECT 서브쿼리로 code_nm들을 배열(ARRAY)로 만든 뒤, array_to_string으로 콤마(,) 기준 문자열로 변환
• ex)
  
  • 입력값: 'APPLE,BANANA,MANGO'
  • 변환 결과: {사과, 바나나, 망고} → '사과, 바나나, 망고'

 

4. 최종 반환

RETURN v_result;

• 최종 결과 문자열을 반환
• 위 과정을 통해, 원래는 REPLACE(REPLACE(...)).. 구조로 일일이 바꿨던 것을 단 하나의 함수 호출로 대체 가능

 

결과

SELECT fn_get_comm_code_nm('APPLE,BANANA,MANGO', 'FRUIT');
-- 결과: '사과, 바나나, 망고'

 

참고로 공통 그룹코드를 조회할 때도 `WHERE CODE_ID = ANY(string_to_array(p_code_id, v_delimiter))`

파라미터를 전부 조회하기 때문에 FRUIT외에도 다른 그룹코드의 코드도 조회할 수 있다.

 

참고로 p_code_id 파라미터도 string_to_array()로 쪼개서 ANY 조건으로 비교하기 때문에, 단일 그룹코드뿐만 아니라 여러 개의 그룹코드도 한 번에 넣을 수 있다.

 

즉, FRUIT만 조회하는 게 아니라 FOOD, FLOWERS 같은 다른 그룹코드도 함께 조회할 수 있다.

예를 들어

SELECT fn_get_comm_code_nm(code, 'FRUIT,FOOD,FLOWERS')
FROM TB_FRUITS;
-- code = 'APPLE,BEEF,TULIP'일 경우 → '사과, 소고기, 튤립'

code 값이 APPLE, BEEF, TULIP이라면
COMM_CD 테이블에서 각각의 그룹코드에 해당하는 코드명을 찾아서
→ '사과, 소고기, 튤립' 이런 식으로 바꿔줄 수 있다.