RefCursor를 이용하여 Multiple Results(다중 결과 데이터) Function 개발

Greenplum 또는 Postgresql 이용하여 Function 을 개발하다 보면 하나의 Function 을 실행했을때 다중 결과 데이터를 조회하고 싶을때 사용하는 것이 RefCursor 라는 기능이다.

RefCursor 는 꼭 다중 결과 데이터를 조회할때만 사용하는 것이 아니라 동적쿼리를 만들어서 실행할 때도 사용하기도 한다.

return query 를 사용하거나 새로운 DataType을 생성하여 return 형식을 고정하는 Function 은 동적쿼리를 생성하여 실행할 수 가 없기때문에 꼭 RefCursor를 이용한 Function 을 개발하여야 한다.

 

Postgresql 9.4 - Cursor : https://www.postgresql.org/docs/9.4/plpgsql-cursors.html

 

0. 환경

• Greenplum 6.12.0
• DBeaver 21.1.0

 

1. RefCurosr를 이용한 Function 개발

CREATE OR REPLACE FUNCTION public.ref_fun_test()
RETURNS SETOF refcursor
AS
$$
DECLARE
    ref1 refcursor := 'refcursor_1';
    ref2 refcursor := 'refcursor_2';
    ref3 refcursor := 'refcursor_3';

    v_sql TEXT;
    v_table varchar := 'pg_catalog.pg_tables';
BEGIN
    
    -- 레프커서 1
    OPEN ref1 FOR
        SELECT 'refcursor_1' AS a
              , NO
              , current_timestamp(0)
           FROM pg_catalog.generate_series(1, 10) NO;
    RETURN NEXT ref1;

    -- 레프커서 2
    OPEN ref2 FOR
        SELECT 'refcursor_2' AS a
              , NO
           FROM pg_catalog.generate_series(1, 5) NO;
    RETURN NEXT ref2;

    -- 레프커서 3 - pg_catalog.pg_tables 테이블을 동적쿼리(Dynamic SQL)로 실행
    v_sql := 'select schemaname, tablename from ' || v_table || ' limit 20';
    
    OPEN ref3 FOR EXECUTE v_sql;
    RETURN NEXT ref3;

END
$$ LANGUAGE plpgsql

 

2. DBeaver 를 이용한 RefCursor 실행방법

개발한 함수(public.ref_fun_test)를 실행하면 등록된 3개의 레프커서 이름을 확인할 수 있다.

등록된 레프커서 조회

FETCH ALL FROM <레프커서명>; 쿼리를 실행해서 해당 커서를 조회할려고 하면 커서가 없다는 SQL Error [34000] 에러가 발생한다.

SQL Error [34000]

SQL Error [34000]: ERROR: cursor "refcursor_1" does not exist

 

레프커서를 실행하려면 SELECT 문과 FETCH ALL FROM 문을 영역선택하여 하나로 쿼리로 실행하면 DBeaver에서 조회가 가능하다.

SELECT * FROM public.ref_fun_test();
FETCH ALL FROM refcursor_1;
FETCH ALL FROM refcursor_2;
FETCH ALL FROM refcursor_3;

 

데이터 결과 패널의 Result 탭을 선택하여 레프커서1, 레프커서2, 레프커서3의 데이터를 확인할 수 있다.

refcursor_1

 

refcursor_2

 

refcursor_3 - 동적쿼리 실행

 

3. JAVA 로 RefCursor 실행하기

JAVA/Maven 으로 Greenplum RefCursor를 실행하여 결과를 확인해 보도록 하자.

 

pom.xml 정의

<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>wyleedp.greenplum.java.examples</groupId>
	<artifactId>greenplum-java-examples</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>greenplum-java-examples</name>
	<description>Greenplum 자바 예제들</description>

	<dependencies>
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>42.2.23</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.12.0</version>
		</dependency>
	</dependencies>

</project>

 

wyleedp.greenplum.java.examples.GreenplumRefcursorExample.java 개발

package wyleedp.greenplum.java.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.lang3.StringUtils;

public class GreenplumRefcursorExample {

	private static final String GP_DRIVER_CLASSNAME = "org.postgresql.Driver";
	private static final String GP_URL = "jdbc:postgresql://192.168.2.141/postgres"; // 접속IP/DB명
	private static final String GP_USERNAME = "gpadmin";  // USERNAME
	private static final String GP_PASSWORD = "changeme"; // PASSWORD

	public void execution() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		
		try {
			Class.forName(GP_DRIVER_CLASSNAME);
			con = DriverManager.getConnection(GP_URL, GP_USERNAME, GP_PASSWORD);
			st = con.createStatement();
			rs = st.executeQuery("select version()");
			
			// Greenplum Version 정보출력
			while(rs.next()) {
				System.out.println("Greenplum Version : " + rs.getString(1));
			}
			
			// 레프커서 실행
			StringBuilder sqlBuilder = new StringBuilder();
			sqlBuilder.append("SELECT * FROM public.ref_fun_test();");
			sqlBuilder.append("FETCH ALL FROM refcursor_1;");
			sqlBuilder.append("FETCH ALL FROM refcursor_2;");
			sqlBuilder.append("FETCH ALL FROM refcursor_3;");

			boolean isExecution = st.execute(sqlBuilder.toString());
			
			int sqlIndex = 0;
			int columnWidth = 25;
			String line = StringUtils.repeat("-", 75);
			
			while(isExecution) {
				rs = st.getResultSet();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				
				// 레프커서별 컬럼개수를 출력한다.
				System.out.println(sqlIndex + "] ColumnCount : " + columnCount);
				
				// 컬럼명을 출력한다.
				System.out.println(line);
				for(int columnIndex=1; columnIndex<=columnCount; columnIndex++) {
					String columnName = metaData.getColumnName(columnIndex);
					System.out.print(StringUtils.rightPad(columnName, columnWidth, " "));
				}
				System.out.println();
				System.out.println(line);
				
				// 데이터를 출력한다.
				while(rs.next()){
					for(int columnIndex=1; columnIndex<=columnCount; columnIndex++) {
						String columnTypeName = metaData.getColumnTypeName(columnIndex);
						
						if(StringUtils.equalsIgnoreCase(columnTypeName, "refcursor")) {
							// 컬럼유형이 레프커서이면 String 으로 출력한다.
							String columnValue = rs.getString(columnIndex);
							System.out.print(StringUtils.rightPad(columnValue, columnWidth, " "));
						}else {
							String columnValue = rs.getObject(columnIndex).toString();
							System.out.print(StringUtils.rightPad(columnValue, columnWidth, " "));
						}
					}
					System.out.println();
				}
				
				System.out.println();
				sqlIndex++;
				
				// 실행할 쿼리가 없으면 while 문을 빠져 나간다.
				if(!st.getMoreResults()) {
					break;
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				if(st != null) st.close();
				if(con != null) con.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) {
		GreenplumRefcursorExample example = new GreenplumRefcursorExample();
		example.execution();
	}

}

 

정상적으로 실행되면 Greenplum 버전, 레프커서별로 데이터가 표시된다.

JDBC를 이용한 Greenplum Refcursor 실행결과

 

Refcursor Function 존재하지 않으면 "No function matches" 에러메세지가 발생한다.

No function matches 에러발생

org.postgresql.util.PSQLException: ERROR: function public.ref_fun_test2() does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 15
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)

 

Github 에도 소스가 업로드되어 있다.

• https://github.com/wyleedp/greenplum-java-examples/blob/main/src/main/java/wyleedp/greenplum/java/examples/GreenplumRefcursorExample.java