[PostgreSQL] Function 동적 페이징 처리하기 (LIMIT, OFFSET)

아래 함수는 _page_size와 _page_number 두 개의 파라미터를 받아 페이징된 결과를 생성하고, 결과에는 총 레코드 수(_total_records), num, id, name을 포함해서 리턴하고 있습니다. 함수 내부에서는 동적 SQL을 사용하여 쿼리를 생성하는 예제입니다.

 

 

1. 테스트 테이블

SELECT *  FROM scheduler.tbl_test;

테스트 테이블 이며 함수를 생성해서 10개씩 나타내며 1,2,3 페이지 까지 호출 해보도록 하겠습니다.

 

 

테스트 테이블

 

2. 함수 생성

CREATE OR REPLACE FUNCTION scheduler.ufx_test(_page_size integer, _page_number integer)
 RETURNS TABLE(_total_records bigint, num integer, id character varying, name character varying)
 LANGUAGE plpgsql
AS $function$
DECLARE 
	return_status					    int8		:= 1;
	total_records 			            int8 		:= 0;
	page_offset   			            int4 		:= 0;	
	sql_pagination			            varchar;
	
	sql_statement			            varchar;
	sql_where				            varchar;
	sql_columnselection		            varchar;
	sql_select							varchar;
begin
	
	/***********************************************************
	* Build Pagination 
	************************************************************/
	IF _page_size IS NULL OR _page_size < 1 THEN
	   _page_size := 50;
	END IF;
	
	IF _page_number IS NULL OR _page_number < 1 THEN
	   _page_number := 1;
	   page_offset  := 0;
	ELSE
	   page_offset := ( _page_number - 1 )* _page_size;
	END IF;
	
	sql_pagination := ' LIMIT ' || _page_size || ' OFFSET ' || page_offset ;

	/***********************************************************
	* Build where 
	***********************************************************/
		
    sql_statement := 'SELECT count( * ) FROM scheduler.tbl_test tt   ';
	
   	sql_where :=   'WHERE 1=1  ';
   		
	sql_statement := sql_statement  ||  sql_where;
		

	EXECUTE sql_statement INTO total_records;		 
	sql_select     := 'SELECT ' || total_records || '::int8, ';


	/***********************************************************
	* Build retrun Query 
	************************************************************/
		sql_columnselection	 :=    '  tt.num   '
								|| ' ,tt.id '
								|| ' ,tt.name '
								|| ' FROM scheduler.tbl_test tt ';
								
				   			   
		/*************************************************************************************
			* Execute return query
		**************************************************************************************/

		sql_statement := concat( sql_select, sql_columnselection,  sql_where,  sql_pagination);
		RAISE NOTICE ' %',sql_statement;
		RETURN QUERY EXECUTE sql_statement;	

	IF total_records > 0 AND NOT FOUND THEN

		RETURN QUERY SELECT total_records * -1
						   ,tt.num
						   ,tt.id
						   ,tt.name; 
	END IF;	

END
$function$
;

 

-함수 시그니처: 함수의 이름은 ufx_test이며, _page_size와 _page_number라는 두 매개변수를 사용합니다.

-반환 형식: _total_records (bigint), num (integer), id (character varying), name (character varying) 열을 포함하는 테이블을 반환합니다.

-페이징: 주어진 _page_size 및 _page_number 매개변수를 사용하여 페이지네이션을 처리합니다.

-COUNT 쿼리: scheduler.tbl_test 테이블에서 SELECT COUNT(*) 쿼리를 실행하고 결과를 total_records에 저장합니다.

-반환 쿼리: 얻은 total_records 카운트를 사용하여 최종 SELECT 쿼리를 작성하고 RETURN QUERY EXECUTE를 사용하여 결과를 반환합니다.

 

 

3. 함수 호출

select * from scheduler.ufx_test(
_page_size => 10::integer, 
_page_number => 2::integer
);

 

-_page_size: 페이지당 표시할 레코드의 개수를 나타내는 정수입니다.

-_page_number: 표시할 페이지의 번호를 나타내는 정수입니다.

 

 

1 페이지 결과

 

 

2 페이지 결과

 

 

3 페이지 결과