[PostgreSQL] function 함수사용 + 예제SQL소스

1. 개요

• 개발하다보면 function을 사용해야할 경우가 생긴다.
• 사원테이블 + 부서테이블의 구조에서 사원테이블에서 부서명을 join해서 가져올수 있지만 함수를 만들어서 SQL을 간소화 할수있다.
• 위내용을 토대로 간단한 예제를 만들어 보겠다.

 

2. function 개념

• PostgreSQL 함수는 특정 작업을 수행하고 값 또는 값 집합을 반환하는 코드 블럭이다.
• 함수는 PL/Python, PL/Perl 및 PL/Java와 같은 다른 프로그래밍 언어로도 작성할수 있다.
• Postgresql은 프로시저를 사용하지 않고 함수를 사용해야한다.

 

 

3. 기본문법 및 속성설명

• Function Name - 호출하는 데 사용되는 함수의 이름 / 스키마 또는 데이터베이스 내에서 교유해야
• Return Type - 함수가 반환하는 데이터 유형
• Input Parameters - 기본값을 갖거나 "IN", "OUT" 또는 "INOUT" 매개변수로 선언
• Security Definer - 사용자의 권한(기본값)으로 실행할지 또는 호출한 사용자의 권한으로 실행할지를 결
• Language - SQL, PL/pgSQL, PL/Python, PL/Perl, PL/Java 등 여러 언어를 지원
• Error Handling - 오류를 발생시키거나, 특수 값을 반환하거나, 오류를 모두 무시하도록 함수를 정의
• 함수 기본문법은 아래와 같다.

CREATE OR REPLACE FUNCTION public.fn_name() 	-- 매개변수를 (in, out으로 매개변수설정)
	RETURNS text 	-- function의 리턴값
	LANGUAGE sql 	-- language 종류 설정
AS $function$
	BEGIN
		--SQL작성
	END;
$function$

 

 

 

4. 예제보기

• 아래와 같이 사원테이블에서 부서명을 가져오기 위한 function을 만들어본다.

사원 부서 테이블

 

• 해당함수를 생성하고 사원테이블 조회시 fn_dept_nm('부서코드') 형식으로 사용하면 된다.
• 만약 파라미터 타입에 따라서 사용할수 있는 변수는 아래와 같다.
• in_dept_cd integer
• in_dept_cd numeric 
• in_dept_cd text
• in_dept_cd character varying

CREATE OR REPLACE FUNCTION public.fn_dept_nm(in_dept_cd text)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
	declare out_dept_nm text;

BEGIN  
    select f.dept_nm INTO out_dept_nm
      FROM dept_table f
     WHERE f.dept_cd = in_dept_cd;     
return out_dept_nm;
end;
$function$
;

 

• 적용된SQL 예제

# 함수적용된 SQL
select a.emp_no
     , a.dept_cd
     , fn_dept_nm(a.dept_cd) as dept_nm
 from emp_table a
 
 
# Join으로 처리시
select a.emp_no
     , a.dept_cd
     , b.dept_nm
  from emp_table a left outer join dept_table b
                           on a.dept_cd = b.dept_cd

 

• 결과SQL

postgresql result

 

 

5. 함수사용시 주의사항

• 함수를 SQL을 간소화 하는 용도로 사용할수 있지만 너무 남발하지는 않는다.
• 검색시 where 절에 넣지 않는다. 성능저하가 온다.
• 주로 개발하다보면 코드관리를 하게되는데 코드관리되는 항목에 사용하면된다.