[PostgreSQL] 함수와 뷰 활용하기

함수

함수란?

함수란 원하는 목적의 달성을 위해 일련의 SQL문 작업들을 하나의 단위로 묶은 것을 의미합니다. 즉, 여러 작업들을 ㅎ묶어 하나의 함수 이름으로 부를 수 있습니다. 다른 DBMS에서는 저장프로시저라고도 합니다.

함수를 사용하는 이유

함수를 사용하는 이유는 복잡하고 시간이 많이 걸리는 작업들을 일일이 수행하기는 비효울적이니, 하나의 단위로 묶어서 작업을 쉽게 하기 위해서입니다.

 

함수 사용 여부에 따라 서버와 사용자(클라이언트)간의 통신 횟수가 현저히 차이가 납니다. 함수를 사용하지 않으면 각각의 쿼리를 일일이 서버와 통신해야 하는 반면 함수를 사용하면 한번의 통신만 하면 작업이 가능합니다. 이처럼 함수를 사용하면 여러번의 통신 횟수가 줄어들어 DBMS의 작업 처리가 더 빨라질 수 있습니다.

PostgreSQL 프로시저 언어란?

SQL, C언어 등 다양한 재료로 함수를 만들 수 있지만 추가적으로 프로시저 언어라는 것을 사용할 수 있습니다. 프로시저 언어는 함수와 트리거를 만드는데 사용할 수 있습니다. 프로시저 언어를 추가로 쓰는 이유는 복잡한 연산 처리가 용이하기 때문입니다. PL/pgSQL, PL/TCL, PL/Perl, PL/Python 등이 있습니다. 프로시저 언어를 사용하려면 다음의 명령어를 추가할 데이터베이스에 접속한 다음 입력하여 원하는 언어를 데이터베이스에 설치하면 됩니다.

# CREATE LANGUAGE 언어이름

PostgreSQL에서 함수 사용하기

CREATE FUNCTION function_name(parameter_1 type, parameter_2 type)
RETURNS return_type AS
  'BEGIN
  ...
  END;'
LANGUAGE language_name;

CREATE 대신에 REPLACE를 쓴다면 기존의 함수를 수정하겠다는 의미입니다. AS 뒤의 BEGIN과 END 사이에 함수의 목적, 즉 함수가 실행할 SQL 코드를 입력합니다. BEGIN과 END는 ' '로 감쌀 수 있고, $$ … $$ 로도 감쌀 수 있습니다.

함수를 만들었다면 함수가 잘 만들어졌는지 확인해야 합니다. \df명령어는 현재 접속한 데이터베이스에 만들어진 모든 함수 목록을 보여주는 역할을 합니다.

잘 만들어졌다면, SELECT문을 이용해서 함수의 이름과 인자를 입력하면 함수를 사용할 수 있습니다.

SELECT function_name(parameter type...)

트리거

어떠한 행동이나 작업을 했을 때 미리 저장해놓은 작업이 자동으로 실행되도록 하는 것을 트리거라고 합니다.

PostgreSQL에서 트리거 사용하기

트리거는 함수를 만들 때와 마찬가지로 CREATE TRIGGER 명령어 뒤에 만들 트리거 이름을 입력하면 됩니다. 뒤에는 어떤 특정한 작업의 앞이나 뒤에 트리거를 실행시킬 것임을 알려주는 명령어를 입력해야 합니다.

postgres=# CREATE TRIGGER trigger_name AFTER INSERT ON table_name
postgres=# FOR EACH ROW EXECUTE PROCEDURE function_name;
CREATE TRIGGER

이제 테이블에 데이터가 입력되면 작성된 트리거가 실행되면서 트리거에 연결된 함수가 실행됩니다.

뷰

뷰의 기본 개념

데이터 집계와 결합을 위해 다양한 서브쿼리를 많이 사용했습니다. 또한, 다른 챕터에서도 의미있는 데이터를 조회하기 위해 복잡한 쿼리문을 작성했습니다. 이러한 복잡한 코드를 매번 작성한다면 생산성이 떨어지고 오류가 많아질 것입니다. 이런 문제를 해결해주어 SQL 구문을 깔끔하게 유지하도록 돕는 것이 바로 뷰입니다.

뷰를 일종의 라이브러리처럼 생각하면 편합니다. 뷰는 기존에 만든 쿼리문을 하나의 가상 테이블로 만들어두었다가 필요한 곳에 적절히 사용하는 기능을 갖고 있습니다. 개로 만든 뷰가 참조하는 뷰에 영향을 주지 않는 한 계속해서 확장할 수 있습니다. 뷰를 만들고 그 뷰를 이용해 또 다른 뷰를 만들 수 있습니다.

뷰는 실제하는 테이블이 아니라 가상의 테이블입니다. 또한 뷰가 생성되면 참조한 테이블과 연결되기 때문에 뷰를 제거하지 않고 이 테이블만 삭제할 수 없습니다. 같은 맥락에서 테이블의 컬럼을 수정하는 경우에는 참조하는 뷰를 신경 써야 하므로 제한적입니다.

