Psql 함수,프로시져,트리거
PostgreSQL 함수, 프로시저와 트리거의 기본 문법과 사용 예시를 정리한다.
함수(Function)
psql에서 함수의 기본 구조는 다음과 같다.
함수 기본 구조
CREATE OR REPLACE FUNCTION 스키마명.함수명(
매개변수명 데이터타입
)
RETURNS 반환타입
LANGUAGE 언어
AS $$
함수 본문
$$;
예시 함수 작성
예를 들어 고객 등급에 따른 할인율 반환하는 함수는 다음과 같이 작성할 수 있다.
CREATE OR REPLACE FUNCTION proc_lab.fn_grade_discount_rate(
p_grade text
)
RETURNS numeric
LANGUAGE sql
AS $$
SELECT CASE UPPER(TRIM(p_grade))
WHEN 'BASIC' THEN 0
WHEN 'SILVER' THEN 0.05
WHEN 'GOLD' THEN 0.10
WHEN 'VIP' THEN 0.15
ELSE 0
END;
$$;
- 함수 호출
SELECT proc_lab.fn_grade_discount_rate(' gold ');
SQL 함수와 PL/pgSQL 함수
PostgreSQL 함수는 본문을 어떤 언어로 작성하는지에 따라 실행 방식이 달라지는데요, 이번에는 LANGUAGE sql 과 LANGAUGE plpgsql을 사용했습니다.
SQL 함수
LANGUAGE sql 함수는 일반적으로 하나의 SQL 문장이나 비교적 단순한 조회 로직을 작성할 때 사용합니다
create or replace function proc_lab.fn_grade_discount_rate(
p_grade text
)
returns numeric
LANGUAGE sql
AS $$
SELECT CASE UPPER(TRIM(p_grade))
WHEN 'GOLD' THEN 0.10
ELSE 0
END;
$$;
SQL 함수는 다음과 같은 경우에 적합합니다.
- 단순 값 계산
- 조건에 따른 값 반환
- 테이블 조회 결과 반화
- 복잡한 데이터 제어가 필요하지 않은 경우
PL/pgSQL 함수
LANGUAGE plpgsql 함수는 PostgreSQL의 절차형 언어를 사용한다. 변수 선언, 조건문, 예외 처리처럼 일반 SQL으로 표현하기 어려운 로직을 작성할 수 있다.
CREATE OR REPLACE FUNCTION proc_lab.fn_customer_order_summary_json(
p_customer_id bigint
)
returns jsonb
LANGUAGE plpgsql
AS $$
DECLARE
v_customer jsonb;
Begin
select jsonb_build_object(
'customer_id', c.customer_id,
'name', c.customer_name
)
into v_customer
from proc_lab.customer c
where c.customer_id = p_customer_id;
if v_customer is null then
raise exception 'Customer ID % does not exist', p_customer_id;
end if;
return v_customer;
end;
$$;
PL/pgSQL은 다음과 같은 경우에 적합하다.
- DECLARE를 이용한 변수 선언
- IF, CASE 를 이용한 조건 처리
- 여러 SQL 문장 순차 실행
- RAISE EXCEPTION을 이용한 예외 처리
- 조회 결과를 변수에 저장하는 SELECT INTO
정리하자면 단순 조회와 계산에는 SQL 함수가 적합하고, 제어 흐름이나 예외 처리가 필요하다면 PL/pgSQL 함수가 적합하다.
COMMENTS
GitHub 계정으로 로그인하여 댓글을 남길 수 있습니다. 댓글은 GitHub Discussions에 공개 저장되며, 작성 내용과 GitHub 프로필 정보가 다른 방문자에게 보일 수 있습니다.