Fetch stored procedure refcursor output and insert into temp table

Question

I have a stored procedure(P1) that returns a refcursor and few other text datatype values. I have another procedure(P2) where I need to the fetch P1's refcursor output and insert it into a temporary table. The temporary table has matching columns and datatypes.

create or replace procedure P1(inout rfcur refcursor, in dtl text)
as
$$
begin
open rfcur for select * from tst_dump where ident = dtl;
end;
$$
language plpgsql;

create or replace P2(inout rfc refcursor, in dt_array text[])
as
$$
declare
i record;
cur refcursor;
begin
for i in array_lower(dt_array, 1)..array_upper(dt_array, 1) loop
call P1(cur, i);
--I need to fetch the result set from `cur` and store into a temp table `t_tab1`.
end loop;
end;
$$
language plpgsql;

Is it possible to achieve this in Postgres?

NOTE: I'm not supposed to make any changes to the procedure P1.

Answer 1

p2 could look like this:

CREATE PROCEDURE p2(IN dt_array text[])
   LANGUAGE plpgsql AS
$$DECLARE
   r record;
   i integer;
   cur refcursor;
BEGIN
   FOR i IN array_lower(dt_array, 1)..array_upper(dt_array, 1) LOOP
      CALL p1(cur, i::text);

      LOOP
         FETCH cur INTO r;
         EXIT WHEN NOT FOUND;
         INSERT INTO t_tab1 (...) VALUES (r.col1, r.col2, ...;
      END LOOP;
   END LOOP;
END;$$;

You should indent your code. That's the basic requirement when you program.

It seems to me that you are doing something the wrong way. Using procedures and cursors complicates everything and makes it slower.

You should do something like

INSERT INTO t_tab
SELECT /* your original query */;