Re: Multiple refcursor as INOUT parameter in procedure - Mailing list pgsql-admin

On Thu, 22 Jul 2021 at 18:03, Mukesh Rajpurohit <vivasvan1902@gmail.com> wrote:

Hi All,

              I have one procedure which has 4 input parameter as refcursor and they are as INOUT parameter. 

Now, I want to call that procedure in begin end; block and want to display those 4 refcursor result in pgadmin. I tried multiple option like unnamed portal, declare a refcursor and use in fetch all from..into...etc. But, no option is showing result.

Please help if there is some solution for this, I tried all option available in sites but it did not worked.

I am not sure if i understood it correctly. you have a refcursor as param in a procedure where you initialize and return a refcursor.

postgres=# create procedure p1 (INOUT r1 refcursor) as $$

begin

open r1 for select id from dates;

return;

end; $$ language plpgsql;

CREATE PROCEDURE

-- you would need a do block to declare vars as this would be plpgsql

postgres=# do $$

declare r1 refcursor; x record;

begin

call p1(r1);

for i in 1..10 loop

  fetch next from r1 into x;

  raise notice '%', x;

end loop;

end; $$ language plpgsql;

NOTICE:  (1)

NOTICE:  (2)

NOTICE:  (3)

NOTICE:  (4)

NOTICE:  (5)

NOTICE:  (6)

NOTICE:  (7)

NOTICE:  (8)

NOTICE:  (9)

NOTICE:  (10)

DO

something like this?

postgres/plpgsql.sql at master · postgres/postgres (github.com) 

--

Thanks,

Vijay

Mumbai, India