How to return multiple ref cursor from PostgreSQL?

Question

I have successfully created a function. I want to return multiple ref cursors like below. When I execute the function I get a response like in the image below. How can I solve this problem?

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;

When I execute above function It returns output like below.

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

I think this is a limitation of pgAdmin. There is nothing you can do in your code — user330315
– user330315, Commented Sep 28, 2018 at 13:48
My above problem was solved now I am struggling that how can I call this function with MVC C# ? — Moin Khan
– Moin Khan, Commented Oct 3, 2018 at 12:11

Laurenz Albe · Accepted Answer · 2018-10-01 10:05:51Z

1

You got exactly what you wanted – four cursors.

After cou have called the function, you can run the SQL statement

FETCH NEXT FROM "<unnamed portal 26>";

If you don't like the name of the cursor, assign a different one inside the function body:

process_wise_micro_audit := 'auditcursor';

Then you can call FETCH like this after the function has finished:

FETCH NEXT FROM auditcursor;

edited Oct 1, 2018 at 10:05

answered Sep 28, 2018 at 14:43

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Moin Khan Over a year ago

Thanks for your response. I have tried and successfully got response what i want but every time when I execute my above function it change count of "unnamed portal". How can fix this ?

Laurenz Albe Over a year ago

As I said, assign a name to the refcursor variable in the function.

Moin Khan Over a year ago

OK Thanks . Should i also write "FETCH NEXT FROM "<unnamed portal 26>";" in function ?

Laurenz Albe Over a year ago

No; that is run after the function was called. I'll edit the answer to make this clearer.

Moin Khan Over a year ago

Thanks @Laurenz Albe. I think this very bad with postgresql that if I have n number of refcurser then I have to write FETCH statement for each cursor.

|

Collectives™ on Stack Overflow

How to return multiple ref cursor from PostgreSQL?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related