# pl/pgsql

PL/pgSQL은 PostgreSQL 데이터베이스 관리 시스템을 위한 프로시저 언어입니다. "PL"은 "프로시저 언어"(Procedural Language)를 의미하며, "pgSQL"은 PostgreSQL의 SQL을 의미합니다. 이 언어는 SQL의 기능을 확장하여 컴플렉스한 데이터베이스 연산과 로직을 처리할 수 있게 해줍니다. PL/pgSQL을 사용하면 조건문, 반복문, 변수, 함수 등 프로그래밍 언어의 일반적인 기능을 SQL 내에서 사용할 수 있게 됩니다.

PL/pgSQL은 다음과 같은 특징을 가집니다:

1. 블록 구조: 코드는 명확한 시작과 끝을 가진 블록으로 구성됩니다. 각 블록은 선언부, 실행부, 예외 처리부로 구성될 수 있습니다.
2. 제어 구조: if-then-else, loop, while, for 등 다양한 제어 구조를 사용하여 복잡한 로직을 구현할 수 있습니다.
3. 예외 처리: 에러 처리를 위한 예외 처리 구문을 제공합니다. 이를 통해 데이터베이스 연산 중 발생할 수 있는 예외 상황을 효과적으로 관리할 수 있습니다.
4. 함수와 트리거: PL/pgSQL을 사용하여 사용자 정의 함수와 트리거를 작성할 수 있습니다. 이를 통해 데이터 조작(DML) 연산 전후에 자동으로 실행되는 로직을 구현할 수 있습니다.
5. 커서 지원: 데이터셋을 순회하며 각 행을 처리할 수 있도록 커서를 지원합니다.
6. 동적 SQL 실행: PL/pgSQL 코드 내에서 동적으로 SQL 쿼리를 구성하고 실행할 수 있는 기능을 제공합니다.

PL/pgSQL을 사용하면 복잡한 데이터 처리 로직을 데이터베이스 서버 내에서 효율적으로 처리할 수 있으며, 네트워크를 통한 데이터 전송을 최소화하여 어플리케이션의 성능을 향상시킬 수 있습니다. PostgreSQL 데이터베이스를 사용하는 많은 어플리케이션에서 PL/pgSQL은 데이터 처리와 관리를 위한 중요한 도구로 활용됩니다.

 

 

////////////////////////////////////////////////////////////////////////////////////////////////////////////
pl/pgsql 함수 정의
//////////////////////////////////////////////
*/

/* fn_update_stast_cnstr 함수 생성 */
CREATE OR REPLACE FUNCTION fn_update_stast_cnstr(_tmlsId integer, _ssnId text)
RETURNS void AS
$$
DECLARE
    _cnctnDt DATE;
BEGIN
    -- 현재 연월일과 cnctn_dt를 비교하기 위해 cnctn_dt 가져오기
    SELECT cnctn_dt::date INTO _cnctnDt
    FROM tb_stast_cnstr_prcs
    WHERE tmls_id = _tmlsId
    AND ssn_id = _ssnId
    ORDER BY cnctn_dt DESC
    LIMIT 1;

    IF _cnctnDt IS NOT NULL THEN
        IF _cnctnDt = CURRENT_DATE THEN
            -- cnctn_dt가 오늘 날짜와 같다면 dscnc_dt 업데이트
            UPDATE tb_stast_cnstr_prcs
            SET dscnc_dt = NOW()
            WHERE tmls_id = _tmlsId
            AND ssn_id = _ssnId
			AND cnctn_dt = (
			    SELECT cnctn_dt
			    FROM tb_stast_cnstr_prcs
			    WHERE tmls_id = _tmlsId
			    AND ssn_id = _ssnId
			    ORDER BY cnctn_dt DESC
			    LIMIT 1
			);
        ELSIF _cnctnDt = CURRENT_DATE - 1 THEN
            -- cnctn_dt가 현재 날짜보다 하루 전이라면 dscnc_dt를 cnctn_dt의 23:59:59로 설정
            UPDATE tb_stast_cnstr_prcs
            SET dscnc_dt = (_cnctnDt + 1) - INTERVAL '1 second'
            WHERE tmls_id = _tmlsId
            AND ssn_id = _ssnId
   			AND cnctn_dt = (
			    SELECT cnctn_dt
			    FROM tb_stast_cnstr_prcs
			    WHERE tmls_id = _tmlsId
			    AND ssn_id = _ssnId
			    ORDER BY cnctn_dt DESC
			    LIMIT 1
			);

            -- 그리고 추가로 INSERT 작업 수행
            INSERT INTO tb_stast_cnstr_prcs(tmls_id, cnctn_ty, ssn_id, cnctn_dt, dscnc_dt)
            VALUES (_tmlsId, _ssnId, to_timestamp(to_char(now(),'YYYY-MM-DD 00:00:00'),'YYYY-MM-DD HH24:MI:SS'), now()); -- 필요한 값에 맞게 조정
        END IF;
    END IF;
END;
$$
LANGUAGE plpgsql;

서버 측 DB에 다음과 같이 함수를 미리 생성한다.(절차형 언어) -> pl/pgsql

 

/* 함수호출 */
SELECT fn_update_stast_cnstr(#{tmls_id},#{ssn_id});

ex) api요청시 쿼리에서 함수를 호출한다.

 

/*함수 삭제*/
DROP FUNCTION if exists fn_update_stast_cnstr(_tmlsId integer, _ssnId text);

반드시 정확한 인자타입과 인자명을 넣을 것

 

/* public 스키마 내의 모든 함수를 나열 */
SELECT 
    n.nspname AS schema_name, 
    p.proname AS function_name, 
    pg_get_function_result(p.oid) AS return_type,
    pg_get_function_arguments(p.oid) AS argument_types,
    l.lanname AS language
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname = 'public'  -- 여기서 스키마 이름을 바꿔 조회할 수 있습니다.
ORDER BY function_name;

위의 쿼리를 통해 서버에 만들어져 있는 pl/pgsql함수를 확인할 수 있다.