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.

It must be a stored function as procedures can't return anything. The code for P2 and P1 would be helpful. — Adrian Klaver
– Adrian Klaver, Commented Feb 8, 2021 at 18:28
This is not the way stuff like that should be done in Postgres. — user330315
– user330315, Commented Feb 9, 2021 at 9:22

Laurenz Albe · Accepted Answer · 2021-02-09 09:21:11Z

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 */;

Collectives™ on Stack Overflow

Fetch stored procedure refcursor output and insert into temp table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related