postgreql 11: how to get result of a query in set of records and then process them in a function

Question

I want to write a function like below.Whats wrong in it?

CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS 
DECLARE 
myRec RECORD;
tab_Rec RECORD[]; --This i want to as a table (i dont know the column details though). 
--Declaring it as array fails
BEGIN
SELECT INTO myREC FROM public.myOtherFunc(abc, sss);
--Returns a table i.e. multiple rows and columns
SELECT * INTO tab_Rec FROM public.myAddFunc(abc);
--I want to do this now
for x in 1 .. array_length(tab_Rec, 1)
loop
         ---some logic
end loop;
END;

Answer 1

There are no "table variables" in PL/pgSQL. You might use a temporary table or a cursor for the purpose.

Better yet, to loop through a result set, use the implicit cursor of a FOR loop. See:

• Loop on tables with PL/pgSQL in Postgres 9.0+
• Declare variable of table type in PL/pgSQL

Minimal example:

CREATE OR REPLACE FUNCTION my_func(abc bigint, sss mytable.col1%TYPE)
  RETURNS void  -- or whatever
  LANGUAGE plpgsql AS 
$func$
DECLARE
   _rec record;
BEGIN
   FOR _rec IN
      SELECT * FROM public.myaddfunc(abc)
   LOOP
      -- SOME LOGIC;
   END LOOP;
END
$func$;

You may not even need the loop, if "SOME LOGIC" can be integrated in a plain query ...