postgresql返回结果集

单个结果集

（1）使用 return query 创建function

CREATE OR REPLACE FUNCTION get_one_record()
 RETURNS SETOF RECORD as
$$
DECLARE
    v_rec RECORD;
BEGIN
   
   return query ( SELECT * FROM public.config );
   return;
END;
$$
LANGUAGE PLPGSQL;

查询结果：

postgres=# SELECT * FROM get_one_record() t(code varchar,name VARCHAR);
 code |      name
------+----------------
 200  | 连接成功
 404  | 未找到服务器
 500  | 服务器内部错误
 401  | 未授权
 503  | 服务器不可用
(5 rows)

（2）使用游标方式

CREATE OR REPLACE FUNCTION public.get_one_refcursor(refcursor)
RETURNS refcursor AS $body$
BEGIN
OPEN $1 for SELECT * FROM public.config;
RETURN $1;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

查询结果：

postgres=# begin;
BEGIN
kf8193=# select * from get_one_refcursor('aa');
 get_one_refcursor
-------------------
 aa
(1 row)

postgres=# fetch all in "aa";
 code |      name
------+----------------
 200  | 连接成功
 404  | 未找到服务器
 500  | 服务器内部错误
 401  | 未授权
 503  | 服务器不可用
(5 rows)

postgres=# commit;
COMMIT

多个结果集

多个结果集只能使用游标方式进行返回

-- 方式1：
CREATE OR REPLACE FUNCTION public.get_more_refcursor(refcursor,refcursor)
RETURNS SETOF refcursor AS $body$
BEGIN
OPEN $1 for SELECT * FROM public.config limit 5;
RETURN NEXT $1;
OPEN $2 for SELECT * FROM public.flink;
RETURN NEXT $2;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


-- 方式2：
CREATE OR REPLACE FUNCTION public.get_more_refcursor2(INOUT refcursor,INOUT refcursor)
RETURNS record AS $body$
BEGIN
OPEN $1 for SELECT * FROM public.config limit 5;
OPEN $2 for SELECT * FROM public.flink;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

查询结果：

postgres=# select * from get_more_refcursor('a','b');
 get_more_refcursor
--------------------
 a
 b
(2 rows)

postgres=# fetch all from a;
 code |      name
------+----------------
 200  | 连接成功
 404  | 未找到服务器
 500  | 服务器内部错误
 401  | 未授权
 503  | 服务器不可用
(5 rows)

postgres=# fetch all from b;
 word | count |     update_time
------+-------+---------------------
 aa   |     1 | 2019-05-09 10:56:10
 bb   |     3 | 2019-05-09 10:56:10
 bb   |     3 | 2019-05-09 10:56:15
 aa   |     3 | 2019-05-09 10:56:15
 aa   |     2 | 2019-05-09 10:56:20
(5 rows)

postgres=# commit;
COMMIT

对比：

return query的方式比较简便，操作方便，但必须指定返回结果的类型。
cursor 游标的方式返回结果集较为随意，但无法直接得到结果。需要在一个事务中进行查询。但可以一次查询返回多个结果集。

JAVA查询结果集

package dynamicSQL;

import java.sql.*;

import static modelEntertainment.BasicPeopleInfo.basicConnection;
import static modelEntertainment.BasicPeopleInfo.close;

/**
 * @author 
 * @description 解析动态结果数据集
 * @date 2019/7/11
 */
public class Test {
    public static void main(String[] args) {
        final String url = "jdbc:postgresql://192.168.***.***:5432/test";
        final String user = "postgres";
        final String password = "passwd";
        final String sql = "{ call get_one_refcursor(?::refcursor) }";
        final String sql2 = "{ call get_more_refcursor2(?::refcursor, ?::refcursor) }";

        Connection connection = basicConnection(url, user, password);
        // 单个结果集
//        try {
//            connection.setAutoCommit(false);
//            CallableStatement st = connection.prepareCall(sql);
//            st.setObject(1, "results");
//            st.registerOutParameter(1, Types.REF_CURSOR);
//            st.execute();
//
//            ResultSet rs = (ResultSet) st.getObject(1);
//            ArrayList<ArrayList<JSONObject>> arrayList = new ArrayList<>();
//            while (rs.next()) {
//                ResultSetMetaData metaData = rs.getMetaData();
				  // 数据集的列数
//                int columnCount = metaData.getColumnCount();
//                ArrayList<JSONObject> arrayList1 = new ArrayList<>();
//                for (int i = 1; i <= columnCount; i++) {
//                    JSONObject jsonObject = new JSONObject();
					  // 数据集的列名
//                    String columnName = metaData.getColumnName(i);
//                    Object object = rs.getObject(i);
//                    jsonObject.put(columnName,object);
//                    arrayList1.add(jsonObject);
//                }
//                arrayList.add(arrayList1);
//            }
//            System.out.println(arrayList);
//            connection.commit();
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        close();


		// 多个结果集
        try {
            connection.setAutoCommit(false);
            CallableStatement callableStatement = connection.prepareCall(sql2);
            callableStatement.setObject(1,"aa");
            callableStatement.setObject(2,"bb");
            callableStatement.registerOutParameter(1, Types.REF_CURSOR);
            callableStatement.registerOutParameter(2, Types.REF_CURSOR);
            callableStatement.execute();
            ResultSet object1 = (ResultSet)callableStatement.getObject(1);
            ResultSet object2 = (ResultSet)callableStatement.getObject(2);
            while (object1.next()) {
                ResultSetMetaData metaData = object1.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    Object object = object1.getObject(i);
                    System.out.println(object);
                }
            }

            while (object2.next()) {
                ResultSetMetaData metaData = object2.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    Object object = object2.getObject(i);
                    System.out.println(object);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
    }
}

注意：java查询多个结果集时，必须使用方式二创建function，方式一创建的function会报一下错误：
org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters