PostgreSQL Function & Procedure 배운 것 정리

 

1, 개요

 PostgreSQL은 강력한 사용자 정의 기능(UDF)을 제공하며, 함수(function)와 프로시저(procedure)를 통해 재사용 가능한 로직을 정의하고 활용할 수 있다. 그리고 이번에는 이것을 배웠고, 배운 것을 간단히 정리하려 한다.

 

2, 함수 (Function)

• 반환값이 있는 SQL 코드 블록
• SELECT문 안에서도 호출 가능
• 입력값에 따라 계산 또는 조건 처리 후 값을 리턴
• PostgreSQL은 함수의 이름 + 인자(입력값, 출력값) 타입까지 합쳐서 구별하기 때문에 같은 함수 이름을 오버로딩할 수 있다.
• 함수의 성능 관련 속성
  • VOLATILE: 기본값으로, 같은 인자여도 결과가 바뀔 수 있을 있다는 것을 나타낸다.
  • STABLE: 트랜젝션 내에서 인자가 같은면 결과가 같다 (조회만 가능)
  • IMMUTABLE: 항상 동일 인자에 대해 동일한 결과

2-1. 기본 함수 문법

CREATE OR REPLACE FUNCTION function_name(param TYPE)
RETURNS return_type AS
$$
  SQL or PLPGSQL block
$$
LANGUAGE SQL | PLPGSQL;

 

2-2. 함수 오버로딩 예시

-- 인자 없음
CREATE OR REPLACE FUNCTION hello_world()
RETURNS TEXT AS 
$$
	SELECT 'hello_world';
$$
LANGUAGE SQL;

-- 하나의 인자
CREATE OR REPLACE FUNCTION hello_world(user_name TEXT)
RETURNS TEXT AS 
$$
	SELECT 'hello ' || user_name;
$$
LANGUAGE SQL;

-- 두 개의 텍스트 인자 (이름 미지정 → 위치로 참조)
CREATE OR REPLACE FUNCTION hello_world(TEXT, TEXT)
RETURNS TEXT AS 
$$
	SELECT 'hello ' || $1 || ' and ' || $2;
$$
LANGUAGE SQL;

 

2-3. 사용자 정의 타입을 받아서 조건 분기

CREATE OR REPLACE FUNCTION is_hit_or_flop(movie movies)
RETURNS TEXT AS
$$
	SELECT CASE
		WHEN movie.revenue > movie.budget THEN 'Hit'
		WHEN movie.revenue < movie.budget THEN 'Flop'
		ELSE 'N/A'
 	END
$$
LANGUAGE SQL;

 

2-4. 함수에서 TABLE 반환

CREATE FUNCTION is_hit_or_flop(movie movies)
RETURNS TABLE (hit_or_flop TEXT, other_thing NUMERIC) AS
$$
	SELECT CASE
		WHEN movie.revenue > movie.budget THEN 'Hit'
		WHEN movie.revenue < movie.budget THEN 'Flop'
		ELSE 'N/A'
	END, 11111;
$$ LANGUAGE SQL IMMUTABLE;

 

2-5. Trigger 함수

• 트리거에 의해 실행되는 특수한 함수
• INSERT, UPDATE, DELETE 같은 이벤트 전후에 작동해서 값을 자동 갱신할 수 있다.

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER AS
$$
	BEGIN
		NEW.updated_at = CURRENT_TIMESTAMP;
		RETURN NEW;
	END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER updated_at
BEFORE UPDATE ON movies
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

 

3. 프로시저 (Procedure)

• 반환값이 없는 혹은 없어도 되는 SQL 코드 블록
• CALL 문으로 직접 실행
• 복잡한 로직 실행, 트랜잭션 단위 작업 처리 등에 유용

 

3-1. 기본 문법

CREATE PROCEDURE procedure_name(...)
AS $$
BEGIN
  -- 여러 SQL 문
END;
$$ LANGUAGE PLPGSQL;

 

3-2. revenue를 0에서 NULL로 설정

CREATE PROCEDURE set_zero_revenue() AS
$$
  UPDATE movies SET revenue = NULL WHERE revenue = 0;
$$ LANGUAGE SQL;

CALL set_zero_revenue();

 

3-3. IN/OUT 파라미터 사용

CREATE PROCEDURE hello_world_p(IN name TEXT, OUT greeting TEXT) AS
$$
BEGIN
  greeting = 'Hello ' || name;
END;
$$ LANGUAGE PLPGSQL;

CALL hello_world_p('nico', NULL);

 

4. Function/Procedure 비교 요약 정리

항목	Function	Procedure
반환값	있음	없음, 또는 없어도 됨
호출 방식	SELECT 또는 FROM 구문에서 사용	CALL로 실행
목적	계산, 값 변환	상태 변경, 로직 실행
사용 위치	쿼리 안에서 호출 가능	단독 실행 (CALL)
예	SELECT hello_world();	CALL set_zero_revenue();

• 함수는 무언가를 반환
• 프로시저는 무언가를 실행
• 둘 다 재사용 가능하고, 복잡한 SQL 로직을 깔끔하게 정리할 수 있다.

 

---

관련 문서

• Function Volatility Categories