이런 복잡한 문제는 뷰가 가상 테이블이기 때문에 발생하는 것이며, 이를 해결하기 위해서 실재하는 구체화된 뷰를 만들 수 있습니다.

뷰 생성 및 삭제

CREATE VIEW view_name AS
  SELECT <컬럼1>, <컬럼2>, <컬럼3>, <컬럼4>
  FROM table_name

생성된 뷰는 \dv 명령어를 통해 확인할 수 있습니다. 생성한 뷰가 어떤 테이블을 참조하는지 정확하게 알고 있어야 뷰를 활용할 수 있습니다. 따라서 생성된 뷰가 많아지면 참조 관계를 확인하는 방법도 필요합니다. \d + <뷰이름> 명령어는 생성한 뷰가 어떤 참조 관계를 갖고 있는지 보여줍니다.

마치 테이블을 조회하듯 뷰에 쿼리문을 사용할 수 있습니다.

SELECT * FROM view_name
WHERE ...;

생성한 뷰를 삭제하는 방법은 다음과 같습니다.

DROP VIEW view_name

뷰 활용하기

개발자는 항상 반복을 최대한 줄이기 위해 많은 노력을 기울이는 직업입니다. 코드를 생산하는 과정 중에 반복적인 코드를 쓴다면 불필요한 시간을 많이 소모할 것입니다. 따라서 반복을 피하고 효율을 높이는 방법은 이전 코드를 재활용하는 방법이고 이를 쿼리문에서 유용하게 수행할 수 있는 방법은 뷰를 활용하는 것입니다.

기존 테이블처럼 뷰 사용하기

SELECT column_1, column_2, column_3, column_4 FROM view_name
WHERE column_4 = (
  SELECT max(column_4) FROM view_name
);

생성된 뷰를 참조하는 새로운 뷰 만들기

CREATE VIEW view_name_2
  SELECT max(column_1)
  FROM view_name;

이렇게 기존 view를 참고하는 새로운 view를 생성하면 새로 생성한 뷰가 우리가 찾고싶은 데이터를 갖고 있게 됩니다. 따라서 아래와 같이 바꿀 수 있습니다.

SELECT column_1, column_2, column_3, column_4 FROM view_name
WHERE column_4 = (
  SELECT * FROM view_name_2  
)

이런 방식으로 뷰가 또 다른 뷰를 참조하는 방식을 활용하면 다양한 방법으로 사용 가능합니다. 하지만 활용 하기 전에 참조 관계를 정확하게 알고 있어야 실수와 오류를 방지합니다.

구체화된 뷰 만들기

구체화된 뷰는 일반적인 뷰와 구조가 다릅니다. 일반적인 뷰는 정의된 쿼리문을 다시 실행시키지만 결과는 저장하지 않습니다. 하지만 구체화된 뷰는 저장된 쿼리문을 실행한 후 실행 결과를 별도로 저장해둡니다. 만약 구체화된 뷰에 쿼리가 실행되면 저장된 결과를 활용해서 결과를 보여줍니다.

구체화된 뷰는 쿼리문의 결과가 별도로 저장되기 때문에 일반적인 뷰보다 읽기 성능이 더 빠릅니다. 그러나 데이터가 자주 업데이트 되는 상황에서는 구체화된 뷰가 오히려 일반적인 뷰보다 성능이 좋지 않습니다. 따라서 데이터가 변하지 않으면서 읽기 연산이 자주 발생하는 경우에 사용하는 것이 좋습니다.

구체화된 뷰는 구조가 일반적인 뷰와 조금 다르지만 사용방법은 동일합니다. 따라서 생성 및 삭제하는 방법만 알더라도 활용법은 일반적인 뷰와 크게 다르지 않습니다.

CREATE MATERIALIZED VIEW <뷰 이름> AS
  <쿼리>
WITH DATA/WITH NO DATA;

CREATE MATERIALIZED VIEW view_name AS
  SELECT column_1, avg(column_2) AS column_2,
  avg(column_3) AS column_3, avg(column_4) AS column_4
  FROM table_name
  GROUP BY 1
  ORDER BY column_2 DESC
WITH DATA;

WITH DATA를 사용한 경우에는 구체화된 뷰를 생성하는 순간 AS 다음에 나오는 쿼리와 테이블 데이터를 저장합니다. WITH NO DATA를 사용한 경우에는 SQL만 저장하며, 물리적인 테이블은 생성되지 않은 상태입니다. 데이터를 매개하지 않은 경우에는 뷰를 생성하자 마자 SELECT문으로 조회할 수 없습니다. REFRESH 명령어를 통해 뷰의 데이터를 갱신한 후 조회할 수 있습니다.

REFRESH 명령어를 최초 1회 사용한 이후에는 이미 구체화된 뷰에 데이터가 물리적으로 저장되어 있는 상태입니다. 만약 테이블의 데이터가 변경된다면 다시 REFRESH하여 데이터를 업데이트 할 수 있습니다.