postgres로 stored procedure을 구현하자

stored procedure를 사용하면
SQL 쿼리 뿐만 아니라,
제어 구문과 변수, 조건문, 반복문 등의 프로그래밍 요소를 사용할 수 있다.

보통 orm을 많이 썼는데 통계 페이지를 하다 보니
쿼리가 아주 복잡해 orm으로 구현이 힘들었다.
그렇다고 쿼리를 코드에 박아버리긴 좀 거시기 하니까.
stored procedure를 사용하고자 한다.

postgres에서는 store procedure을 function이라는 아주 직관적인 용어를 쓴다.

이제 아래의 샘플을 가지고 설명하겠다.

CREATE OR REPLACE FUNCTION getTotal(start_date text, end_date text)
	RETURNS INTEGER
   LANGUAGE plpgsql
AS $$
	DECLARE CNT INTEGER;
	BEGIN
      SELECT (COUNT(*) AS INTEGER) INTO CNT 
      FROM my_date
      WHERE my_date BETWEEN start_date AND end_date
    	;
    RETURN CNT;
    END;
$$
;

• CREATE OR REPLACE FUNCTION: 함수를 정의한다. REPLACE를 쓰면 함수를 수정하고 덮어쓰기 좋다.
• (start_date text, end_date text): 함수의 인자로 이름과 타입을 함께 쓴다.
  (IN start_date text, IN end_date text, OUT cnt integer) 이렇게 작성하여 in과 out을 지정하기도 한다.
• RETURNS: 반환 타입을 지정해 준다.
• LANGUAGE: 함수의 로직을 구현하는 언어로 postgres에 내장된 plpgsql을 사용하며
  python, java, c도 구현할 수 있다. 프로그래밍 언어를 쓰려면 확장을 설치해야 한다.
• $: $사이에 있는 로직을 수행함을 의미힌다.
• DECLARE: 변수를 선언한다.
• BEGIN ~ END: 쿼리를 수행한다.
• SELECT INTO: 조회 결과를 변수에 담는다.
• RETURN: 반환할 변수

이번에는 실제로 써본 집계 쿼리를 간단히 하여 보았다.

날짜 범위의 브랜드별 리뷰수를 보는 쿼리다.

CREATE OR REPLACE FUNCTION review.totalReview(start_date text, end_date text)
	RETURNS table (
	  brand_name  varchar,
	  cnt BIGINT
	)
AS $$
	begin
		RETURN QUERY
		select pr.brand_name, count(*) cnt
		from product_review as pr
		where review_date between start_date and end_date
		group by pr.brand_name 
		;
	END;
$$ LANGUAGE plpgsql
;

SELECT * FROM totalReview('2022-08-30', '2022-10-30');

여러개의 ROW를 가져오면 RETURN 타입을 TABLE로 주어야 한다.

reference

• http://www.gisdeveloper.co.kr/?p=4546