PostgreSQL 함수(function) 생성 에러

Introduction

PostgreSQL의 사용자 정의 함수는 복잡한 비즈니스 로직을 데이터베이스 레벨에서 처리할 수 있게 해주는 강력한 기능입니다. 하지만 PL/pgSQL 함수를 작성하면서 다양한 구문 에러와 런타임 에러에 직면할 수 있습니다. 이 글에서는 흔한 함수 생성 에러와 해결 방법을 다루겠습니다.

Environment

-- PostgreSQL 버전 확인
SELECT version();

-- PL/pgSQL 언어 확인
SELECT * FROM pg_language WHERE lanname = 'plpgsql';

# psql 연결
psql -U postgres -d mydb

-- 함수 생성 테스트
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_employee_id INTEGER,
    p_base_salary NUMERIC
)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_base_salary * 1.1;
END;
$$ LANGUAGE plpgsql;

Problem

함수 생성 및 실행 시 발생하는 에러들:

-- 에러 1: 반환 타입 불일치
CREATE FUNCTION get_user_name(p_id INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    RETURN 123;  -- 정수 반환 시도
END;
$$ LANGUAGE plpgsql;
-- ERROR: RETURN types don't match

-- 에러 2: 변수 선언 오류
CREATE FUNCTION process_data() RETURNS VOID AS $$
DECLARE
    v_count INTEGER;
    v_name TEXT
BEGIN  -- 세미콜론 누락
    v_count := 0;
END;
$$ LANGUAGE plpgsql;
-- ERROR: syntax error at or near "BEGIN"

-- 에러 3: NULL 처리 에러
CREATE FUNCTION divide_numbers(a INTEGER, b INTEGER)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;  -- b가 0이거나 NULL인 경우
END;
$$ LANGUAGE plpgsql;
-- Division by zero 또는 NULL 반환

# 함수 컴파일 에러 확인
psql -U postgres -d mydb -c "
CREATE FUNCTION test_func() RETURNS VOID AS \$\$
BEGIN
    RAISE NOTICE 'Hello';
END;
\$\$ LANGUAGE plpgsql;
"
# NOTICE: function test_func() doesn't exist yet
# DETAIL: There is no built-in function named "test_func".

Analysis

함수 에러의 원인을 분석했습니다:

-- 함수 존재 여부 확인
SELECT 
    routine_name,
    routine_type,
    data_type
FROM information_schema.routines
WHERE routine_schema = 'public';

-- 함수 의존성 확인
SELECT 
    dep_class.relname as dependent_object,
    dep.objid::regproc as function_name
FROM pg_depend dep
JOIN pg_class dep_class ON dep.depid = dep_class.oid
WHERE dep_class.relkind = 'f';

-- 함수 에러 로그 확인
SHOW log_destination;
SHOW logging_collector;

-- 함수 파싱 에러 디버깅
DO $$
DECLARE
    v_test INTEGER;
BEGIN
    v_test := 'abc'::INTEGER;  -- 타입 캐스팅 에러
EXCEPTION WHEN others THEN
    RAISE NOTICE 'Error: %', SQLERRM;
END;
$$;

Solution

1단계: 올바른 함수 구문 작성

-- 안전한 함수 작성 패턴
CREATE OR REPLACE FUNCTION safe_divide(
    p_numerator NUMERIC,
    p_denominator NUMERIC
)
RETURNS NUMERIC AS $$
BEGIN
    -- NULL 체크
    IF p_numerator IS NULL OR p_denominator IS NULL THEN
        RETURN NULL;
    END IF;
    
    -- 0으로 나누기 방지
    IF p_denominator = 0 THEN
        RAISE EXCEPTION 'Division by zero is not allowed';
    END IF;
    
    RETURN p_numerator / p_denominator;
END;
$$ LANGUAGE plpgsql;

-- 테스트
SELECT safe_divide(10, 3);   -- 3.3333333333333333
SELECT safe_divide(10, 0);   -- ERROR: Division by zero
SELECT safe_divide(10, NULL); -- NULL

2단계: 예외 처리 구현

-- 종합적인 예외 처리 함수
CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS JSONB AS $$
DECLARE
    v_order RECORD;
    v_result JSONB;
BEGIN
    -- 주문 조회
    SELECT * INTO v_order
    FROM orders
    WHERE id = p_order_id;
    
    -- 주문 없음 처리
    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Order not found',
            'order_id', p_order_id
        );
    END IF;
    
    -- 주문 처리 로직
    UPDATE orders
    SET status = 'processed',
        processed_at = CURRENT_TIMESTAMP
    WHERE id = p_order_id;
    
    v_result := jsonb_build_object(
        'success', true,
        'order_id', p_order_id,
        'message', 'Order processed successfully'
    );
    
    RETURN v_result;
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', SQLERRM,
            'sqlstate', SQLSTATE
        );
END;
$$ LANGUAGE plpgsql;

3단계: 디버깅 기법

-- RAISE NOTICE를 활용한 디버깅
CREATE OR REPLACE FUNCTION debug_example(p_input INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_temp INTEGER;
BEGIN
    RAISE NOTICE 'Input value: %', p_input;
    
    v_temp := p_input * 2;
    RAISE NOTICE 'After multiplication: %', v_temp;
    
    IF v_temp > 100 THEN
        RAISE NOTICE 'Value exceeds threshold';
        RETURN v_temp;
    ELSE
        RAISE NOTICE 'Value is within normal range';
        RETURN v_temp;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 실행 및 디버그 출력 확인
SELECT debug_example(50);
-- NOTICE:  Input value: 50
-- NOTICE:  After multiplication: 100
-- NOTICE:  Value exceeds threshold

-- 로깅을 활용한 디버깅
CREATE OR REPLACE FUNCTION log_example(p_data JSONB)
RETURNS VOID AS $$
BEGIN
    -- 함수 시작 로그
    INSERT INTO function_logs (function_name, input_data, log_time)
    VALUES ('log_example', p_data, CURRENT_TIMESTAMP);
    
    -- 비즈니스 로직 처리
    -- ...
    
    -- 함수 종료 로그
    INSERT INTO function_logs (function_name, input_data, log_time, status)
    VALUES ('log_example', p_data, CURRENT_TIMESTAMP, 'completed');
END;
$$ LANGUAGE plpgsql;

Lessons Learned

반환 타입 명시: 함수의 반환 타입을 명확하게 정의하고, 모든 경로에서 일관된 타입을 반환해야 합니다
NULL 처리 필수: 모든 입력 매개변수에 대한 NULL 체크를 수행해야 합니다
예외 처리 구현: EXCEPTION 블록으로 런타임 에러를 처리하고 유용한 에러 메시지를 제공해야 합니다
RAISE NOTICE 활용: 디버깅 시 RAISE NOTICE로 변수 값을 출력하면 문제 해결에 도움이 됩니다
함수 주석 작성: 함수의 목적, 매개변수, 반환값을 주석으로 문서화해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

PostgreSQL 함수(function) 생성 에러: PL/pgSQL 디버깅 가이드