Query문자열에서 출력할 필드명을 추출하는 GET_JSON_FIELDS 함수

업무상 사용하는 MariaDB는 10.5.8 버전입니다.

 

Query 문자열을 보내면, 해당 Query를 실행하고 그 결과를 JSON으로 출력하는 프로시저를 작성하려고 하는데,

JSON_ARRAYAGG(JSON_OBJECT(', field_names, '))  문구에 사용할 field_names 문자열를 추출하는 함수로

이런 형태의 결과가 출력됩니다.

  'Field_name1', Field_name, 'Field_name2', Field_name2, ...   

 

ChatGPT에 물었는데, 여러가지 오류를 돌려막기 하면서 결국 해결이 안되었습니다.

INFORMATION.SCHEME나, SHOW 명령어로 임시 Table을 만들어 추출하는 방식을 제안하는 것이 주 였으나,

사용중인 버전에서는 모두 오류로 진행이 되지 않아서,,,

제 생각을 얘기해 주고 그 내용대로 함수를 작성한 결과입니다.

 

제가 생각했던 내용은

1. Query문에서 Tab, CR, LF를 공백으로 대체

2. SELECT와 FROM 사이 문자열 추출

3. SELECT 문에 있을법한 키워드 제거하고 끝에 쉼표(,) 추가

    (마지막 필드명 다음에 쉼표가 없어서 아래 순환문 종료후 추가 처리가 발생하지 않도록 하는 목적)

4. 한글자씩 읽어서 필드명을 차례 대로 추정

4.1. 괄호쌍의 중첩을 count

4.2. 괄호쌍이 1보다 큰 열린 괄호가 되면 필드명 초기화

4.3. 공백이나 다른 글자는 필드명에 유지

4.4. 쉼표(,)가 나오면 괄호중첩 Count가 0일 때 필드명으로 결정 검토

4.4.1. 남은 문자열에 " AS "가 포함되어 있으면 그 오른쪽을 필드명으로 사용

4.4.2. 남은 문자열이 없으면 Col_n 형식으로 필드명을 부여

4.4.3. 그 외의 경우에는 그대로 TRIM하여 필드명으로 사용

4.4.4. 결정된 필드명을 출력할 문자열에  "'필드명', 필드명"  형태로 추가

4.4.5. 필드명 초기화

5. 결과 출력

 

DELIMITER $$
DROP FUNCTION IF EXISTS GET_JSON_FIELDS;

CREATE FUNCTION GET_JSON_FIELDS(
	  in_query TEXT
)RETURNS text CHARSET utf8

BEGIN
    -- 2025-01-16, EGTOOLS
    -- 이 함수는 SELECT colname1, colname2 ... FROM Table_name  이렇게 필드명이 있는 쿼리에서 필드명만 추출함
    -- Seclect * FROM table_name 은 '*'만 파싱해 오므로 의미가 없어서 사용할 수 없음.
    
    -- 로컬 변수 선언
    DECLARE clean_query TEXT;
    DECLARE remembered_field TEXT DEFAULT '';
    DECLARE current_field TEXT DEFAULT '';
    DECLARE field_list TEXT DEFAULT '';
    DECLARE col_counter INT DEFAULT 0;
    DECLARE char_pos INT DEFAULT 1;
    DECLARE open_parentheses INT DEFAULT 0;
    DECLARE single_quote INT DEFAULT 0;
    DECLARE double_quote INT DEFAULT 0;
    DECLARE char_length INT;
    DECLARE char_at CHAR(1);

    -- SELECT와 FROM 사이 추출
    SET in_query = TRIM(in_query);
    SET in_query = REPLACE(in_query, CHAR(9), ' ');
    SET in_query = REPLACE(in_query, CHAR(10), ' ');
    SET in_query = REPLACE(in_query, CHAR(13), ' ');
    
    -- FROM 이후 제거
    SET @field_pos = LOCATE(' FROM ', UPPER(in_query));
    IF @field_pos > 0 THEN
        SET clean_query = LEFT(in_query, @field_pos - 1);
    ELSE
        SET clean_query = in_query;
    END IF;

    -- SELECT 및 기타 불필요한 키워드 제거
    SET clean_query = REPLACE(clean_query, 'SELECT', '');
    SET clean_query = REPLACE(clean_query, 'DISTINCT', '');
    SET clean_query = REPLACE(clean_query, 'SQL_CALC_FOUND_ROWS', '');
    SET clean_query = TRIM(clean_query);

    -- in_query 끝에 쉼표 추가 (필드 구분용)
    SET clean_query = CONCAT(clean_query, ',');
    SET char_length = CHAR_LENGTH(clean_query);

    -- 글자 하나씩 읽으며 처리
    WHILE char_pos <= char_length DO
        SET char_at = SUBSTRING(clean_query, char_pos, 1);

        -- 괄호 처리
        IF char_at = '(' THEN
            SET open_parentheses = open_parentheses + 1;
            SET remembered_field = '';
        ELSEIF char_at = ')' THEN
            SET open_parentheses = open_parentheses - 1;
            SET remembered_field = '';
        ELSEIF char_at = ' ' THEN   
            SET remembered_field = CONCAT(remembered_field, ' ');
        ELSEIF char_at = ',' THEN   -- 기억한 문자열 관리
            IF open_parentheses = 0 AND single_quote = 0 AND double_quote = 0 THEN
                -- 쉼표가 3가지 Counter가 모두 0인 상태에서 나왔을 때, 필드 종료
                SET current_field = remembered_field;
                SET col_counter = col_counter + 1;

                -- AS 이후 필드명 추출
                IF LOCATE(' AS ', UPPER(current_field)) > 0 THEN
                   SET current_field = TRIM(SUBSTRING(current_field, LOCATE(' AS ', UPPER(current_field))+3));
                   -- SET current_field = current_field;
                ELSEIF current_field = '' THEN
                   SET current_field = CONCAT('Col_', col_counter);
                ELSE 
                    SET current_field =TRIM(current_field);
                END IF;
                SET current_field = REPLACE(current_field, "'", '');
                SET current_field = REPLACE(current_field, '"', '');
                
                -- 필드명 연결
                SET field_list = CONCAT(field_list, IF(field_list='','',','), "'", current_field, "',", current_field); 

                -- 기억 초기화
                SET remembered_field = '';
                SET current_field = '';
            END IF;
        ELSE
                -- 쉼표 외의 문자는 기억에 추가
                SET remembered_field = CONCAT(remembered_field , char_at);
        END IF;

        -- 다음 문자로 이동
        SET char_pos = char_pos + 1;
    END WHILE;

    -- 결과 반환
    RETURN field_list;
END$$

DELIMITER ;