[PostgreSQL] PL/pgSQL 소개 및 사용자 정의 함수

Yuri Lee·2021년 5월 12일

PostgreSQL란?

PostgreSQL는 프로그래밍 언어를 이용해 함수를 직접 만들어 실행하기 위해 PL/pgSQL이라는 언어를 제공한다.
이 PL/pgSQL를 활용한다는 것은 Stored Procedure를 사용한다는 것이고, 이를 이용해 Trigger를 사용할 수 있는 발판이 된다.
PL/pgSQL은 PostgreSQL을 보다 편리하고 빠른 응답성을 제공받기 위해 사용할 수 있을 뿐만 아니라 데이터베이스 자체의 기능을 확장할 수 있는 기반을 제공한다.

PL/pgSQL은 기본적인 SQL만으로 프로시저를 만들지 않고 PL/pgSQL같은 절차지향식 언어를 지원한다. (Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 PL/pgSQL 로 부른다.

여러개의 SELECT나 UPDATE, INSERT 등과 같은 Query 문을 Stored Procedure 문을 통해 하나로 묶어 실행함으로써 서버와 클라이언트 간의 Round Trip의 개수를 줄인다.
- Round Trip 이란? 라운트 트립(Round-trip)은 클라이언트와 서버간의 데이터 왕복 과정을 의미한다.
Round Trip을 줄이면 불필요한 네트워크 통신 횟수를 줄여 더 빠르게 DBMS 연산 결과를 얻을 수 있다.
SELECT나 UPDATE, INSERT, DELETE 문 등을 Java의 JDBC 등을 이용해 실행하게 되면, Query 문을 해석하고 실행하기에 앞서 많은 준비가 필요하다. 하지만 Stored Procedure는 이미 컴파일되어 DBMS 단에서 바로 실행할 수 있도록 준비되어 있어 그 실행 속도가 매우 빠르며, 함수 호출 시 인자만을 변경하여 빠르고 쉽게 재활용이 가능하다.
특정 유저에게 UPDATE/DELETE를 막고, 함수만 사용할 수 있게 함으로써 기존 데이터 회손을 최대한 막을 수 있다. 보완이 뛰어나다.

Stored Procedure를 C나 PL/pgSQL과 같은 프로그래밍 언어로 개발해야 하므로 특별환 스킬을 요구하고, 문제 발생시 디버깅이 어렵다.
Stored Procedure는 표준인 SQL과는 다르게 DBMS 마다 다르므로 DBMS가 변경되면 해당 DBMS에 맞게 Stored Procedure를 다시 개발해야 한다.

PL/pgSQL을 이용해 간단한 Stored Procedure를 만들어 보자. 사용자 정의 함수를 만들기 위해서는 CREATE FUNCTION 문을 사용한다.

CREATE FUNCTION function_name(param1 type, param2 type)
 RETURNS return_type AS
BEGIN
 -- code
END;
LANGUAGE language_name;

CREATE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER AS
$$ BEGIN
    RETURN a+b;
END; $$
LANGUAGE PLPGSQL;

Step by step goes a long way ✨