代码之家  ›  专栏  ›  技术社区  ›  Moin Khan

如何从PostgreSQL返回多个ref游标?

  •  1
  • Moin Khan  · 技术社区  · 6 年前

    我已经成功地创建了一个函数。我想返回多个参考光标如下。当我执行这个函数时,我得到一个如下图所示的响应。我怎样才能解决这个问题?

    CREATE OR REPLACE FUNCTION public.get_dashboard_graph(
        p_fromdate character varying,
        p_todate character varying)
        RETURNS SETOF refcursor 
        LANGUAGE 'plpgsql'
        COST 100.0
        VOLATILE 
        ROWS 1000.0
    AS $function$
    
    DECLARE
          process_wise_positrol refcursor;           
          process_wise_micro_audit refcursor;
          process_wise_positrol_line_stop refcursor;          
          process_wise_micro_audit_line_stop refcursor;
    BEGIN
    
    -- process wise positrol completed
    OPEN process_wise_positrol FOR
        select count(*), d.dd_value from audit_transaction t, audit_master m, dd_type_details d 
        where t.audit_id = m.audit_id and m.activity_id = 9 and t.iscompleted = 'completed' and d.dd_id = m.process 
        and audit_start_time BETWEEN p_fromdate::timestamp AND p_todate::timestamp group by d.dd_value;
    RETURN NEXT process_wise_positrol;
    
    -- process wise Micro audit completed
    OPEN process_wise_micro_audit FOR
        select count(*), d.dd_value from audit_transaction t, audit_master m, dd_type_details d 
        where t.audit_id = m.audit_id and m.activity_id = 8 and t.iscompleted = 'completed' 
        and d.dd_id = m.process 
        and audit_start_time BETWEEN p_fromdate::timestamp AND p_todate::timestamp group by d.dd_value;
    RETURN NEXT process_wise_micro_audit;
    
    -- process wise positrol line stop
    OPEN process_wise_positrol_line_stop FOR
        select count(*), d.dd_value from audit_transaction t
        left join audit_master m on m.audit_id= t.audit_id
        left join dd_type_details d on d.dd_id= m.process
        left join audit_ques_link al on al.audit_trans_id= t.audit_trans_id
        where t.audit_id = m.audit_id and m.activity_id = 9 and al.line_stop = 0
        and t.iscompleted = 'completed' and d.dd_id = m.process 
        and audit_start_time BETWEEN p_fromdate::timestamp AND p_todate::timestamp 
        group by d.dd_value;
    RETURN NEXT process_wise_positrol_line_stop;
    
    -- process wise Micro audit line  stop
    OPEN process_wise_micro_audit_line_stop FOR
        select count(*), d.dd_value from audit_transaction t
        left join audit_master m on m.audit_id= t.audit_id
        left join dd_type_details d on d.dd_id= m.process
        left join audit_ques_link al on al.audit_trans_id= t.audit_trans_id
        where t.audit_id = m.audit_id and m.activity_id = 8 and al.line_stop = 0
        and t.iscompleted = 'completed' and d.dd_id = m.process 
        and audit_start_time BETWEEN p_fromdate::timestamp AND p_todate::timestamp 
        group by d.dd_value;
    RETURN NEXT process_wise_micro_audit_line_stop;
    
    END;
    
    $function$;
    
    ALTER FUNCTION public.get_dashboard_graph(character varying, character varying)
        OWNER TO postgres;
    

    当我执行上述函数时,它返回如下输出。

    select * from get_Dashboard_Graph('09/01/2018','09/28/2018');
    

    enter image description here

    1 回复  |  直到 6 年前
        1
  •  1
  •   Laurenz Albe    6 年前

    你得到了你想要的东西——四个光标。

    调用函数后,可以运行SQL语句

    FETCH NEXT FROM "<unnamed portal 26>";
    

    如果不喜欢光标的名称,请在函数体中指定一个不同的名称:

    process_wise_micro_audit := 'auditcursor';
    

    FETCH 函数完成后如下所示:

    FETCH NEXT FROM auditcursor;