[PostgreSQL] 랜덤쿠폰번호 생성 및 중복체크 Function 만들기

[PostgreSQL] 랜덤쿠폰번호 생성 및 중복체크 Function 만들기

 

개발 중인 프로젝트에서 쿠폰 시스템을 도입하게 되어 기존에 다른 개발자 분이 만들어 둔 sql Function을 plpgsql로 수정하여 보완하였다.

 

쿠폰 생성 조건 

1. 쿠폰번호는 16자리

임시 테이블의 칼럼을 varchar(16)으로 생성하였다. 임시 테이블은 세션이 종료되면 자동으로 drop 되기 때문에 function을 이용할 때 사용하기 좋다. 자릿수는 입맛에 따라 바꿀 수 있다.

 

2. 영문과 숫자를 이용한 쿠폰번호 생성

이 부분에서 조금 헤맸다.

기존에 generate_series() 함수를 이용해 필요한 쿠폰 갯수만큼의 random() 숫자를 만들어 사용하였는데..

영문자를 넣기 위해서 다양한 방법을 찾던중에 난수에 md5() 함수로 암호화 하는 신박한 내용을 벤치마킹했다.

영문자는 upper() 함수로 대문자로 만들어 유저가 쿠폰번호를 입력할 때도 자동으로 대문자로 치환되도록 했다.

 

3. 맨앞 4자리는 조직 코드를 이용해 카테고리화

커스터마이징 할 때는 concat() 함수를 이용해서 function 의 return 타입으로 꼭 캐스팅을 해주어야 한다.

 

4. 기존에 쿠폰 테이블에 등록된 번호인지 중복 체크하는 로직이 필요

기존 Function은 아예 쿠폰발행갯수를 2배로 난수를 만들어 최종 return select 쿼리에 limit로 개수를 뽑아내는 방식을 이용하였는데 쿠폰번호가 쌓이게 되면 중복을 피할 수 없는 구조였다. 

이에 loop 문을 이용해 검증 후 임시테이블에 담아둘 수 있도록 했다. 

 

기존 Function

CREATE OR REPLACE FUNCTION ming.fn_coupon_no_generator(as_cnt integer)
 RETURNS TABLE(coupon_no text)
 LANGUAGE sql
AS $function$
		select x1.coupon_no 
		  from (
				select trim(to_char(random() * 10000000000000000, '0000000000000000')) as coupon_no
				  from generate_series(1, as_cnt * 2) 
				except
				select coupon_no 	as coupon_no
				  from ming.coupon_table 
				 where coupon_no is not null   
		       ) x1
		limit as_cnt  
$function$
;

 

최종 쿠폰생성 Function

CREATE OR REPLACE FUNCTION MING.FN_COUPON_NO_GENERATOR(P_ORGANIZATION_CODE CHARACTER VARYING, P_CNT INTEGER)
 RETURNS TABLE(COUPON_NO CHARACTER VARYING)
 LANGUAGE PLPGSQL
AS $FUNCTION$
/* 변수 정의 */
DECLARE
	V_ORGANIZATION_CODE VARCHAR(4) := COALESCE(P_ORGANIZATION_CODE, '5555'); --조직코드 기본값
	V_RANDOM_NO VARCHAR(12) := ''; --쿠폰번호의 랜덤 12자리
	V_COUPON_CNT INTEGER := 0; --만들어진 쿠폰 갯수
BEGIN
	/* 임시 테이블 생성 */
	DROP TABLE IF EXISTS TEMP_COUPON_TABLE;
	CREATE TEMP TABLE TEMP_COUPON_TABLE(
	    COUPON_NO VARCHAR(16)
	);

	/* 랜덤 번호 생성 후 INSERT */
	LOOP 
		INSERT INTO TEMP_COUPON_TABLE 
		SELECT X1.COUPON_NO
		  FROM  (
			SELECT CONCAT(V_ORGANIZATION_CODE, UPPER(SUBSTRING(MD5(RANDOM()::VARCHAR),1,12))) AS COUPON_NO
			EXCEPT
			SELECT T.COUPON_NO 	AS COUPON_NO
			  FROM MING.COUPON_TABLE T
			 WHERE T.COUPON_NO IS NOT NULL   
			   AND T.COUPON_NO != ''		
			) X1;
		V_COUPON_CNT = V_COUPON_CNT + 1;  
		EXIT WHEN V_COUPON_CNT = P_CNT; --최종 카운트에 도달하면 반복문 종료
	END LOOP;
	
	RETURN QUERY SELECT X.COUPON_NO FROM TEMP_COUPON_TABLE X  LIMIT P_CNT;  
END;
$FUNCTION$
;

 

Alias를 잘 먹여야 한다.. function 유효성검사가 너무 예민하다..