간단한 함수 만들기: CREATE FUNCTION

PostgreSQL에서 함수는 진짜 강력한 도구야. 반복적인 작업을 자동화하고, 비즈니스 로직을 만들고, 서버에 더 똑똑한 기능을 줄 수 있지. 함수는 데이터베이스 안에서 실행되는 미니 프로그램이라고 생각하면 돼. 이런 상황에서 특히 좋아:

• 코드 재사용. 똑같은 쿼리를 여러 번 쓰고 있다면, 함수로 감싸서 필요할 때마다 호출하면 돼.
• 작업 자동화. 예를 들어, 직원들의 근무 시간으로 급여를 계산해야 할 때 함수가 딱이야.
• 로직 캡슐화. 복잡한 계산을 서버 쪽에서 처리하게 해서, 클라이언트가 SQL 쿼리로 머리 아플 필요 없게 해줘.

기본 문법 CREATE FUNCTION

함수 만드는 기본 구조는 이렇게 생겼어:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- 함수 본문 (로직)
    RETURN 결과;
END;
$$ LANGUAGE plpgsql;

주요 부분을 하나씩 볼게:

CREATE FUNCTION function_name(parameters):

여기서 함수 이름 function_name을 정하고, 파라미터(필요하면)를 적어.

파라미터는 이름이랑 데이터 타입을 같이 써: my_param INTEGER, another_param TEXT 이런 식으로.

RETURNS return_type:

함수가 뭘 반환할지 정해. 하나의 값(INTEGER, TEXT 등)이나 데이터 집합(TABLE, RECORD)도 가능해.

BEGIN ... END:

이 키워드 사이가 바로 함수의 "본문"이야. 여기서 모든 마법이 일어나지.

RETURN 결과:

함수 실행 결과를 반환해. 조심해야 할 점: 반환 타입이 RETURNS에 쓴 거랑 꼭 맞아야 해.

LANGUAGE plpgsql:

PL/pgSQL 언어를 쓴다는 뜻이야. PostgreSQL은 다른 언어도 지원하지만, 지금은 이걸로 할 거야.

간단 예시: 두 숫자 더하기

두 개의 정수를 더해서 반환하는 함수를 만들어보자.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

이제 호출해보자:

SELECT add_numbers(5, 7); -- 결과: 12

여기서 무슨 일이 일어나냐면?

• 함수는 a와 b라는 INT 타입 파라미터 두 개를 받아.
• 함수 안에서 그냥 둘을 더해서(a + b) 결과를 반환해.
• 진짜 계산기처럼 간단하지!

변수 사용 예시

예를 들어, 우리한테 대학 데이터베이스가 있고, 등록된 학생 수를 알고 싶다고 해보자.

함수를 만들어보자:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- 결과 저장할 변수 선언
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- 테이블에서 행 개수 세기
    RETURN total; -- 결과 반환
END;
$$ LANGUAGE plpgsql;

함수 호출:

SELECT count_students(); -- 예를 들어, 결과: 120

여기서 보면:

• SQL 쿼리 결과를 저장하려고 total 변수를 써.
• SELECT ... INTO 명령이 쿼리 결과를 변수에 넣어줘.

이런 방식은 데이터를 먼저 처리하고 나중에 반환할 때 특히 편해.

여러 값 반환하기: RETURNS TABLE

방금 예시에서는 값 하나만 반환했지. 근데 함수가 데이터 집합, 예를 들어 학생 목록을 반환해야 한다면? 바로 RETURNS TABLE이 필요해.

예시:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

함수 호출:

SELECT * FROM get_students();

예상 결과:

함수 안에서 쿼리 실행할 때 RETURN QUERY의 장점

RETURN QUERY는 SQL 쿼리 결과를 함수에서 바로 반환할 수 있게 해줘. 중간 단계가 줄어서 함수가 더 간단해져.

이번엔 활성화된 학생만 반환하는 함수를 만들어보자:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

get_active_students() 함수를 호출하기 전에 students 테이블을 만들고 테스트 데이터를 넣어야 해. 이렇게 하면 돼:

-- 학생 테이블 만들기
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- 몇 개의 데이터 추가
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

테이블:

이제 호출해보자:

SELECT * FROM get_active_students();

결과:

실행 전에 데이터 검증하기

함수 안에서 IF로 데이터가 올바른지 체크할 수 있어. 예를 들어, 학생이 모든 시험을 통과했을 때만 다음 학년으로 진급시키는 함수를 만들어보자.

예시:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- 학생이 통과한 시험 개수 세기
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = '통과';

    -- 조건 체크
    IF passed_exams < 5 THEN
        RETURN '학생이 충분히 시험을 통과하지 않았어';
    END IF;

    -- 학생 학년 올리기
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN '학생 진급 완료!';
END;
$$ LANGUAGE plpgsql;

함수 만들 때 자주 하는 실수

반환 타입 누락. PostgreSQL은 항상 함수가 뭘 반환하는지 명확히 써야 해. 예를 들어:

CREATE FUNCTION fail() AS $$ -- 오류: RETURNS 없음
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

수정 예시:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

반환 타입 불일치. RETURNS INT라고 했으면 숫자를 반환해야 해. 이럴 때 문자열을 반환하면 안 돼.

함수 안 SQL 쿼리 오류. 함수에 쓰기 전에 쿼리를 꼭 테스트해봐. psql이나 pgAdmin에서 직접 실행해보는 게 좋아.