5단계 카테고리 가상 데이타 만들기 - code 2

 

-  GPT의 도움을 받았습니다

-  카테고리는 code1 ~ code5 까지 있습니다.

-   code1 필드값은 'cate' 로 통일합니다

-   code2에 들어갈 데이터를 생성합니다

 

 

 

●   테이블 구조 (  POSTGRESQL  )

codeseq - INTEGER
code1 - VARCHAR(50)
code2 - VARCHAR(50)
code3 - VARCHAR(50)
code4 - VARCHAR(50)
code5 - VARCHAR(50)
codename - VARCHAR(50)
regdate - TIMESTAMP WITHOUT TIME ZONE
enableyn - VARCHAR(1)
voteyn - VARCHAR(1)
comment - VARCHAR(100)

 

 

●   결과값 먼저 확인하기

-- 쿼리 결과 미리 보기
WITH categories AS (
  SELECT unnest(ARRAY['반려동물', '야생동물', '수중생물', '조류', '파충류']) AS category,
         generate_series(1, 10) AS code2
)
SELECT 'cate' AS code1, 
       LPAD(code2::text, 2, '0') AS code2, 
       NULL AS code3, 
       category AS codename, 
       NOW() AS regdate, 
       'Y' AS enableyn, 
       'N' AS voteyn,
       category AS comment
FROM categories;

 

 

 

●  DB에 생성

-- 쿼리 결과 DB에 생성
WITH categories AS (
  SELECT unnest(ARRAY['반려동물', '야생동물', '수중생물', '조류', '파충류') AS category,
         generate_series(1, 10) AS code2
)
INSERT INTO t_code (code1, code2, code3, codename, regdate, enableyn, voteyn, comment)
SELECT 'cate', 
       LPAD(code2::text, 2, '0'), 
       NULL, 
       category, 
       NOW(), 
       'Y', 
       'N',
       category
FROM categories;

 

 

 

●  하위 카테고리 갯수 불러오기

CREATE OR REPLACE FUNCTION get_subcategory_count(
    p_code_1 VARCHAR(5),
    p_code_2 VARCHAR(5) DEFAULT NULL,
    p_code_3 VARCHAR(5) DEFAULT NULL,
    p_code_4 VARCHAR(5) DEFAULT NULL
)
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER;
    v_level INTEGER;
BEGIN
    -- 입력된 파라미터에 따라 레벨 결정
    IF p_code_4 IS NOT NULL AND p_code_4 != '' THEN
        v_level := 4;
    ELSIF p_code_3 IS NOT NULL AND p_code_3 != '' THEN
        v_level := 3;
    ELSIF p_code_2 IS NOT NULL AND p_code_2 != '' THEN
        v_level := 2;
    ELSE
        v_level := 1;
    END IF;

    -- 레벨에 따라 적절한 쿼리를 실행
    IF v_level = 1 THEN
        -- code1이 주어졌을 때 code2의 개수를 계산
        SELECT COUNT(DISTINCT code2)
        INTO v_count
        FROM t_code
        WHERE code1 = p_code_1 AND code2 IS NOT NULL AND code2 != '';
    ELSIF v_level = 2 THEN
        -- code2가 주어졌을 때 code3의 개수를 계산
        SELECT COUNT(DISTINCT code3)
        INTO v_count
        FROM t_code
        WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 IS NOT NULL AND code3 != '';
    ELSIF v_level = 3 THEN
        -- code3이 주어졌을 때 code4의 개수를 계산
        SELECT COUNT(DISTINCT code4)
        INTO v_count
        FROM t_code
        WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 = p_code_3 AND code4 IS NOT NULL AND code4 != '';
    ELSIF v_level = 4 THEN
        -- code4가 주어졌을 때 code5의 개수를 계산
        SELECT COUNT(DISTINCT code5)
        INTO v_count
        FROM t_code
        WHERE code1 = p_code_1 AND code2 = p_code_2 AND code3 = p_code_3 AND code4 = p_code_4 AND code5 IS NOT NULL AND code5 != '';
    END IF;

    RETURN v_count;
END;
$$
LANGUAGE plpgsql;

 

 

 

●  검색결과 쿼리

select *, get_subcategory_count('cate','','','') from t_code where code1='cate'   and code3 is null order by codeseq, code1, code2, code3;