PostgreSQL 函数返回多个结果集

PostgreSQL函数返回多个结果集

sql
CREATE OR REPLACE FUNCTION get_multiple_result_sets() RETURNS SETOF REFCURSOR AS
$$
DECLARE
    result_set1 REFCURSOR;
    result_set2 REFCURSOR;
BEGIN
    -- 执行查询并将结果集绑定到REFCURSOR变量
    OPEN result_set1 FOR SELECT * FROM table1;
    
    -- 返回第一个结果集
    RETURN NEXT result_set1;
    
    -- 切换到第二个结果集
    MOVE result_set1;
    -- 执行另一个查询并将结果集绑定到REFCURSOR变量
    OPEN result_set2 FOR SELECT * FROM table2;
    
    -- 返回第二个结果集
    RETURN NEXT result_set2;
    
    -- 关闭结果集
    CLOSE result_set1;
    CLOSE result_set2;
END;
$$
LANGUAGE plpgsql;

sql
-- 调用函数并获取结果集
DO $$
DECLARE
    result_set1 REFCURSOR;
    result_set2 REFCURSOR;
    row record;
BEGIN
    -- 调用函数并获取结果集
    SELECT * FROM get_multiple_result_sets() INTO result_set1, result_set2;
    
    -- 遍历第一个结果集
    LOOP
        FETCH NEXT FROM result_set1 INTO row;
        EXIT WHEN NOT FOUND;
        -- 处理结果
        RAISE NOTICE 'Result from first result set: %', row.column_name;
    END LOOP;
    
    -- 遍历第二个结果集
    LOOP
        FETCH NEXT FROM result_set2 INTO row;
        EXIT WHEN NOT FOUND;
        -- 处理结果
        RAISE NOTICE 'Result from second result set: %', row.column_name;
    END LOOP;
    
    -- 关闭结果集
    CLOSE result_set1;
    CLOSE result_set2;
END;
$$;