both id, and counter have same list length. In fact, they are related in the following way:

id 1 has counter value equal to 6 / id 2 has counter value equal to 7 and so on..

id = [1,2,3,4,5]
counter = [6,7,8,9,10]

-- I want to accomplish this (simplified pseudocode):

for i in range(len(id)):

    with cte1 as
    (select data from table_1 where id = )

    with cte2 as

    (select * from cte1 where counter =  )

    select * from cte2 -- this is the final result of one iteration



    -- result set of each iteration is unioned together after each loop run

CodePudding user response：

Use unnest() taking multiple input arrays to unnest multiple arrays in lockstep. Example function:

CREATE FUNCTION f_loop_in_lockstep(_id_arr int[], _counter_arr int[])
  RETURNS TABLE (data text)
  LANGUAGE plpgsql AS  
$func$
DECLARE
   _id int;
   _counter int;
BEGIN
   FOR _id, _counter IN 
      SELECT *
      FROM   unnest (_id_arr, _counter_arr) t  -- !!
   LOOP
      RETURN QUERY
      SELECT t1.data FROM table_1 t1 WHERE id = _id;

      RETURN QUERY
      SELECT t2.data FROM table_2 t2 WHERE counter = _counter;
   END LOOP;
END
$func$;

Call:

SELECT * FROM f_loop_in_lockstep('{1,2,3,4,5}'::int[]
                               , '{6,7,8,9,10}'::int[]);

Pass actual arrays.

fiddle

More often than not, the loop can be replace with a set-based operation in pure SQL ...

See:

• Unnest multiple arrays in parallel