PL/pgSQL의 주요 기능

자, 이제 PL/pgSQL이 왜 이렇게 강력하고 DB 개발자나 관리자한테 필수 도구인지 좀 더 깊게 들어가보자. 이번 강의에서는 PL/pgSQL의 장점, 독특한 기능, 그리고 실제로 어떻게 쓸 수 있는지 예시를 통해 알아볼 거야.

PL/pgSQL이 왜 필요한지 이해하려면, 모든 프로그래밍 작업을 오직 SQL만으로 해야 하는 세상을 상상해봐. 예를 들어, 각 학부별 학생 수를 세려면 복잡한 SQL 쿼리를 짜고, 그 결과를 클라이언트 쪽에서 처리해야 해. 이거 좀 비효율적이지? 여기서 PL/pgSQL이 등장하는 거지. 변수, 반복문, 조건문, 에러 처리까지 다 지원하니까.

PL/pgSQL을 쓰는 장점:

1. 서버 쪽 로직: PL/pgSQL 덕분에 서버랑 클라이언트 사이에 오가는 데이터가 줄어들어. 모든 로직이 서버에서 실행되니까 네트워크 지연도 줄어들지.
2. 성능: PL/pgSQL 함수는 DB에 컴파일돼서 저장돼. 그래서 여러 개의 SQL 쿼리를 따로 실행하는 것보다 훨씬 빨라.
3. 작업 자동화: PL/pgSQL로 데이터 업데이트, 로그 남기기, 데이터 무결성 체크 같은 반복 작업을 자동화할 수 있어.
4. 비즈니스 로직: PL/pgSQL은 복잡한 계산, 검증, 분석 리포트 생성 같은 비즈니스 로직을 쉽게 구현할 수 있게 해줘.
5. 편리함과 가독성: PL/pgSQL 코드는 구조화하기 쉽고, 함수로 쪼개서 관리하기도 좋아. 그래서 유지보수도 편해.

PL/pgSQL의 활용 분야

이제 PL/pgSQL을 어디에 쓸 수 있는지, 그리고 실제로 어떻게 문제를 해결하는지 살펴보자.

1. 반복 작업 자동화

PL/pgSQL로 반복되는 작업을 자동화할 수 있어. 예를 들어, 매일 특정 데이터를 업데이트하거나 주기적으로 분석을 돌려야 할 때. PL/pgSQL 함수 하나 만들어서 작업 스케줄러(예: pg_cron)랑 연동하면 정해진 시간에 자동으로 실행돼.

예시: 상태 자동 업데이트

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = '비활성'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE '학생 상태가 업데이트됨.';
END;
$$ LANGUAGE plpgsql;

이 함수는 예를 들어, 1년 넘게 로그인 안 한 학생들의 상태를 "비활성"으로 자동 지정해줘.

2. 리포트 생성

PL/pgSQL은 여러 테이블에서 데이터를 집계하거나 합쳐서 분석 리포트를 만들 때 딱이야. 자동으로 리포트 생성해서 별도 테이블에 저장하는 프로시저도 만들 수 있지.

예시: 학부별 학생 수 리포트 만들기

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

이 함수를 실행하면 모든 학부별 학생 수 통계를 바로 얻을 수 있어.

3. 테이블 변경 로그 남기기

로그 남기기는 DB 테이블 데이터가 바뀔 때 그 내역을 기록하는 거야. PL/pgSQL로 이걸 효율적으로 구현할 수 있는데, 트리거랑 같이 쓰면 좋아.

변경 로그 함수 예시

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

이 함수는 change_logs 테이블에 어떤 테이블이 바뀌었는지, 어떤 작업(예: INSERT, UPDATE, DELETE)이었는지, 그리고 이전/이후 데이터까지 다 기록해.

4. 복잡한 알고리즘 구현

PL/pgSQL로는 표준 SQL로는 힘든 알고리즘도 짤 수 있어. 예를 들어, 비용 계산, 비즈니스 규칙 체크, 자동 ID 생성 같은 거 말이야.

예시: 유니크 아이디 생성

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

이 함수는 현재 타임스탬프랑 랜덤 숫자를 붙여서 유니크한 아이디를 만들어줘.

5. 트리거와 함께 쓰기

트리거랑 PL/pgSQL은 찰떡궁합이야. 예를 들어, 연관된 데이터를 자동으로 업데이트해야 할 때, PL/pgSQL 함수랑 트리거를 같이 쓰면 딱이지.

예시: 학생 삭제 트리거

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE '학생 %의 수강 정보가 삭제됨.', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

이 함수로 students 테이블에서 학생을 삭제하면, enrollments 테이블에서 그 학생의 수강 정보도 자동으로 지워져.

6. 에러 처리

복잡한 작업을 할 때 에러 처리는 진짜 중요하지. PL/pgSQL은 EXCEPTION 블록으로 에러를 잡아서 처리할 수 있어.

예시: 에러 처리

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE '학부 ID %가 존재하지 않아!', faculty_id;
END;
$$ LANGUAGE plpgsql;

여기서 만약 없는 학부 ID를 넣으려고 하면, 프로그램이 죽는 대신 경고 메시지를 띄워줘.

PL/pgSQL로 해결하는 복잡한 문제 예시

PL/pgSQL을 쓰면 이런 것도 할 수 있어! 몇 가지 예시를 들어볼게:

1. 온라인 쇼핑몰 할인 자동 업데이트 매일 프로모션이 끝나는 상품의 할인을 자동으로 업데이트하는 함수.

2. 데이터 검증 및 정정 중복된 레코드가 있는지 테이블을 검사하고, 있으면 삭제하는 함수.

3. 빠른 설정 전환 시스템 파라미터를 바꿔서, 예를 들어 앱의 동작 모드를 전환하는 함수.

IT 세계의 실제 예시

PL/pgSQL은 전 세계 수많은 회사에서 쓰이고 있어. 예를 들면:

• 온라인 쇼핑몰은 세금 계산, 할인 자동 업데이트, 판매 리포트 생성에 함수들을 써.
• 은행은 하루에도 수천 건의 거래를 처리하는데, 이자 계산이나 신용 등급 체크에 PL/pgSQL을 써.
• 소셜 네트워크는 추천 친구 같은 복잡한 데이터 처리 알고리즘에 활용해.

PL/pgSQL은 PostgreSQL 개발자한테는 거의 만능 칼 같은 존재야. DB 작업을 훨씬 쉽게 해주고, 일반 SQL로는 힘든 일도 가능하게 해주지. 그리고 무엇보다 배우기 쉽고, 누구나 데이터베이스 마스터가 된 느낌을 받을 수 있어!