Postgres pass refcursor output of a procedure to another procedure

Question

I have this stored procedure T1 which returns a refcursor.

create procedure t1(inout rr refcursor)
as $$
begin
open rr for select 'receiving it';
end;
$$ language plpgsql;

Another stored procedure t_main calls T1 and it needs to return the same refcursor result set of T1.

create or replace procedure t_main(inout rc refcursor)
as
$$
declare
rc_in refcursor;
begin
call t1(rc_in);
rc := rc_in;
end;
$$
language plpgsql;

The part rc := rc_in; is not passing the refcursor rc_in to rc. When I execute t_main, I get error saying cursor 'rc' does not exist.

Thanks in advance.

Mano · Accepted Answer · 2021-03-12 21:45:51Z

1

The solution is as below

create or replace procedure t_main(inout rc refcursor)
as
$$
begin
call t1(rc );
end;
$$
language plpgsql;

Using the same refcursor parameter of the main procedure in the dependent procedure solved the issue.

answered Mar 12, 2021 at 21:45

Mano

75115 silver badges46 bronze badges

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

1 Answer 1