0

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
analyst92
  • 243
  • 1
  • 6
  • The "id" list as well as your `cte1` seems to be unused noise? Please clarify. Show the desired result for the given example. Disclose your Postgres version. – Erwin Brandstetter Feb 03 '23 at 00:32
  • PostgreSQL 13.6 (Ubuntu 13.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit – analyst92 Feb 03 '23 at 03:24
  • This above is just the pseudocode, meant to show that both lists are being utilized by multiple cte's – analyst92 Feb 03 '23 at 03:25
  • In the first loop run - id will be 1, counter will be 6. In the next loop run, id will be 2 and counter will be 7. – analyst92 Feb 03 '23 at 03:27

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Can you please let me know which part of your solution to modify if I got the following error: SQL Error [42804]: ERROR: structure of query does not match function result type Detail: Returned type character varying(255) does not match expected type text in column 1. Where: PL/pgSQL function f_loop_in_lockstep(integer[],integer[]) line 13 at RETURN QUERY – analyst92 Feb 03 '23 at 09:41
  • 1
    @analyst92 My function is declared with `RETURNS TABLE (data text)` You return `varchar(255)`. Would work with `RETURNS TABLE (data varchar)`. Aside: `varchar(255)` typically indicates a misunderstanding of the data type. See: https://stackoverflow.com/a/20334221/939860, https://dba.stackexchange.com/a/113162/3684 – Erwin Brandstetter Feb 03 '23 at 16:34