SQL 쿼리에서 함수 호출하기

예를 들어 복잡한 계산이나 데이터 처리를 위한 함수가 있다고 해보자. 만약 SQL 쿼리에서 함수를 바로 쓸 수 없다면, 이런 식으로 작업해야 할 거야:

1. Python이나 JavaScript 같은 프로그래밍 언어에서 함수를 호출한다.
2. 결과를 SQL 쿼리로 넘긴다.

이건 너무 번거롭지! PostgreSQL에서는 함수를 SQL 쿼리 안에 바로 넣어서 쓸 수 있어서, 코드도 줄고, 작업도 빨라지고, 서버 호출도 줄어들어. 이건 특히 이런 상황에서 유용해:

• 계산 자동화.
• 데이터 삽입 전에 검증.
• 기존 데이터 수정.

SELECT에서 함수 호출하기

기본부터 시작해서, 일반 SELECT 쿼리에서 함수를 어떻게 쓰는지 알아보자. 예를 들어 students라는 학생 정보 테이블이 있다고 하자. 학생의 생일을 기준으로 현재 나이를 반환하는 함수를 만들고 싶어.

1단계: 함수 작성하기

calculate_age라는 함수를 만들어서 생일을 받아 나이를 반환하게 해보자:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

2단계: SELECT 쿼리에서 함수 사용하기

이제 테이블의 각 행마다 이 함수를 호출할 수 있어:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

어떻게 동작하냐면?

• students 테이블의 각 행마다 calculate_age 함수가 나이를 계산해.
• 반환된 값이 age 컬럼에 표시돼.

결과 예시:

보다시피, 별로 어렵지 않고 결과도 깔끔하고 프로페셔널하게 보여.

INSERT에서 함수 호출하기

함수는 데이터 삽입할 때도 유용해. 예를 들어 logs라는 테이블에 사용자 행동을 기록한다고 하자. 함수로 자동으로 메시지를 만들어서 로그를 삽입하고 싶어.

1단계: 함수 만들기

generate_log_message라는 함수를 만들어서 사용자 이름과 행동을 받아 메시지 텍스트를 반환하게 해보자:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

2단계: INSERT에서 함수 사용하기

이제 행을 추가할 때 함수를 호출해서 logs 테이블에 메시지를 넣어보자:

INSERT INTO logs (message)
VALUES (generate_log_message('오토', '사이트 접속'));

결과:

함수가 알아서 텍스트 포맷팅도 해주고, 타임스탬프도 붙여줘. 이런 식으로 함수로 반복 작업을 자동화할 수 있어.

UPDATE에서 함수 호출하기

함수는 테이블의 데이터를 수정할 때도 쓸 수 있어. 예를 들어 students 테이블에서 학생들의 그룹명을 새로운 학년으로 바꾸고 싶다고 해보자.

1단계: 함수 만들기

promote_student라는 함수를 만들어서 예전 그룹(예: 101)을 받아서 새 그룹(예: 201)을 반환하게 해보자:

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

2단계: UPDATE에서 함수 사용하기

이제 모든 학생의 그룹을 업데이트해보자:

UPDATE students
SET group_name = promote_student(group_name);

결과:

함수 호출로 그룹명이 한 번에 바뀌는 마법을 볼 수 있지!

WHERE 조건에서 함수 호출하기

함수는 필터링 조건에서도 쓸 수 있어. 학생 나이 예제를 좀 더 확장해보자.

1단계: 나이로 필터링하기

이미 만든 calculate_age 함수를 써서 20살 넘는 학생만 골라보자:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

결과:

여기서 함수가 각 학생의 나이를 실시간으로 계산해서 필터링해줘.

집계 함수랑 같이 쓰기

조금 더 복잡한 예로, 22살 미만 학생 수를 세고 싶다고 해보자. 함수는 COUNT() 같은 집계 함수랑도 잘 어울려.

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

어떻게 동작하냐면?

• calculate_age 함수가 필터링에 쓰여.
• COUNT(*)가 조건에 맞는 행 개수를 세.

실전 활용 예시

데이터 검증 자동화. 예를 들어, 모든 학생의 나이가 18~30살 범위에 있는지 확인하고 싶다고 해보자. 체크용 함수를 만들어서 WHERE 조건에 써봐.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

데이터 삽입 최적화. 온라인 쇼핑몰에서 주문 총액을 클라이언트에서 계산하지 말고, 테이블 orders에 데이터 넣을 때 함수로 바로 계산하게 할 수도 있어.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

함수 호출할 때 흔한 실수

함수를 쿼리에서 많이 쓰다 보면 실수도 생길 수 있어. 자주 나오는 상황이랑 해결법을 정리해볼게:

권한 없음. 함수나 테이블 소유자가 아니면 PostgreSQL이 호출을 막을 수 있어. 함수 실행 권한이 있는지 꼭 확인해.

타입 불일치. 함수에 인자를 넘길 때 데이터 타입을 잘 맞춰야 해. 예를 들어 함수가 DATE를 기대하는데 문자열을 넘기면 에러가 나. 명시적으로 타입 변환을 해줘:

SELECT calculate_age('2000-01-01'::DATE);

함수 내부 문법 오류. 함수에서 에러가 나면 전체 쿼리가 실패할 수 있어. 함수는 꼭 미리 테스트해보고 써!